Re: stored procedure, cursor problems

Steve Walterhouse wrote:
>
> Here is a simple procedure definition with a cursor declaration:
>
> create or replace procedure schproc is
> CURSOR c1 IS SELECT DMD_PROJ_ONLY, DMD_PROJECT, SUP_PART, DMD_ORD_NBR,
> SUP_ORD_NBR, DMD_FLP_NBR FROM WDS01.SOD_TABLE SOD, WDS01.CSD_TABLE CSD
> WHERE SOD.SO_NBR = CSD.DMD_ORD_NBR GROUP BY CSD.DMD_PROJ_ONLY,
> CSD.DMD_PROJECT, CSD.SUP_PART, CSD.DMD_ORD_NBR, CSD.SUP_ORD_NBR,
> CSD.DMD_FLP_NBR HAVING CSD.SUP_ORD_NBR>' ';
> BEGIN
> OPEN c1;
> CLOSE c1;
> END schproc;
> /
>
> Warning: Procedure created with compilation errors.
>
> SQL> show errors
> Errors for PROCEDURE SCHPROC:
>
> LINE/COL
> ERROR
> --------
> -----------------------------------------------------------------
> 27/14 PL/SQL: SQL Statement
> ignored
> 27/102 PLS-00201: identifier 'WDS01.SOD_TABLE' must be
> declared
>
> //////*****
> Why these errors. It is not recognizing wds01.sod_table as a table.
> WDS01 is a valid oracle user that owns table sod_table.
> The oracle user that I'm connected as has select privilege on
> wds01.sod_table.
> ******///////
>
> Steve Walterhouse
> microcon_at_magicnet.net

The owner of this procedure must have explicit rights to the WDSO1.SOD_TABLE and WDS01.CSD_TABLE. The select rights you refer to cannot be granted via a role. See page 7-10 in the Oracle7 Server Applications Developer's Guide. It is explained pretty well there.

hth

-- 
"Came for the party, left on the run"

Robert Christenson
Gazette Technologies
robertoc#spam~be~gone#@fyiowa.infi.net