Stored Procedure Problem

From: John De Lello <JohnD_NO_SPAM_at_DelWare.com>
Date: Sun, 14 Mar 2004 16:54:18 GMT
Message-ID: <_605c.29579$Sp2.9083839@news4.srv.hcvlny.cv.net>

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 Received on Sun Mar 14 2004 - 10:54:18 CST