| 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
Sybrand already pointed to the solution: http://osi.oracle.com/~tkyte/ResultSets/index.html (sometimes a little bit slow due to technology used [pl/sql cartridge in oracle application server - if rarely used the application always has to be loaded first]; if necessary try it several times - the results are worth that effort).
Key technology: you must use a variable of type "ref cursor" as output / return-parameter. See the site mentioned above for a working example in vb.
Klaus
Jeff <jeff.cichocki_at_wgrfurniture.com> schrieb in im Newsbeitrag:
92tkj9$mm3$1_at_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.
> >
>
>
> >
 |
 |