Cursors in packages

[babi_geek]

Can we specify return type for Static cursors? If yes Can any one give example?

[debasisdas]

please find a related article here.

[krishnaindia2007]

Yes we can.
Here is the example

 
create or replace procedure test_proc is 

cursor c1 return dept%rowtype is 
select * from dept where deptno = 10; 

dept_rec dept%rowtype; 

begin 

open c1; 

fetch c1 into dept_rec; 

dbms_output.put_line ('department name is ' || dept_rec.dname); 

close c1; 
end; 
/

[krishnaindia2007]

You can specify return type for static cursors. But you can't return a value using static cursors.

[babi_geek]

So far I think return type specification is allowed only for ref cursors.

Then can we define static cursors in package specification part?

[krishnaindia2007]

>>Then can we define static cursors in package specification part?

Yes you can.

CREATE PACKAGE emp_pkg IS
CURSOR C1 RETURN emp%rowtype ;
.......
END;
/


CREATE OR REPLACE PACKAGE BODY emp_pkg AS
cursor c1 return emp%rowtype is
select * from emp;
.......
END EMP_PKG;
/