Re: Stored Procedure Problem

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sun, 14 Mar 2004 21:58:06 +0000
Message-ID: <pik950pagdh97rcsg7eiqchsnigcmpp2q6@4ax.com>

On Sun, 14 Mar 2004 16:54:18 GMT, "John De Lello" <JohnD_NO_SPAM_at_DelWare.com> 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?

 Posting the error would be good. My guess is 'bad bind variable'. Your point about replacing the :v_iGroupID with a literal looks to me to be a clue.

>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

 Remove the colon. You only need to mark bind variables with colons inside strings if it appears in a dynamic string - i.e. the SQL is in a string literal or variable.

 Since this is an open-for with a literal SQL statement, PL/SQL turns variables into bind variables as per usual - the colon is not required, and causes an error.

 Adapting the example from Daniel's site from his post:

SQL> CREATE OR REPLACE PROCEDURE child (   2 p_NumRecs IN PLS_INTEGER,
  3 p_return_cur OUT SYS_REFCURSOR)
  4 IS
  5 BEGIN

  6     OPEN p_return_cur FOR
  7         SELECT * FROM all_tables WHERE rownum <= :p_NumRecs;

  8 END child;
  9 /

Warning: Procedure created with compilation errors

SQL> show errors;
Errors for PROCEDURE TEST.CHILD:

LINE/COL ERROR

-------- ----------------------------------------
7/49     PLS-00049: bad bind variable 'P_NUMRECS'

SQL> CREATE OR REPLACE PROCEDURE child (   2 p_NumRecs IN PLS_INTEGER,
  3 p_return_cur OUT SYS_REFCURSOR)
  4 IS
  5 BEGIN

  6     OPEN p_return_cur FOR
  7         SELECT * FROM all_tables WHERE rownum <= p_NumRecs;

  8 END child;
  9 /

Procedure created

 (and for completeness, the dynamic-string-with-bind-variable option):

SQL> CREATE OR REPLACE PROCEDURE child (   2 p_NumRecs IN PLS_INTEGER,
  3 p_return_cur OUT SYS_REFCURSOR)
  4 IS
  5 BEGIN

  6     OPEN p_return_cur FOR
  7        'SELECT * FROM all_tables WHERE rownum <= :p_NumRecs'
  8        using p_NumRecs;

  9 END child;
 10 /

Procedure created

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

Received on Sun Mar 14 2004 - 15:58:06 CST