CODEWITHSUNDEEP
stored-proceduresplsql
Nick Jam
Nick Jam

Reputation: 59

How to execute a procedure that has OUT parameters

I created this procedure in order to join two tables and select data. The base sql statement works as intended

CREATE OR REPLACE PROCEDURE task1
(p_task1 OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN p_task1 FOR
    SELECT Department.D#, Department.DNAME, Employee.D#, Employee.Name
    FROM Department JOIN Employee ON
    Employee.D# = Department.D#;
END task1;
/

However, when I use the command EXECUTE task1 in SQL*Plus, I receive this error:

SQL> EXECUTE task1;
BEGIN task1; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TASK1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I did some research into the error and its linked to parameters but im unsure what parameters i need, I didn't even intend on coding my procedure to take a parameter.

Upvotes: 0

Views: 143

Answers (1)

Shaun Peterson
Shaun Peterson

Reputation: 1790

You have an OUT parameter... but still a parameter, in you procedure you have declared the parameter as

(p_task1 OUT SYS_REFCURSOR)

So when you call task1 you need to provide a location for it to put the output (OUT parameter)

so in your terminal you would have something along the lines of the below....

execute declare ref_cur SYS_REFCURSOR; begin task1(ref_cur); end;

In the above line we have added declare ref_cur SYS_REFCURSOR; which allows you to access the data the procedure fetches.

However a this stage you are just fetching the data and then doing nothing with it. Once retrieved you will need to iterate through the ref_cursor and do whatever it is you need to do with it.

Upvotes: 1

Related Questions