CODEWITHSUNDEEP
sqloracle-databasevariables
javakid1993
javakid1993

Reputation: 255

Inserting query results into table named by a variable

I have a stored procedure which runs many queries and obtains a lot of values which it stores in variables. I want this procedure to be able to be able to insert the query results into a table which is given by the user. I would store the given table name as a varchar parameter but how can I insert into this table?

At compile time, Oracle is saying that the table does not exist.

Upvotes: 0

Views: 323

Answers (2)

Alex Poole
Alex Poole

Reputation: 191425

Presumably it's telling you that a table with the name of the variable you're using doesn't exist, which of course it won't. The actual table may or may not exist at compile time; since it's flexible it's probably safer to assume it might not. Either way you don't know what it will be so you need to use dynamic SQL to achieve this.

As mentioned in a comment on another answer, you have to be careful about SQL injection. Normally you'd want to use bind variables in the dynamic SQL, but you can't use binds for object names, so you have to concatenate it. Hopefully you're using 11g, which includes the dbms_assert package.

Here's a simple example:

create or replace procedure p42 (table_name varchar2) as
begin
  execute immediate 'insert into '
    || dbms_assert.qualified_sql_name(table_name)
    || ' select * from dual';
end;
/

I can then create a table after the procedure already exists, and successfully call the procedure:

create table t42 (dummy varchar2(1));

exec p42('t42');

select * from t42;

DUMMY
-----
X

Your real query will obviously be more complicated, and should use bind variables for any filter values you're passing in along with the target table name.

The advantage of the dbms_assert call is that it will error if something illegal is passed in, and if something nasty is passed:

exec p42('t42 select ''Y'' from dual union all');

ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 207
ORA-06512: at "STACKOVERFLOW.P42", line 3
ORA-06512: at line 1

If the procedure simply concatenated the passed value:

  execute immediate 'insert into ' || table_name || ' select * from dual';

... then that same call would insert two rows into the table:

exec p42('t42 select ''Y'' from dual union all');

select * from t42;

DUMMY
-----
Y     
X

Which is something to worry about if the data integrity is at all important to you. If you can't use dbms_assert then you could try to check that the passed name actually exists in all_tables and doesn't contain anything like a union etc. but it's much safer to acknowledge that you won't think of all the possible attacks and let the built-in function do the hard work for you.

Upvotes: 5

Metaphor
Metaphor

Reputation: 6415

Try something like this:

exec ('insert into ' + @tblname + ' (col) values (123)')

Upvotes: 0

Related Questions