Is it possible to use a variable or param as the name of a table in a select statement?

[Matt Kohanek]

I am just wondering if something like this is possible - I know the following example does not work because I tried it, but is there a way to achieve something similar?

DECLARE
   v_dual VARCHAR2(300):= 'dual';
   v_var VARCHAR2(20);
BEGIN
   SELECT '42' into v_var FROM v_dual;  
   dbms_output.put_line(v_var); 
END;

[Martin Vajsar]

The construct you're looking for is called "dynamic sql". You can use the execute immediate statement to execute an SQL which is a result of a VARCHAR2/CLOB expression; you can even use bind variables with it (look up that statement in Oracle's docs). More complicated things can be handled with DBMS_SQL package.

In your case the line would be

execute immediate 'select ''42'' from ' || v_dual into v_var;

Be careful, though. By stuffing literal values into the SQL commands you can easily expose yourself to SQL injection even within PL/SQL.

[Matt Kohanek]

Martin

Yeah, SQL Injection is exactly what I am working on. I am removing all occurrences of what you mention. But for some parts of the code, say we have two tables:

us_table
canada_table

And the execute immediate is something like 'select id from '||region||'_table where 1=1'

I have just used if statements to deal with this (luckily there are only two possibilities where this occurs in our code), but I was just wanting to be sure I was using best practice.

Thanks

[Martin Vajsar]

For similar things, you could use the DBMS_ASSERT package, that can help to validate a string is a valid identifier, for example. Or when you construct the table name, you could verify that it is an actual table name against USER_TABLES or ALL_TABLES dictionary views.