Re: Stored Procedures

From: Jeff <jeff.cichocki_at_wgrfurniture.com>
Date: Tue, 02 Jan 2001 22:26:57 GMT
Message-ID: <92tkj9$mm3$1@nnrp1.deja.com>

In article <3A524A27.35F8CE08_at_Unforgetable.com>,   Walter T Rejuney <BlueSax_at_Unforgetable.com> wrote:
> Jeff wrote:
> >
> > I hate to sound like a rookie, but I am. I am new to the system
 and to
> > oracle. Permissions are not a problem. The query will only return
 one
> > row, but the others that I am working on will return more than one
> > row. I am not familiar with cursors at all in oracle, could you
 shed
> > some light on it for me (hopefully an example)? This procedure
 will be
> > called from inside a VB application I am writing if that helps.
> >
> > In article <3A52363B.634188CC_at_Unforgetable.com>,
> > Walter T Rejuney <BlueSax_at_Unforgetable.com> wrote:
> > > jscichocki_at_my-deja.com wrote:
> > >
> > > > Trying to create the following stored procedure. Everytime I
 try to
> > > > create it, it compiles with errors. I have validated the
 query. It
> > > > functions perfect. I have two other procedure that are giving
 me
 the
> > > > same grief (also select oriented as opposed to updates or
 deletes).
> > > >
> > > > CREATE OR REPLACE PROCEDURE CUSTOM.MY_UPS_GREETED
> > > > (HOME_STORE IN VARCHAR2, TODAY IN VARCHAR2) AS
> > > > BEGIN
> > > > SELECT COUNT(EMP_CD) GREETED
> > > > FROM CUSTOM.UPS_OPPORTUNITY
> > > > WHERE HOME_STORE_CD = HOME_STORE
> > > > AND UP_DATE = TODAY
> > > > AND EMP_CD <> 'ZZZ';
> > > > END;
> > > >
> > > > Thanks for your help.
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > >
> > > At the very minimum you need a host variable for the target of an
 INTO
> > > clause. Otherwise the cursor has no place to put the data. You
 probably
> > > also need some type of exception handler in case the query itself
> > > returns more than one row.
> > >
> > > Beyond that, we need to know exactly what compile errors you are
 getting.
> > > Do you have the proper permissions to create stored procedrues in
 the
> > > CUSTOM schema? Do you have proper grants to SELECT from the
> > > CUSTOM.UPS_OPPORTUNITY table? Is UP_DATE a varchar field where the
 date
> > > is in a particular format? If it is a DATE field then you need to
 perform
> > > a to_date() function on your TODAY variable and apply the
 appropriate
> > > date mask (although this is a run-time problem, not a compile
 problem)?
> > >

>

> Your stored procedure should look something like this:

>

> CREATE OR REPLACE PROCEDURE CUSTOM.MY_UPS_GREETED
> (HOME_STORE IN VARCHAR2,
> TODAY IN VARCHAR2
> Row_Count OUT Integer) AS
> BEGIN
> SELECT COUNT(EMP_CD)
> INTO Row_Count
> FROM CUSTOM.UPS_OPPORTUNITY
> WHERE HOME_STORE_CD = HOME_STORE
> AND UP_DATE = TO_CHAR(TODAY,'YYYYMMDD')
> AND EMP_CD <> 'ZZZ';
> END;

>

> Of course, this assumes that the date is being passed in in YYYYMMDD
> format - if it isn't then just adjust the mask accordingly. The caller
> should also declare a variable large enough to hold the largest
 results.
> If the caller is another PL/SQL procedure then you can just declare
> something like:

>

> Rcount Integer(10) := 0;

>

> If the caller is a VB program then you'd want to declare an integer
> paramter to receive the results.

>

> Personally, however, I would write the stored procedure a little
> differently because I'm really picky about some things:

>

> CREATE OR REPLACE PROCEDURE CUSTOM.MY_UPS_GREETED
> (HOME_STORE IN VARCHAR2,
> TODAY IN VARCHAR2
> Row_Count OUT Integer) AS
> Greet_Count Integer(1) := 0;
> Cursor C_Count is
> SELECT COUNT(EMP_CD) GREETED
> FROM CUSTOM.UPS_OPPORTUNITY
> WHERE HOME_STORE_CD = HOME_STORE
> AND UP_DATE = TO_CHAR(TODAY,'YYYYMMDD')
> AND EMP_CD <> 'ZZZ';
> BEGIN
> For I in C_Count Loop
> RCnt := C_Count%ROWCOUNT;
> If RCnt > 1 Then
> Raise Too_Many_Rows;
> End if;
> Greet_Count := I.GREETED;
> End Loop;
> If RCnt = 0 Then
> Greet_Count := 0;
> End if;
> END;

>

> I never, ever, in over eight years of writing pl/sql use the
> OPEN/FETCH/CLOSE syntax in PL/SQL - I always, at all times, use the
 FOR
> loop syntax because it guarantees under all circumstances that when
 the
> loop goes out of scoop that the cursor is closed.
>

I hate to even ask this after the post you just gave me (excellent info - Thank You) but, how does it change if I wanted to send back the entire result set, not just a count (get rid of the count all together) but all of the emp_cd's

Thanks! You have been a temendous help.

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 02 2001 - 16:26:57 CST