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

From: pdog <pmussomeli_at_gmail.com>
Date: 30 Aug 2006 13:46:02 -0700
Message-ID: <1156970762.234075.149850@74g2000cwt.googlegroups.com>

---

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;
/ Received on Wed Aug 30 2006 - 15:46:02 CDT