Re: Stored Procedures

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)?
>
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 02 2001 - 14:53:27 CST