Re: Passing retrieval arguments to DW stored procedure

Well, I knew I must be looking in the wrong place. To tell you the truth, I bet all three of the options below will work. The problem is the syntax of the "execute" statement in the datawindow painter. After looking at a couple of issues in the PB Folio database (Issue: 503066 and Issue 498896), the syntax for my example would be...

CORRECT SYNTAX:
      execute PROC_CASHFLOW;1 avc_user = :avc_user

INCORRECT SYNTAX (don't try this at home, kids):

      execute PROC_CASHFLOW(:avc_user)

I hope someone comes across this thread because I've spent a couple of days chasing ghosts.

Thanks for the input, Bruce.

Brian in Seattle

In article <6rso96$t5p$1_at_nnrp1.dejanews.com>,   vick9935_at_my-dejanews.com wrote:
> I am getting ready to "go postal" on this one. I have tried a few
> combinations...
>
> 1. Using the package/package body/procedure in package body technique from the
> previous example.
>
> create or replace package PACK_CASHFLOW as
> type PROC_CASHFLOW_DEF_TYPE is record (
> monthdollar number,
> datevalue date,
> cumdollar number,
> typeofdate varchar2(10)) ;
> type rc_type is ref cursor return PROC_CASHFLOW_DEF_TYPE ;
> procedure PROC_CASHFLOW (avc_user in varchar2,rc in out rc_type );
> end ;
>
> create or replace package body PACK_CASHFLOW as
> .
> .
> .
> procedure PROC_CASHFLOW (avc_user in varchar2,rc in out rc_type ) as
> begin
> open rc FOR select...where userid = avc_user;
> end;
>
> 2. Creating a procedure wrapper to call the procedure in the package. I never
> could get that one to compile...Using the above example, I just had another
> procedure call...
>
> CREATE OR REPLACE MY_PROC_WRAPPER(avc_user in varchar2,rc in out
> PACK_CASHFLOW.rc_type ) as
> BEGIN
> PACK_CASHFLOW.PROC_CASHFLOW(avc_user, rc in out PACK_CASHFLOW.rc_type);
> END;
>
> 3. Breaking the procedure out completely. I get the same parsing error that I
> get with option #1.
>
> create or replace procedure PROC_CASHFLOW (avc_user in varchar2,rc in out
> PACK_CASHFLOW.rc_type ) as
> begin
> OPEN rc FOR SELECT...WHERE userid = avc_user;
> end;
>
> I know this is something simple, but I have tried several iterations...with no
> luck.
>
> Thanks,
>
> Brian
>
> In article <6rrsv0$pup$1_at_nnrp1.dejanews.com>,
> Bruce Armstrong [TeamPS] <bruce.armstrong_at_eudoramail.com> wrote:
> > You can't call a procedure within a package directly from a datawindow.
 What
> > you need to do is write a 'wrapper' stand-alone procedure that calls the one
> > in the package. You then call that from the datawindow.
> >
> > In article <6rl2g6$rnf$1_at_nnrp1.dejanews.com>,
> > vick9935_at_my-dejanews.com wrote:
> > > I am getting an ORA-20004 "Syntax error attempting to parse..." when
 passing
 a
> > > retrieval argument to an Oracle stored procedure.
> > >
> > > My "execute" statement is the following:
> > >
> > > execute dbowner.my_pack.my_proc(:as_user);0
> > >
> > > This same datawindow can successfully execute the stored procedure when I
 do
> > > not pass a retrieval argument (hardwiring the value into the stored
 procedure
> > > SQL). My procedure declaration, within my package body, includes the
> > > following:
> > >
> > > procedure my_proc (avc_user in varchar2,rc in out rc_type ) as
> > > begin
> > > OPEN rc FOR SELECT ROUND((SUM(NVL((CURRENT_BUDGET_PROJ *
> > > PCT_TASK_BUDGET)/100,0)))/1000,0) MonthDollar,
> > > .
> > > .
> > > .
> > > WHERE USERID = avc_user
> > > .
> > > .
> > > .
> > >
> > > Any ideas would be greatly appreciated,
> > >
> > > --
> > > Brian in Seattle
> > >
> > > A person who smiles in the face of adversity...probably has a scapegoat.
> > >
> > > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
> > >
> >
> > --
> > Bruce Armstrong [TeamPS]
> > Preach the gospel at all times.
> > If necessary, use words.[Francis of Assisi]
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
> >
>
> --
> Brian in Seattle
>
> A person who smiles in the face of adversity...probably has a scapegoat.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

--
Brian in Seattle

A person who smiles in the face of adversity...probably has a scapegoat.

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