| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Stored Procedures
Jeff wrote:
>
> 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/
It isn't very easy with straight PL/SQL, but if you are doning the call from VB then you can use a REF cursor.
To do it in pure PL/SQL you'd have to create a package that contains definitions for your record set and then put the results from the cursor into a PL/SQL table and sweep through the table on the receiving side.
............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. v............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. v............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. v............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. ............................................. .............................................Received on Wed Jan 03 2001 - 07:33:36 CST
 |
 |