problem with bind var in procedure

From: Michael J Belgard <mj.belgard_at_postoffice.worldnet.att.net>
Date: 1997/12/11
Message-ID: <66p4vu$jc2_at_bgtnsc02.worldnet.att.net>#1/1

This procedure is not recognizing the :x bind variable for grad_year Do you know why?

Thanks,

Mike

CREATE OR REPLACE PROCEDURE TESTADM119( operator varchar2,

     grad_year   high_school_attended.grad_year%TYPE
      ) AS

  CURSOR1 INTEGER;
  ROWS_PROCESSED INTEGER;
  entity_ID prospect.entity_id%TYPE;
  CLASS_RANK_CODE prospect.class_rank_code%TYPE;   v_select_stmt VARCHAR2(300);

/* This is the command to execute the procedure:

 execute TESTADM119('>=',1998); */

begin

  CURSOR1 := DBMS_SQL.OPEN_CURSOR;   DBMS_OUTPUT.PUT_LINE(grad_year);

         v_select_stmt := 'SELECT DISTINCT P.ENTITY_ID, P.CLASS_RANK_CODE

                FROM PROSPECT P, MIX_CODE_LOG M, HIGH_SCHOOL_ATTENDED H
                 WHERE P.ENTITY_ID=M.PROSPECT_ID AND
P.ENTITY_ID=H.PROSPECT_ID and
            H.GRAD_YEAR '||operator||'  :x';

  DBMS_OUTPUT.PUT_LINE(v_select_stmt);

  DBMS_SQL.PARSE (CURSOR1, v_select_stmt, DBMS_SQL.V7);

         dbms_sql.bind_variable(CURSOR1, ':x', grad_year);
         dbms_sql.bind_variable(CURSOR1, ':y', operator);

  dbms_sql.define_column(CURSOR1, 1, entity_ID);   dbms_sql.define_column(CURSOR1, 2, CLASS_RANK_CODE,10);

  ROWS_PROCESSED := dbms_sql.execute(CURSOR1);  loop

  if dbms_sql.fetch_rows (CURSOR1) > 0 then
    dbms_sql.column_value ( CURSOR1, 1, entity_ID);
    dbms_sql.column_value ( CURSOR1, 2, CLASS_RANK_CODE);
                  DBMS_OUTPUT.PUT_LINE(TO_CHAR(entity_ID)||'    '||

CLASS_RANK_CODE);
  ELSE
    EXIT;
  END IF;
 END LOOP;  DBMS_SQL.CLOSE_CURSOR (CURSOR1);     EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(sqlerrm);
  if dbms_sql.is_open(CURSOR1) then
   DBMS_SQL.CLOSE_CURSOR (CURSOR1);
  end if;
  end;
/

When I try to run it this is what happens:

SQL> execute TESTADM119('>=',1998);
1998
SELECT DISTINCT P.ENTITY_ID, P.CLASS_RANK_CODE

                FROM PROSPECT P, MIX_CODE_LOG M,
HIGH_SCHOOL_ATTENDED H
                 WHERE P.ENTITY_ID=M.PROSPECT_ID AND
P.ENTITY_ID=H.PROSPECT_ID and
            H.GRAD_YEAR >=  :x

ORA-01006: bind variable does not exist

PL/SQL procedure successfully completed. Received on Thu Dec 11 1997 - 00:00:00 CET