Re: Binding variables (DBMS_SQL)

Thanks for the explanation, it was really useful. I'll stick to string concatenation when using DDL from now on.

Morten

On Mon, 5 Jul 1999, Thomas Kyte wrote:

> 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 Tue Jul 06 1999 - 03:22:31 CDT