CODEWITHSUNDEEP
oracle-databaseplsqloracle11gplsqldeveloper
Nianios
Nianios

Reputation: 1426

Run oracle procedure with cursor

I am not experienced with db (actually not at all) and I face a problem:
I have oracle 11g and I am using PL/SQL developer.
I have a simple procedure : 

type t_ref_cursor is ref cursor;

procedure fakeProc (
       io_cursor        in out t_ref_cursor
      )
   is
      begin

      open io_cursor for
      SELECT * from myTable;

 end fakeProc;

Now I want to run it as a SQL window (not in a test window) What I am trying to run:

 v_cur         cursor;
 begin
    fakeProc(:v_cur);
 end;

I get errors: ORA-00900:Invalid SQL statement ORA-01008:not all variables bound

So can you point me the right way to run a procedure like this(with begin -end)?

Upvotes: 0

Views: 3224

Answers (2)

Teshte
Teshte

Reputation: 632

Use something like this : 

declare v_cur         SYS_REFCURSOR;
begin
  fakeProc(v_cur);
end;

And the procedure looks like:

CREATE OR REPLACE PROCEDURE FAKEPROC( 
    io_cursor        in out SYS_REFCURSOR
  )
IS 
      begin

      open io_cursor for
      SELECT * from resource_map;


END FAKEPROC;

Don't forget to close cursor after finishing working with it.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Version 7.1.4 of PL/SQL Developer doesn't support ref cursor:

SQL> VARIABLE p_cur REFCURSOR;
REFCURSOR not supported

Later versions may support them (in a command window), or you can use SQL*Plus. This is a direct copy-paste from SQL*Plus:

SQL> CREATE OR REPLACE PROCEDURE prc (p_cur OUT SYS_REFCURSOR) IS
  2  BEGIN
  3     OPEN p_cur FOR SELECT * FROM dual;
  4  END;
  5  /

Procedure created.

SQL> -- declare variable
SQL> VARIABLE p_cur REFCURSOR;
SQL> BEGIN
  2     prc(:p_cur);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> print p_cur

DUM
---
X

Upvotes: 1

Related Questions