Re: Binding variables (DBMS_SQL)

A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Mon, 5 Jul 1999 19:22:51 +0100, you wrote:

>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.
>

The : can be there -- its optional in the call to bind_variable. the error is in what they are trying to bind:

>> v_Drop := 'DROP TRIGGER :name';

You cannot bind identifiers -- you can only bind where a character string constant can go -- for example, since:

SQL> drop trigger 'MYTRIGGER';

is not valid, drop trigger :name is not valid.

You can bind:

select * from emp where ename = :x

since it is valid to say

select * from emp where ename = 'X';

but you cannot code:

select * from :x where ename = 'X'; since it is not valid to code:

select * from 'EMP' where ename = 'X';

You cannot do the bind in DDL at all -- no DDL accepts bind variables. You can only bind in DML and only when you could have put a character string constant in its place....

>
>--
>
>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
>>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jul 05 1999 - 14:09:21 CDT