Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to fire a stored proc from a query window

Re: How to fire a stored proc from a query window

From: Lee <lduhl_at_corp.realcomp.com>
Date: 17 Aug 2004 09:22:44 -0700
Message-ID: <719532c5.0408170822.58583c3a@posting.google.com>


Thomas Gaines <Thomas.Gaines_at_noaa.gov> wrote in message news:<cfr0oq$qrt$1_at_boulder.noaa.gov>...
> Lee wrote:
>
> > We've been working with Oracle (10g) for a couple months and we are
> > just now trying to fire a stored proc from a query window (Sql/Plus or
> > 3rd party utility)
> >
> > I would like to call the proc declared below and have the results
> > returned, but for the life of me I cannot figure out how to call it.
> >
> >
> > CREATE OR REPLACE PROCEDURE SA.USP_LEES_TEST_SP (
> > strAgentCode IN CHAR DEFAULT NULL,
> > RC1 IN OUT Omwb_emulation.globalPkg.RCT1)
> > AS
> > < snip>
> >
> > I've tried things like the following, but nothing works:
> > EXECUTE sa.usp_Lees_Test_Sp('12345'),
>
> Here, you're undoubtedly receiving this error:
> PLS-00306: wrong number or types of arguments in call to 'USP_LEES_TEST_SP'
>
> Which argument is '12345' supposed to correspond with? You've specified
> two.
> You probably want to do something like this:
>
> exec sa.usp_Lees_Test_Sp(strAgentCode =>'12345', :dummy_var);
>
> where var has been declared in SQL*Plus or whatever environment you're
> using.
> In SQL*Plus, you'd do it like this:
>
> variable dummy_var Omwb_emulation.globalPkg.RCT1
>
> I don't know what the Omwb_emulation.globalPkg.RCT1 type
> is, but this should probably work.
>
> How does this sound to you? Like Dave said, what errors
> are you receiving. Small details like this are often
> quite helpful...
>
> Tom
>

Dave/Tom

The error I was getting was just as Tom suspected above.

I followed Tom's instructions and the query appears to run but it did not return any results to my query window.

FYI, The Omwb_Emulation package, just declares a Ref Cursor as the following:
TYPE RCT1 IS REF CURSOR; As mentioned above, I run the following and get no resuts returned. This procedure should return approx 50 records. The procedure appers to run fine.

variable dummy_var Omwb_emulation.globalPkg.RCT1 exec sa.usp_Lees_Test_Sp(strAgentCode =>'297', :dummy_var);

I'm assuming my "RCT1" cursor is returned populated with the 50+ records that were found, but how do I get those results displayed.

Thanks Again for your help!
Lee Received on Tue Aug 17 2004 - 11:22:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US