---------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BKDP_SEARCH_PKG AS
TYPE BKDP_Cur_GetAccts IS REF CURSOR; --RETURN BKDP_Rec_GetAccts;

PROCEDURE SP_BKDP_ACCOUNTSEARCH (param_account in
RACCOUNT.ACCOUNT%TYPE,
param_name in RACCTREL.NAME%TYPE,
param_case in RACCBKRP.CASE_NUMBER%TYPE,
param_ssn in RACCTREL.SS_NO%TYPE,
BKDP_inout OUT BKDP_Cur_GetAccts);
END BKDP_SEARCH_PKG;
/
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY BKDP_SEARCH_PKG AS

PROCEDURE SP_BKDP_ACCOUNTSEARCH
(param_account in RACCOUNT.ACCOUNT%TYPE,
param_name in RACCTREL.NAME%TYPE,
param_case in RACCBKRP.CASE_NUMBER%TYPE,
param_ssn in RACCTREL.SS_NO%TYPE,
BKDP_inout OUT BKDP_Cur_GetAccts)
as

SQLQuery varchar2(4000);
BEGIN

SQLQuery := 'Select RACCOUNT.account, RACCTREL1.NAME PRIMNAME,
BKDP_MISSINGFIELDS.LEGAL_NAME, ' ||chr(10)
||'RACCTREL1.SS_NO PRIMSSN, RACCTREL1.STATE PRIMSTATE,
RACCBKRP.CASE_NUMBER, ' ||chr(10)
||'RACCBKRP.COURT_ID, RACCTREL2.NAME CONAME, RACCTREL2.SS_NO COSSN,
RACCTREL2.STATE COSTATE ' ||chr(10)
||'FROM RACCOUNT INNER JOIN RACCTREL RACCTREL1 ON (RACCOUNT.ACCOUNT
= RACCTREL1.ACCOUNT AND RACCTREL1.REL_POS= ''1'') ' ||chr(10)
||'LEFT OUTER JOIN RACCTREL RACCTREL2 ON (RACCOUNT.ACCOUNT =
RACCTREL2.ACCOUNT AND RACCTREL2.REL_POS= ''2'') '||chr(10)
||'LEFT OUTER JOIN BKDP_MISSINGFIELDS ON RACCOUNT.ACCOUNT =
BKDP_MISSINGFIELDS.ACCOUNT '||chr(10)
||'LEFT OUTER JOIN RACCBKRP ON RACCOUNT.ACCOUNT = RACCBKRP.ACCOUNT
'||chr(10)
||'WHERE ROWNUM < 500 ';
if (param_account IS NOT NULL) then
SQLQuery := SQLQuery || 'AND RACCOUNT.ACCOUNT = ''' || param_account
|| ''' ';
end if;
if (param_name IS NOT NULL) then
SQLQuery := SQLQuery || 'and RACCTREL1.NAME_U LIKE ''' || param_name
|| '%'' ';
end if;
if (param_case IS NOT NULL) then
SQLQuery := SQLQuery || 'AND RACCBKRP.CASE_NUMBER LIKE ''' ||
param_case || '%'' ';
end if;
if (param_ssn is not null) then
SQLQuery := SQLQuery || 'AND RACCTREL1.SS_NO LIKE ''' || param_ssn ||
'%'' ';
end if;

SQLQuery := SQLQuery || 'ORDER BY RACCOUNT.ACCOUNT';

open BKDP_inout for SQLQuery;

END SP_BKDP_ACCOUNTSEARCH;

END BKDP_SEARCH_PKG;
/

