Re: Stored Procedures

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)? Received on Tue Jan 02 2001 - 14:12:43 CST