Q

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
This was first published in July 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close