Re: Binding variables (DBMS_SQL)

I think the only error is in this line:

> DBMS_SQL.BIND_VARIABLE(v_CursorB, ':name', v_Trignam);

The ':' should not be there. The name of the variable is 'name', the ':' appears in the SQL text to identify it as a bind variable.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Morten wrote in message ...
>
>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 - 13:22:51 CDT