Print bind variables when using execute immediate

Question

i'm trying to print out the vSQL variable after the execute immediate statement. i know the execute immediate statement ll give ORA-00903 because u can not bind variables to tables etc.i just want to print that string after using statement.

here is my code :

DECLARE 
vSQL VARCHAR2(100);
vOwner VARCHAR2(100);
vTableName VARCHAR2(100);
vPartition VARCHAR2(100);
BEGIN
    vOwner := 'STG';
    vTableName := 'TEMP';
    vSQL := 'ALTER TABLE :1.:2 TRUNCATE PARTITION(:3)';
    EXECUTE IMMEDIATE vSQL USING vOwner,vTableName,vPartition;
    --DBMS_OUTPUT.PUT_LINE(vSQL); -- Something like this
END;

thx for ideas...

It's not allowed to bind identifiers. If you simply want to print that alter table statement why is the execute immediate statement there? It won't ever be successfully executed anyway unless you drop the idea of trying to bind identifiers. — Nick Krasnov
– Nick Krasnov, Commented Jan 3, 2014 at 11:01
Bind variable cant be use to set schema objects! Form a string instead, no other way! — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Jan 3, 2014 at 11:29
In your case, vSQL never gets modified when you run it along with the bind variable, if you think that :1 will be replaced with vOwner value, it is not possible. Oracle do all the substitutions during execution time. — San
– San, Commented Jan 3, 2014 at 11:44
so is it possible to trace the substitutions during execution time ? — Deniz
– Deniz, Commented Jan 3, 2014 at 15:16

Lajos Veres · Accepted Answer · 2014-01-03 11:46:08Z

3

As the commenters wrote you can bind only data to fields, you can't bind library object's names/etc.

So I think you can do it only without binding:

DECLARE 
vSQL VARCHAR2(100);
vOwner VARCHAR2(100);
vTableName VARCHAR2(100);
vPartition VARCHAR2(100);
BEGIN
    vOwner := 'STG';
    vTableName := 'TEMP';
    vSQL := 'ALTER TABLE ' || vOwner || '.' || vTableName || ' TRUNCATE PARTITION('||vPartition||')';
    EXECUTE IMMEDIATE vSQL;
    DBMS_OUTPUT.PUT_LINE(vSQL);
END;

answered Jan 3, 2014 at 11:46

Lajos Veres

13.7k7 gold badges47 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Deniz Over a year ago

You get my question wrong , i already know how to work the query in a proper way. I just wanted to know how can i trace(debug) the code when it fails like my code.

Lajos Veres Over a year ago

I don't understand 100%. This substitution won't ever happen. If this is only a fake example for something what could happen then maybe some kind of tracing could help and you could extract from the related udump/etc files what happened internally.

Dzyann · Accepted Answer · 2015-01-28 14:36:56Z

0

If you know an error is going to happen, catch it and then print.

DECLARE 
vSQL VARCHAR2(100);
vOwner VARCHAR2(100);
vTableName VARCHAR2(100);
vPartition VARCHAR2(100);
BEGIN
    vOwner := 'STG';
    vTableName := 'TEMP';
    vSQL := 'ALTER TABLE :1.:2 TRUNCATE PARTITION(:3)';
    EXECUTE IMMEDIATE vSQL USING vOwner,vTableName,vPartition;

    EXCEPTION   --The error will get catched here.     
        WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE(vSQL); --This will print exactly 'ALTER TABLE :1.:2 TRUNCATE PARTITION(:3)'
           --If you want to print it with the variables do something like:
           DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || vOwner || ', ' || vTableName || ', ' || vPartition; 

END;

answered Jan 28, 2015 at 14:36

Dzyann

5,27812 gold badges67 silver badges103 bronze badges

Collectives™ on Stack Overflow

Print bind variables when using execute immediate

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related