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 last published in July 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close