Re: what's wrong with my package? (not a personal problem)

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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org