CODEWITHSUNDEEP
oracle-database
Moudiz
Moudiz

Reputation: 7377

Query quotes in execute statement

I have this correct query (I am getting the value from a procedure, my problem is with the quotes):

EXECUTE IMMEDIATE ('insert into TABLES (table_name)
  values ('||''''||TABLE_NAME||''''||')');

When I try to add another column, I get an error, can anybody add the correct quotes? This one is incorrect:

EXECUTE IMMEDIATE ('insert into TABLES (table_name,column_name)
  values ('||''''||TABLE_NAME||''''||'',''||column_name||''''||')');

Upvotes: 0

Views: 690

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

For the statement you have:

EXECUTE IMMEDIATE ('insert into TABLES (table_name,column_name)
  values ('''||TABLE_NAME||''','''||column_name||''')');

But you should use bind variables, which are much easier to construct and read as well as much safer:

EXECUTE IMMEDIATE ('insert into TABLES (table_name,column_name)
  values (:table_name,:column_name)') USING TABLE_NAME, COLUMN_NAME;

And as noted in comments this doesn't need to be dynamic at all; or at least it wouldn't if you had called your variables something different to the column names - which still works but looks ambiguous:

insert into TABLES (table_name,column_name)
values (TABLE_NAME,COLUMN_NAME);

You can specify the scope to make things clerarer; assuming your procedure is called MY_PROC you can do:

insert into TABLES (table_name,column_name)
values (MY_PROC.TABLE_NAME,MY_PROC.COLUMN_NAME);

Upvotes: 2

Bacs
Bacs

Reputation: 919

Assuming you even need dynamic sql - I don't know the context in which you're attempting this - your quotes problem would be sorted as follows:

EXECUTE IMMEDIATE ('insert into TABLES (table_name) values ('''||TABLE_NAME||''')');

EXECUTE IMMEDIATE ('insert into TABLES (table_name,column_name) values ('''||TABLE_NAME||''','''||column_name||''')');

But really you should be using bind variables:

declare v_table_name varchar2(30); 

begin

    -- get your v_table_name here

    EXECUTE IMMEDIATE ('insert into TABLES (table_name) values (:TABLE_NAME)')
    USING v_table_name;

end;

Upvotes: 1

Related Questions