Re: Stored Procedures

From: Walter T Rejuney <BlueSax_at_Unforgetable.com>
Date: Tue, 02 Jan 2001 16:37:43 -0500
Message-ID: <3A524A27.35F8CE08@Unforgetable.com>

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. Received on Tue Jan 02 2001 - 15:37:43 CST