Re: Calling a Stored Proc in SQL*Plus

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 07 Jul 1998 20:40:32 GMT
Message-ID: <35a687ae.28347211_at_192.86.155.100>

A copy of this was sent to Markjo_at_my-dejanews.com (if that email address didn't require changing) On Tue, 07 Jul 1998 19:00:50 GMT, you wrote:

>I have the following stored procedure in the database:
>
>PROCEDURE Get_Account_Address(p_AccountDivision in customer.account_division%
>TYPE, p_AccountNumber in customer.account_number%TYPE, CustCursor in out
>c_CustomerInfo) is
> BEGIN
> OPEN CustCursor FOR
> SELECT *
> FROM Bill_To_Address
> WHERE account_division = p_AccountDivision and account_number =
>p_AccountNumber;
> END Get_Account_Address;
>
>Where c_CustomerInfo is a REF CURSOR.
>
>What is the syntax for calling this procedure in SQL*Plus so that I can check
>the results?
>

to print a ref cursor in sqlplus, you do something like:

Here is an example:

create or replace package types
as

    type cursorType is ref cursor;
end;
/  

create or replace function sp_ListEmp return types.cursortype as

    l_cursor types.cursorType;
begin

    open l_cursor for select ename, empno from emp order by ename;  

    return l_cursor;
end;
/    

REM SQL*Plus commands to use a cursor variable  

variable c refcursor
exec :c := sp_ListEmp
print c

>Thanks for any help.
>
>Mark
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

[Quoted] Opinions are mine and do not necessarily reflect those of Oracle Corporation  

[Quoted] Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 07 1998 - 22:40:32 CEST