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;
> /

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/cd/B19306_01/appdev.102/b14261/sqloperations.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. Received on Thu Aug 31 2006 - 11:55:25 CDT