Home » SQL & PL/SQL » SQL & PL/SQL » refcursor as in parameter to the procedure
refcursor as in parameter to the procedure [message #344481] Sat, 30 August 2008 00:26 Go to next message
rahulsql
Messages: 18
Registered: August 2008
Junior Member
i am trying with this example
it gives error
create or replace procedure ex_ref_cur(p_cur_var in sys_refcursor)
is
emp_rec emp%rowtype;
begin

open p_cur_var for select empno,ename from emp where job='CLERK';
loop
fetch p_cur_var into emp_rec;
exit when p_cur_var%notfound;
DBMS_OUTPUT.PUT_LINE (EMP_REC.ENAME ||' '||EMP_REC.EMPNO);

end loop;
close p_cur_var;
end;


if i p_cur_var in ut then it works why?
create or replace procedure ex_ref_cur(p_cur_var in out sys_refcursor)
is
emp_rec emp%rowtype;
begin

open p_cur_var for select empno,ename from emp where job='CLERK';
loop
fetch p_cur_var into emp_rec;
exit when p_cur_var%notfound;
DBMS_OUTPUT.PUT_LINE (EMP_REC.ENAME ||' '||EMP_REC.EMPNO);

end loop;
close p_cur_var;
end;

is there any solution to use refcursor as in parameter to the named block

we can use refcursor as in parameter for anynomous block by declaring the refcursor variable before using as in parameter.but it becomes anonomous block

DECLARE
-- declare a REF CURSOR that returns emp%ROWTYPE (strongly typed)
TYPE emp_refcur_typ IS REF CURSOR RETURN emp%ROWTYPE;
emp_cursor emp_refcur_typ;
-- use the following local procedure to process all the rows after
-- the result set is built, rather than calling a procedure for each row
PROCEDURE process_emp_cv (emp_cv IN emp_refcur_typ) IS
EMP_REC emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Here are the names from latestthe result set --');
LOOP
FETCH emp_cv INTO EMP_REC;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.ENAME || ' '|| EMP_REC.SAL||' '||EMP_REC.JOB||' '||EMP_REC.EMPNO);
END LOOP;
END;

BEGIN
-- find employees whose employee ID is GREATER THAN 7570
OPEN emp_cursor FOR SELECT * FROM emp WHERE empno >7000;
process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing
CLOSE emp_cursor;
-- find employees whose last name starts with R
OPEN emp_cursor FOR SELECT * FROM emp WHERE job='SALESMAN';
process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing
CLOSE emp_cursor;
END;




finally i want to use cursor variable as in parameter to the named stored procedure.can any one solve this
Re: refcursor as in parameter to the procedure [message #344483 is a reply to message #344481] Sat, 30 August 2008 00:29 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


>it gives error
error? what error?
I do not see any error.
Re: refcursor as in parameter to the procedure [message #344488 is a reply to message #344483] Sat, 30 August 2008 01:01 Go to previous messageGo to next message
rahulsql
Messages: 18
Registered: August 2008
Junior Member
create or replace procedure ex_ref_cur(p_cur_var in sys_refcursor)
is
emp_rec emp%rowtype;
begin

open p_cur_var for select empno,ename from emp where job='CLERK';
loop
fetch p_cur_var into emp_rec;
exit when p_cur_var%notfound;
DBMS_OUTPUT.PUT_LINE (EMP_REC.ENAME ||' '||EMP_REC.EMPNO);

end loop;
close p_cur_var;
end;


error is
PLS-00361: IN cursor 'P_CUR_VAR' cannot be OPEN'ed
Re: refcursor as in parameter to the procedure [message #344491 is a reply to message #344488] Sat, 30 August 2008 01:08 Go to previous message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00361: IN cursor 'string' cannot be OPEN'ed
Cause: A cursor parameter with mode IN cannot be modified, and therefore cannot be opened.
Action: Change the cursor parameter to be IN OUT or OUT


Regards
Michel
Previous Topic: Function
Next Topic: Check variable of cursor negative or not? -- error
Goto Forum:
  


Current Time: Sat Sep 20 13:06:56 CDT 2014

Total time taken to generate the page: 0.09165 seconds