Re: Stored Procedure Problem

Hi Daniel,

Thanks for the reply. I'm not really sure I understand what you are saying. How would I modify the OPEN to allow a variable in the WHERE then?

Thank you
John D

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1079288328.347713_at_yasure...
> 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)
>
Received on Sun Mar 14 2004 - 14:12:08 CST