Hello,
I'm creating a package with a procedure that has a ref cursor as the output and a type as the input parameter. Here is my package definition
PACKAGE
-------
CREATE OR REPLACE PACKAGE TEST
As
TYPE RefCursorType IS REF CURSOR;
type intTableType is table of varchar2(50) index by binary_integer;
PROCEDURE GETDATA (
P_RECORDS OUT RefCursorType,
YEAR_LIST IN intTableType
);
END;
PACKAGE BODY
-----------------------
SET DEFINE OFF
CREATE OR REPLACE PACKAGE BODY TEST
AS
PROCEDURE GETDATA
(
P_RECORDS OUT RefCursorType,
YEAR_LIST IN intTableType
)
AS
iYearList IDTableType;
BEGIN
--GET ARRAY COUNT
IYEARLIST := IDTABLETYPE();
IYEARLIST.EXTEND(YEAR_LIST.COUNT);
--LOOP THROUGH LISTS AND POPULATE ARRAY
FOR I IN YEAR_LIST.FIRST .. YEAR_LIST.LAST
LOOP
IYEARLIST(I) := IDTYPE(TO_CHAR(YEAR_LIST(I)));
END LOOP;
OPEN P_RECORDS FOR
SELECT CITHTML AS FORMATTED
FROM dbTest.FORMATTED_HTML
WHERE YEAR IN (SELECT * FROM TABLE(IYEARLIST))
;
END GETDATA ;
END TEST ;
/
I would like to execute this procedure from sql developer or sql plus to see if it is working correctly. The year field in dbTest.FORMATTED_HTML is defined as varchar2(20 bytes). It has years as well as some text.
I've used this in the past with simple stored procedures but not ones with an array parameter
var r refcursor;
exec GETOLDDATA(:r,40);
print r;
How can I edit this to allow it to execute my stored procedure?
Thank you