pdog wrote:
> ---------------------------------------------------------------------------------------------
> CREATE OR REPLACE PACKAGE BKDP_SEARCH_PKG AS
> TYPE BKDP_Cur_GetAccts IS REF CURSOR; --RETURN BKDP_Rec_GetAccts;
>
> PROCEDURE SP_BKDP_ACCOUNTSEARCH (param_account in
> RACCOUNT.ACCOUNT%TYPE,
> param_name in RACCTREL.NAME%TYPE,
> param_case in RACCBKRP.CASE_NUMBER%TYPE,
> param_ssn in RACCTREL.SS_NO%TYPE,
> BKDP_inout OUT BKDP_Cur_GetAccts);
> END BKDP_SEARCH_PKG;
> /
> ------------------------------------------------------------------------------------------
> ------------------------------------------------------------------------------------------
> CREATE OR REPLACE PACKAGE BODY BKDP_SEARCH_PKG AS
>
> PROCEDURE SP_BKDP_ACCOUNTSEARCH
> (param_account in RACCOUNT.ACCOUNT%TYPE,
> param_name in RACCTREL.NAME%TYPE,
> param_case in RACCBKRP.CASE_NUMBER%TYPE,
> param_ssn in RACCTREL.SS_NO%TYPE,
> BKDP_inout OUT BKDP_Cur_GetAccts)
> as
>
> SQLQuery varchar2(4000);
> BEGIN
>
> SQLQuery := 'Select RACCOUNT.account, RACCTREL1.NAME PRIMNAME,
> BKDP_MISSINGFIELDS.LEGAL_NAME, ' ||chr(10)
> ||'RACCTREL1.SS_NO PRIMSSN, RACCTREL1.STATE PRIMSTATE,
> RACCBKRP.CASE_NUMBER, ' ||chr(10)
> ||'RACCBKRP.COURT_ID, RACCTREL2.NAME CONAME, RACCTREL2.SS_NO COSSN,
> RACCTREL2.STATE COSTATE ' ||chr(10)
> ||'FROM RACCOUNT INNER JOIN RACCTREL RACCTREL1 ON (RACCOUNT.ACCOUNT
> = RACCTREL1.ACCOUNT AND RACCTREL1.REL_POS= ''1'') ' ||chr(10)
> ||'LEFT OUTER JOIN RACCTREL RACCTREL2 ON (RACCOUNT.ACCOUNT =
> RACCTREL2.ACCOUNT AND RACCTREL2.REL_POS= ''2'') '||chr(10)
> ||'LEFT OUTER JOIN BKDP_MISSINGFIELDS ON RACCOUNT.ACCOUNT =
> BKDP_MISSINGFIELDS.ACCOUNT '||chr(10)
> ||'LEFT OUTER JOIN RACCBKRP ON RACCOUNT.ACCOUNT = RACCBKRP.ACCOUNT
> '||chr(10)
> ||'WHERE ROWNUM < 500 ';
> if (param_account IS NOT NULL) then
> SQLQuery := SQLQuery || 'AND RACCOUNT.ACCOUNT = ''' || param_account
> || ''' ';
> end if;
> if (param_name IS NOT NULL) then
> SQLQuery := SQLQuery || 'and RACCTREL1.NAME_U LIKE ''' || param_name
> || '%'' ';
> end if;
> if (param_case IS NOT NULL) then
> SQLQuery := SQLQuery || 'AND RACCBKRP.CASE_NUMBER LIKE ''' ||
> param_case || '%'' ';
> end if;
> if (param_ssn is not null) then
> SQLQuery := SQLQuery || 'AND RACCTREL1.SS_NO LIKE ''' || param_ssn ||
> '%'' ';
> end if;
>
> SQLQuery := SQLQuery || 'ORDER BY RACCOUNT.ACCOUNT';
>
> open BKDP_inout for SQLQuery;
>
> END SP_BKDP_ACCOUNTSEARCH;
>
> END BKDP_SEARCH_PKG;
> /

1. It doesn't have an Oracle version number

2. It doesn't have an error message

3. It's author thinks we are going to go through the trouble of reverse
engineering the tables just so we can help him solve his problem.

If you want to debug NDS write out the final string to a text file and
then try it in SQL*Plus.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 30 Aug 2006 13:46:02 -0700, "pdog" <pmussomeli@gmail.com> wrote:

>---------------------------------------------------------------------------------------------


You would at least need to post
- a version (5 digits and 3 dots)
- the table definitions
- the exact error message you are getting.

Other than that this going to create an unscalable application by not
using bind variables.

--
Sybrand Bakker, Senior Oracle DBA

