Re: Passing retrieval arguments to DW stored procedure

From: <vick9935_at_my-dejanews.com>
Date: 1998/08/24
Message-ID: <6rso96$t5p$1@nnrp1.dejanews.com>#1/1

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

Received on Mon Aug 24 1998 - 00:00:00 CDT