CODEWITHSUNDEEP
sqldatabaseoracle-databasestored-proceduresplsql
Ferchi
Ferchi

Reputation: 100

Cursor in PL SQL

I need to call a function, and this function returns a cursor. ¿How can I send a cursor as a parameter? I'm trying the following, but It´s not working. Thanks.

DECLARE
  P_NUMBER NUMBER;
  P_TABCURSOR REF CURSOR;    
BEGIN
  P_NUMBER := '80233068';
  P_TABCURSOR := NULL;

P_TABCURSOR:=  PKG_CHANNEL.OBTAIN_CHANNEL (  P_NUMBER => P_NUMBER,
P_TABCURSOR => P_TABCURSOR) ;  
END;

I get this error: PLS-00201: identifier 'CURSOR' must be declared

Upvotes: 1

Views: 885

Answers (4)

Boneist
Boneist

Reputation: 23578

If pkg_channel.obtain_channel has the parameter defined as typeCursor, then there must be a type defined in the spec of: pkg_channel.typecursor. That's what you need to use as the datatype of the variable that will hold that out parameter.

Also, you're calling a procedure, so you wouldn't try to explicitly set the variable value (i.e. using :=). And don't declare a variable as a number, and then try to pass a string in! Instead, you'd do something like:

declare
  p_number number;
  p_tabcursor pkg_channel.typecursor;    
begin
  p_number := 80233068;
  pkg_channel.obtain_channel (p_number => p_number,
                              p_tabcursor => p_tabcursor);  
end;
/

Upvotes: 0

yoelbenyossef
yoelbenyossef

Reputation: 463

First of all, I think that your syntax is off. I think you want sys_refcursor. There might be an object ref_cursor, but ref cursor is 2 variables to pl/sql

https://community.oracle.com/thread/888365

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

It must be this one:

TYPE P_TABCURSOR_TYPE IS REF CURSOR; 
P_TABCURSOR P_TABCURSOR_TYPE;

or 

P_TABCURSOR SYS_REFCURSOR;

which is exactly the same, just shorter.

Upvotes: 2

Tenzin
Tenzin

Reputation: 2505

You should first make a cursur.
And then a record where you put every value of the curson into.

Have a look here about CURSORS and RECORDS.
Click here for link

Example:

First you need to create a cursor. 

CURSOR cursorName is
    SELECT id, name 
    FROM Employee;

Then you fetch the records from that cursor into a record. 

OPEN cursorName; 
LOOP 
    FETCH cursorName INTO recordName
    EXIT WHEN cursorName%notfound;
END LOOP

Upvotes: -1

Related Questions