pdog wrote:
> ---------------------------------------------------------------------------------------------
> CREATE OR REPLACE PACKAGE BKDP_SEARCH_PKG AS
> TYPE BKDP_Cur_GetAccts IS REF CURSOR; --RETURN BKDP_Rec_GetAccts;
>
> PROCEDURE SP_BKDP_ACCOUNTSEARCH (param_account in
> RACCOUNT.ACCOUNT%TYPE,
> param_name in RACCTREL.NAME%TYPE,
> param_case in RACCBKRP.CASE_NUMBER%TYPE,
> param_ssn in RACCTREL.SS_NO%TYPE,
> BKDP_inout OUT BKDP_Cur_GetAccts);
> END BKDP_SEARCH_PKG;
> /
> ------------------------------------------------------------------------------------------
> ------------------------------------------------------------------------------------------
> CREATE OR REPLACE PACKAGE BODY BKDP_SEARCH_PKG AS
>
> PROCEDURE SP_BKDP_ACCOUNTSEARCH
> (param_account in RACCOUNT.ACCOUNT%TYPE,
> param_name in RACCTREL.NAME%TYPE,
> param_case in RACCBKRP.CASE_NUMBER%TYPE,
> param_ssn in RACCTREL.SS_NO%TYPE,
> BKDP_inout OUT BKDP_Cur_GetAccts)
> as
>
> SQLQuery varchar2(4000);
> BEGIN
>
> SQLQuery := 'Select RACCOUNT.account, RACCTREL1.NAME PRIMNAME,
> BKDP_MISSINGFIELDS.LEGAL_NAME, ' ||chr(10)
> ||'RACCTREL1.SS_NO PRIMSSN, RACCTREL1.STATE PRIMSTATE,
> RACCBKRP.CASE_NUMBER, ' ||chr(10)
> ||'RACCBKRP.COURT_ID, RACCTREL2.NAME CONAME, RACCTREL2.SS_NO COSSN,
> RACCTREL2.STATE COSTATE ' ||chr(10)
> ||'FROM RACCOUNT INNER JOIN RACCTREL RACCTREL1 ON (RACCOUNT.ACCOUNT
> = RACCTREL1.ACCOUNT AND RACCTREL1.REL_POS= ''1'') ' ||chr(10)
> ||'LEFT OUTER JOIN RACCTREL RACCTREL2 ON (RACCOUNT.ACCOUNT =
> RACCTREL2.ACCOUNT AND RACCTREL2.REL_POS= ''2'') '||chr(10)
> ||'LEFT OUTER JOIN BKDP_MISSINGFIELDS ON RACCOUNT.ACCOUNT =
> BKDP_MISSINGFIELDS.ACCOUNT '||chr(10)
> ||'LEFT OUTER JOIN RACCBKRP ON RACCOUNT.ACCOUNT = RACCBKRP.ACCOUNT
> '||chr(10)
> ||'WHERE ROWNUM < 500 ';
> if (param_account IS NOT NULL) then
> SQLQuery := SQLQuery || 'AND RACCOUNT.ACCOUNT = ''' || param_account
> || ''' ';
> end if;
> if (param_name IS NOT NULL) then
> SQLQuery := SQLQuery || 'and RACCTREL1.NAME_U LIKE ''' || param_name
> || '%'' ';
> end if;
> if (param_case IS NOT NULL) then
> SQLQuery := SQLQuery || 'AND RACCBKRP.CASE_NUMBER LIKE ''' ||
> param_case || '%'' ';
> end if;
> if (param_ssn is not null) then
> SQLQuery := SQLQuery || 'AND RACCTREL1.SS_NO LIKE ''' || param_ssn ||
> '%'' ';
> end if;
>
> SQLQuery := SQLQuery || 'ORDER BY RACCOUNT.ACCOUNT';
>
> open BKDP_inout for SQLQuery;
>
> END SP_BKDP_ACCOUNTSEARCH;
>
> END BKDP_SEARCH_PKG;
> /


Change
SQLQuery := SQLQuery || 'ORDER BY RACCOUNT.ACCOUNT';
to
SQLQuery := SQLQuery || ' ORDER BY RACCOUNT.ACCOUNT';

better yet:
Read about ref cursor at:
http://download-east.oracle.com/docs...operations.htm
it may give you an idea to eliminate "dynamic sql" from your solution.
Look at example : Example 6-30
HTH
Thomas Olszewicki
CPAS Systems Inc.

