Re: Stored Procedure Problem

John De Lello wrote:

> Can someone please explain why the following stored procedure will not
> compile successfully? It will work fine IF I replace ":v_iGroupID" in the
> WHERE clause with a number. But if I leave it the way I need it to be
> (below) it fails to compile.
>
> What am I missing?
>
> CREATE OR REPLACE PACKAGE pkg_ESFC
> IS
> TYPE rec_GetGroupSids IS RECORD
> (n_gr_id number,
> n_si_id number,
> l_gr_hadauth number,
> l_gr_hasauth number);
>
> TYPE cur_GetGroupSids IS REF CURSOR
> RETURN rec_GetGroupSids;
> END;
>
> CREATE OR REPLACE PROCEDURE sys_GetGroupSIDs(resultcursor out
> pkg_ESFC.cur_GetGroupSids, v_iGroupID IN OUT number)
> AS
> BEGIN
>
> OPEN resultcursor FOR
> SELECT n_gr_id "n_gr_id",
> GroupRights.n_si_id "n_si_id",
> 1 "l_gr_HadAuth",
> 1 "l_gr_HasAuth"
> FROM GroupRights
> WHERE GroupRights.n_grp_id = :v_iGroupID
> AND GroupRights.l_gr_HasAuth = 1
> UNION
> SELECT 0,
> n_si_id,
> 0,
> 0
> FROM SecurityItems
> WHERE n_si_id NOT IN ( SELECT n_si_id FROM GroupRights WHERE
> GroupRights.n_grp_id = 1 AND GroupRights.l_gr_HasAuth = 1);
> END;
>
> Thank you
> John D

You referenced the type not the cursor in your OPEN.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)