How do I concatenate variables with a string to create an Oracle table name in a FROM clause in a stored procedure?

Question

e.g.

select * 
  from v_schema || '.tbl_a@' || abc.world

Answer 1

You're not really supposed to do this but here are several ways.

Answer 2

You can use dynamic sql:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm .

To protect against sql injection you can use dbms_assert:

http://www.oracle-base.com/articles/10g/dbms_assert_10gR2.php

Answer 3

-- Dynamic SQL

execute immediate 'select * from ' || v_schema || '[email protected]';