1. version number = 9.2.0.7.0
2. there is no error message. the code compiles fine and when i try to
execute i get "pl/sql procedure executed successfully"
3. BKDP_MISSINGFIELDS
(
ACCOUNT VARCHAR2(20 BYTE) NOT NULL,
PRO_SE INTEGER,
LEGAL_NAME VARCHAR2(35 BYTE),
PACER_NUMBER VARCHAR2(20 BYTE),
PACER_AMOUNT NUMBER,
PACER_USE INTEGER,
TRUSTEE_NUMBER VARCHAR2(20 BYTE),
TRUSTEE_AMOUNT NUMBER,
SITE_ID VARCHAR2(1 BYTE),
SECURITY_TYPE_ID CHAR(5 BYTE),
SECURITY_FLAG INTEGER,
POP INTEGER,
PERC_UNSECURED NUMBER,
CONFIRMATION_DATE DATE,
CONVERSION_DATE DATE,
POC_ACK_DATE DATE
)

RACCOUNT
(
DIVISION_ID VARCHAR2(6 BYTE) NOT NULL,
ACCOUNT VARCHAR2(20 BYTE) NOT NULL,
PRIM_DIVISION_ID VARCHAR2(6 BYTE) NOT NULL,
PRIM_ACCOUNT VARCHAR2(20 BYTE),
LINK_STAT NUMBER(2),
RM_START_DATE DATE,
PRODUCT_CODE VARCHAR2(6 BYTE),
NO_ACCOUNTS NUMBER(2),
BRANCH_NUMBER VARCHAR2(6 BYTE),
CREDIT_LIMIT NUMBER(14,4),
DTE_LST_CR_CHG DATE,
CONTRACT_DATE DATE,
INT_RATE_CODE VARCHAR2(4 BYTE),
INT_START_DATE DATE,
CYCLE_CODE NUMBER(2),
LAST_POST_DATE DATE,
ACCUM_DATE DATE,
CHGOFF_DATE DATE,
CHGOFF_RSN_CODE VARCHAR2(3 BYTE),
CHGOFF_PRINCPAL NUMBER(14,4),
CHGOFF_INTEREST NUMBER(14,4),
CHGOFF_OTH_AMTS NUMBER(14,4),
REASON_INTO_COL VARCHAR2(3 BYTE),
CYCLES_DELQ NUMBER(3),
LAST_PAY_DATE DATE,
LAST_CONT_DATE DATE,
LAST_NOTE_DATE DATE,
LAST_NOTE_CODE VARCHAR2(3 BYTE),
RESPONSIBILITY VARCHAR2(6 BYTE),
RESP_TYPE CHAR(1 BYTE),
STATUS VARCHAR2(4 BYTE),
NEXT_STAT_DATE DATE,
ALLOC_CODE VARCHAR2(6 BYTE),
TOT_CHARGES NUMBER(14,4),
TOT_PRINCIPAL NUMBER(14,4),
TOT_INTEREST NUMBER(14,4),
TOT_COST NUMBER(14,4),
TOT_OTHER NUMBER(14,4),
TOT_NUM_PMTS NUMBER(8),
TOT_PTD NUMBER(14,4),
TOT_PRIN_PTD NUMBER(14,4),
TOT_INT_PTD NUMBER(14,4),
TOT_COST_PTD NUMBER(14,4),
TOT_OTH_INC_PTD NUMBER(14,4),
TOT_DELQ_AMT NUMBER(14,4),
TOT_DUE_AMT NUMBER(14,4),
TOT_PRIN_DUE NUMBER(14,4),
TOT_INT_DUE NUMBER(14,4),
TOT_COSTS_DUE NUMBER(14,4),
TOT_OTHER_DUE NUMBER(14,4),
OVER_LIMIT_AMT NUMBER(14,4),
PCT_BAL_OVR_LMT NUMBER(14,4),
SETTLE_ALLOW NUMBER(14,4),
DISPU_PCT_BAL NUMBER(6,2),
DEBTS_SINCE_ENT NUMBER(4),
TOT_NSF_AMT NUMBER(14,4),
NUM_NSF_PMNTS NUMBER(4),
DATE_LAST_NSF DATE,
LAST_STMT_DATE DATE,
FRST_PMT_DATE DATE,
FIRST_PMT_AMOUNT NUMBER(14,4),
NEXT_CYCLE VARCHAR2(6 BYTE),
NXT_SCH_PMT_DTE DATE,
NXT_SCH_PMT_AMT NUMBER(14,4),
NXT_SCH_PRN_PMT NUMBER(14,4),
NXT_SCH_INT_PMT NUMBER(14,4),
NXT_SCH_CST_PMT NUMBER(14,4),
NXT_SCH_OTH_PMT NUMBER(14,4),
CALL_ATMPS_CONT NUMBER(6),
WKDY_CALL_ATMPS NUMBER(6),
WKNT_CALL_ATMPS NUMBER(6),
WKND_CALL_ATMPS NUMBER(6),
BKN_PROM_S_ENT NUMBER(6),
PLACEMENT_CODE NUMBER(2),
DATE_SENT_AGENCY DATE,
DATE_LAST_RECALL DATE,
BAD_ADDR_FLAG CHAR(1 BYTE),
HOME_OWN_FLAG CHAR(1 BYTE),
HOLD_FLAG CHAR(1 BYTE),
SECURED_BAL NUMBER(14,4),
UNSECURED_BAL NUMBER(14,4),
NG_CHECK_FLAG CHAR(1 BYTE),
DEBIT_FLAG CHAR(1 BYTE),
FIRST_CONT_FLAG CHAR(1 BYTE),
LAST_CALL_NUM VARCHAR2(20 BYTE),
ASSIGNMENT_TYPE VARCHAR2(6 BYTE),
HEADER_NOTE VARCHAR2(300 BYTE),
PROCESS_ID_NO NUMBER(10),
UPDATE_TIME NUMBER(6),
UPDATE_DATE DATE,
UPDATE_TYPE CHAR(1 BYTE),
UPDATE_ID VARCHAR2(6 BYTE),
BAD_PHONE_FLAG CHAR(1 BYTE),
HOST_BALANCE NUMBER(14,4),
HOST_PRINCIPAL NUMBER(14,4),
HOST_INTEREST NUMBER(14,4),
HOST_COSTS NUMBER(14,4),
HOST_OTHER NUMBER(14,4),
FLAGS VARCHAR2(26 BYTE),
USER_DATE_1 DATE,
USER_DATE_2 DATE,
USER_DATE_3 DATE,
USER_DATE_4 DATE,
USER_DATE_5 DATE,
USER_NUMBER_1 NUMBER(18,4),
USER_NUMBER_2 NUMBER(18,4),
USER_NUMBER_3 NUMBER(18,4),
USER_NUMBER_4 NUMBER(18,4),
USER_NUMBER_5 NUMBER(18,4),
USER_TEXT_1 VARCHAR2(50 BYTE),
USER_TEXT_2 VARCHAR2(35 BYTE),
USER_TEXT_3 VARCHAR2(35 BYTE),
USER_TEXT_4 VARCHAR2(35 BYTE),
USER_TEXT_5 VARCHAR2(35 BYTE),
BILLED_THRU DATE,
LAST_PMT_AMT NUMBER(14,4),
ACCUM_INT_PRIN NUMBER(14,4),
ACCUM_INT_INT NUMBER(14,4),
ACCUM_INT_COST NUMBER(14,4),
ACCUM_INT_OTHER NUMBER(14,4),
BUR_UPDATE_DATE DATE,
BUR_UPDATE_TIME NUMBER(6),
CURR_ID VARCHAR2(6 BYTE) NOT NULL,
SCORE_STRATEGY VARCHAR2(6 BYTE),
TSP NUMBER(3) NOT NULL,
CRC NUMBER(10),
AGING_BUCKET1 NUMBER(18,4),
AGING_BUCKET2 NUMBER(18,4),
AGING_BUCKET3 NUMBER(18,4),
AGING_BUCKET4 NUMBER(18,4),
AGING_BUCKET5 NUMBER(18,4),
AGING_HISTORY VARCHAR2(24 BYTE),
BEHAVIOR_SCORE NUMBER(5),
CREDIT_BUR_RATING VARCHAR2(3 BYTE),
CREDIT_SCORE NUMBER(5),
CYCLES_DELQ_NOW NUMBER(3),
PAYMENT_METHOD VARCHAR2(2 BYTE),
OLDST_OUTSTNDNG_DT DATE,
TOT_COMMISSION NUMBER(14,4),
INITIAL_BALANCE NUMBER(14,4),
INITIAL_PRINCIPAL NUMBER(14,4),
INITIAL_INTEREST NUMBER(14,4),
INITIAL_COSTS NUMBER(14,4),
INITIAL_OTHER NUMBER(14,4),
STATUS_DATE DATE,
LAST_ATTEMPTS_DATE DATE,
AMT_RETURNED NUMBER(14,4),
REASON_RETURNED VARCHAR2(3 BYTE),
CLOSE_DATE DATE,
AGENCY_ACCOUNT NUMBER(14),
BUR_CC_DATE DATE,
BUR_CC_CODE VARCHAR2(2 BYTE),
BUR_CC_UPDATE_ID VARCHAR2(6 BYTE),
BUR_DT_DELINQ DATE
)

