Q
Problem solve Get help with specific problems with your technologies, process and projects.

Executing procedure with in/out parameter

How to execute a procedure or package from the SQL prompt where the procedure has an in/out parameter in Oracle?

How to execute a procedure or package from the SQL prompt where the procedure has an in/out parameter in Oracle?
I have created a procedure called emp. This procedure has an in out parameter named sys_refcursor. See below:
create or replace procedure emp (
       cv_results in out sys_refcursor )
  is 
begin  
                open cv_results for 
      select last_name, first_name, emp_no
   from employee
  where emp_no in ('10','11','13');
end;
After logging onto SQL*PLUS, I created a variable, then executed the procedure, then I typed the command "Print" with a parameter "x". Note: The Print command displays the result from the in out parameter.
SQL> variable x refcursor
SQL> exec emp(:x)

PL/SQL procedure successfully completed.

SQL> print x

LAST_NAME        FIRST_NAME       EMP_N
--------------- ------------------------------------------ -----
Duemling          Desiree           10
Beekhuysen     Thomas (Tom)     11
Bernatowicz      Joe               13

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close