How to call procedure that contain SYS_REFCURSOR

Question

I create the procedure to output multi rows and columns.

create or replace PROCEDURE MYPROC(
    C1 OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN C1 FOR SELECT * FROM A_TABLE;
END MYPROC;

There is no error when I compiled it. But I Can't call my procedure as normal like

Exec MYPROC;

I've got this error.

Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

But when I run without script, it displayed my expected result.

So please help me how can I execute this procedure? Thank You.

Answer 1

Error is because you are not passing any parameter to the procedure when it expects 1.

You need define a refcursor variable and then pass the variable into the procedure and finally read it.

var cur refcursor;
exec MYPROC(c1 => :cur);
print cur;

or

var cur refcursor;
begin
    MYPROC(c1 => :cur);
end;
/
print cur;