RACCTREL
(
DIVISION_ID VARCHAR2(6 BYTE) NOT NULL,
ACCOUNT VARCHAR2(20 BYTE),
REL_TYPE VARCHAR2(10 BYTE),
REL_POS VARCHAR2(2 BYTE),
P_A_ADDR_FLAG CHAR(1 BYTE),
NAME VARCHAR2(35 BYTE),
NAME_U VARCHAR2(35 BYTE),
SALUTATION VARCHAR2(35 BYTE),
ADDRESS1 VARCHAR2(35 BYTE),
ADDRESS1_U VARCHAR2(35 BYTE),
ADDRESS2 VARCHAR2(35 BYTE),
ADDRESS2_U VARCHAR2(35 BYTE),
CITY VARCHAR2(20 BYTE),
CITY_U VARCHAR2(20 BYTE),
STATE VARCHAR2(2 BYTE),
STATE_U VARCHAR2(2 BYTE),
ZIP_CODE VARCHAR2(10 BYTE),
ZIP_CODE_U VARCHAR2(10 BYTE),
COUNTY VARCHAR2(20 BYTE),
COUNTY_U VARCHAR2(20 BYTE),
COUNTRY VARCHAR2(15 BYTE),
COUNTRY_U VARCHAR2(15 BYTE),
SS_NO VARCHAR2(11 BYTE),
PHONE_HOME VARCHAR2(12 BYTE),
PHONE_WORK VARCHAR2(20 BYTE),
PHONE_OTHER VARCHAR2(20 BYTE),
DRIV_LIC_NO VARCHAR2(27 BYTE),
TIME_ZONE VARCHAR2(3 BYTE),
COMMNT VARCHAR2(30 BYTE),
UPDATE_TIME NUMBER(6),
UPDATE_DATE DATE,
UPDATE_TYPE CHAR(1 BYTE),
UPDATE_ID VARCHAR2(6 BYTE),
USER_DATE_1 DATE,
USER_DATE_2 DATE,
USER_DATE_3 DATE,
USER_DATE_4 DATE,
USER_DATE_5 DATE,
USER_NUMBER_1 NUMBER(18,4),
USER_NUMBER_2 NUMBER(18,4),
USER_NUMBER_3 NUMBER(18,4),
USER_NUMBER_4 NUMBER(18,4),
USER_NUMBER_5 NUMBER(18,4),
USER_TEXT_1 VARCHAR2(50 BYTE),
USER_TEXT_2 VARCHAR2(35 BYTE),
USER_TEXT_3 VARCHAR2(35 BYTE),
USER_TEXT_4 VARCHAR2(35 BYTE),
USER_TEXT_5 VARCHAR2(35 BYTE),
TSP NUMBER(3) NOT NULL,
CRC NUMBER(10),
BILINGUAL VARCHAR2(10 BYTE),
HO_FLAG CHAR(1 BYTE),
CON_INF_IND CHAR(1 BYTE),
USER_TEXT_6 VARCHAR2(10 BYTE)
)



> 1. It doesn't have an Oracle version number
>
> 2. It doesn't have an error message
>
> 3. It's author thinks we are going to go through the trouble of reverse
> engineering the tables just so we can help him solve his problem.
>
> If you want to debug NDS write out the final string to a text file and
> then try it in SQL*Plus.

pdog wrote:
> 1. version number = 9.2.0.7.0
> 2. there is no error message. the code compiles fine and when i try to
> execute i get "pl/sql procedure executed successfully"

I must be missing something, but how do you know that something is
wrong? You got a message indicating successful execution. So there are
no parse or runtime errors.

If you still think something is wrong, then on the basis of above, I'd
have to say that there are logic errors in your code, which you'll
probably have to sort out yourself.

HTH,
Brian




--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown