Hi all,
I have created the below procedure. I want to return all columns of the corresponding table and I should use Ref cursor as OUT parameter.
CREATE OR REPLACE PROCEDURE P1 (P_ROWS NUMBER,P_SORT VARCHAR2,V_REF OUT SYS_REFCURSOR)
AS
V_STRT NUMBER;
V_END NUMBER;
BEGIN
SELECT DECODE(P_ROWS,1,1,2,21,3,41,4,61,5,81) INTO V_STRT FROM DUAL;
SELECT DECODE(P_ROWS,1,20,2,40,3,60,4,80,5,100) INTO V_END FROM DUAL;
OPEN V_REF FOR SELECT * FROM EMPLOYEES WHERE ROWNUM BETWEEN V_STRT AND V_END ORDER BY P_SORT;
END;
I can able to call it from SQL* PLUS using Bind variable.
VARIABLE X REFCURSOR;
EXEC P1(2, EMPLOYEE_ID, :X)
PRINT X.
Its executed successfully.
but when I am executing apart from SQL PLUS its throwing error.
DECLARE
V_CUR SYS_REFCURSOR;
V_VAL EMPLOYEES%ROWTYPE;
--TYPE TNAME IS TABLE OF EMPLOYEES%ROWTYPE;
BEGIN
P1(2,'SALARY',V_CUR);
LOOP
FETCH V_CUR INTO V_VAL;
DBMS_OUTPUT.PUT_LINE(V_VAL);
EXIT WHEN V_CUR%NOTFOUND;
END LOOP;
END;
ERROR:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
I know this wrong " DBMS_OUTPUT.PUT_LINE(V_VAL);" but I should return all the columns of the table.
Thanks & Regards,
Dinesh Kumar P.