Ask the Expert

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?

    Requires Free Membership to View

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 )
                open cv_results for 
      select last_name, first_name, emp_no
   from employee
  where emp_no in ('10','11','13');
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

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

This was first published in July 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: