REF CURSOR in SP problem

From: janstobbe <janstobbe_at_earthlink.net>
Date: Tue, 07 Aug 2001 18:35:23 GMT
Message-ID: <3B7034EF.8010705@earthlink.net>

I'm creating a package as follows:

CREATE OR REPLACE PACKAGE CIWeb_Search AS

     TYPE SearchRec IS RECORD (
       claimnum		VARCHAR2(20),
       dateOfLoss	DATE,
	  fName 
		VARCHAR2(20),
	  mName 
		VARCHAR2(20),
	  lName 
		VARCHAR2(20),
	  street 
	VARCHAR2(20),
	  city 
		VARCHAR2(20),
	  state 
		VARCHAR2(20),
	  country 
	VARCHAR2(20),
	  zip 
		VARCHAR2(20)
     );
     TYPE SearchRecTableType IS TABLE OF SearchRec;
	TYPE SearchCurTyp IS REF CURSOR;	
	PROCEDURE search_name_lname(
		lname VARCHAR2,
		offset INTEGER,
		searchCnt INTEGER,
		searchCursor IN OUT SearchCurTyp);

END CIWeb_Search;
/
CREATE OR REPLACE PACKAGE BODY CIWeb_Search AS

	PROCEDURE search_name_lname(
		lname VARCHAR2,
		offset INTEGER,
		searchCnt INTEGER,
		searchCursor IN OUT SearchCurTyp) IS

	searchRow SearchRec;
     j INTEGER;
     maxSelectCnt INTEGER ;
	searchTable SearchRecTableType  := SearchRecTableType();

	CURSOR lname_cursor IS
		SELECT DISTINCT w.CLAIM_NUM, w.DATE_OF_LOSS_DT, n.FNAME, n.MNAME,
						n.BUSINESS_OR_LNAME, n.STREET1, n.CITY, n.COUNTRY, n.STATE, n.ZIP
		FROM CWS.WFM_WORK_ITEM w, CWS.CLM_NAME n
		WHERE w.WORK_ID = n.WORK_ID AND n.BUSINESS_OR_LNAME LIKE lname
		ORDER by n.BUSINESS_OR_LNAME ASC;
	BEGIN
         if searchCnt<= 100 then
		  maxSelectCnt:= searchCnt;
		else
	      maxSelectCnt:=100;
		end if;		

		OPEN lname_cursor;

         IF offset = 0 THEN
			FOR i in 0..maxSelectCnt - 1 LOOP
				FETCH lname_cursor INTO searchRow;
				EXIT WHEN lname_cursor%NOTFOUND;
				searchTable.extend();
				searchTable(i+1):=searchRow;
			END LOOP;
		ELSE
		  j:=0;
		  WHILE j < offset LOOP
		    FETCH lname_cursor INTO searchRow;
			EXIT WHEN lname_cursor%NOTFOUND;
			j:=j+1;
		  END LOOP;

		  IF j < offset then
		    RETURN;
		  END IF;

		  FOR i in 0..maxSelectCnt - 1 LOOP
			FETCH lname_cursor into searchRow;
			EXIT WHEN lname_cursor%NOTFOUND;
			searchTable.extend();
			searchTable(i+1):=searchRow;
		  END LOOP;
		END IF;

		OPEN searchCursor FOR

		SELECT * FROM TABLE(searchTable);		
	END;
	

END CIWeb_Search;

This gets created fine but when I go into SQL Plus and do:

variable x refcursor
exec ciweb_search.search_name_lname('SMITH', 0, 10, :x)

I get the following error:
ERROR at line 1:
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [],
[], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [pfrrun.c:pfrbnd1()], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [0]

Does anyone see what I'm doing wrong? Received on Tue Aug 07 2001 - 13:35:23 CDT