Binding variables (DBMS

From: Morten <c960901_at_aix5.kbar.dtu.dk>
Date: Mon, 5 Jul 1999 14:25:07 +0200
Message-ID: <Pine.A41.3.95.990705142040.18258A-100000@aix5.kbar.dtu.dk>

Hi, I've experienced (nothing but) trouble trying to bind variables. The code (below) compiles but upon execution I get:

ERROR at line 1:

ORA-04070: invalid trigger name
ORA-06512: at "BIIS.DROPTRIG", line 43
ORA-06512: at line 1

The line that goes bad is where I parse. I've had similar problems trying to use variable binding with a table name. I'm trying to understand what I'm messing up here, all comments greatly appreciated. Thanks.

Script to perform 'drop trigger NAME' where NAME is found as
trigger_name in user_triggers

CREATE OR REPLACE PROCEDURE DropTrig AS

v_CursorA  NUMBER;
v_CursorB  NUMBER;
v_Dummy    INTEGER;
v_Query    VARCHAR(100);
v_Drop     VARCHAR(100);
v_Trignam  user_triggers.trigger_name%TYPE;

BEGIN
DBMS_OUTPUT.ENABLE(1000000); v_CursorA := DBMS_SQL.OPEN_CURSOR;
v_CursorB := DBMS_SQL.OPEN_CURSOR;

v_Query := 'SELECT trigger_name FROM user_triggers'; v_Drop := 'DROP TRIGGER :name';

DBMS_SQL.PARSE(v_CursorA, v_Query, DBMS_SQL.V7);

JUST BELOW IS WHERE THINGS GO BAD DBMS_SQL.PARSE(v_CursorB, v_Drop, DBMS_SQL.V7);

DBMS_SQL.DEFINE_COLUMN(v_CursorA, 1, v_Trignam, 32);

v_Dummy := DBMS_SQL.EXECUTE(v_CursorA);

  LOOP
    IF DBMS_SQL.FETCH_ROWS(v_CursorA) = 0 THEN       EXIT;
    END IF;     DBMS_SQL.COLUMN_VALUE(v_CursorA, 1, v_Trignam);

DBMS_OUTPUT.PUT_LINE('Dropping '||v_Trignam);

DBMS_SQL.BIND_VARIABLE(v_CursorB, ':name', v_Trignam); v_Dummy := DBMS_SQL.EXECUTE(v_CursorB); END LOOP; EXCEPTION
WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('DropTrig error');
      DBMS_SQL.CLOSE_CURSOR(v_CursorA);
      DBMS_SQL.CLOSE_CURSOR(v_CursorB);

  RAISE;
  DBMS_SQL.CLOSE_CURSOR(v_CursorA);
  DBMS_SQL.CLOSE_CURSOR(v_CursorB);
END;
/

Morten Received on Mon Jul 05 1999 - 07:25:07 CDT

Binding variables (DBMS_SQL)