Dynamic query with columns and table names from multiple tables using SQL or PL/SQL

Question

Lets assume (for simplicity) i have two tables:

Product
 product_id
 CreatedDate
 LastEditedBy
 EditedDate

Entity
 entity_id
 CreatedDate
 LastEditedBy
 EditedDate

Both tables have the same column names but only the ID column has a different name. I would like to run a query so that when i run it from SQL plus i just give it a parameter and it gets the data from one of the tables. In the above example i could run the query like this

@archiveHistory.sql product
@archiveHistory.sql entity

Here is my attempt but it always failed to recognise one of the columns, i.e. if i run it with product, it says entity_id does not exist. if i run it with entity it says product_id does not exist.

Note that i am using the passed in parameter on both the column selection and the table name selection.

define identifier = '&1'

Select * from (
 Select case lower('&identifier')
  when product then product_id 
  when entity then entity_id
  end ID, CreatedDate, LastEditedBy, EditedDate
 From &identifier
)

I think it will work if the column list in the CASE statement were all from the same table.

Questions

• What do i need to do so the query ignores the column that is not relevant i.e. ignore product_id if the argument is entity

• I thought about using an anonymous PL/SQL block (i.e. Begin End) but i am not sure how i can display the output without using dbms_output.put_line.

Answer 1

For this particular case, I think the following SQL-only solution might work best:

SELECT product_id AS the_field
     , CreatedDate, LastEditedBy, EditedDate
FROM   Product
WHERE  LOWER('&identifier') = 'product'
UNION ALL
SELECT entity_id AS the_field
     , CreatedDate, LastEditedBy, EditedDate
FROM   Entity
WHERE  LOWER('&identifier') = 'entity'

The query planner will pre-evaluate your '&identifier' = ... predicates, which prevents execution of the unneeded union subquery.

If that's not an option (because your real-world use-case is much more complex), there are plenty of answers on Stack Overflow already regarding the execution of dynamic SQL from PL/SQL:

• Executing a dynamic sql statement into a SYS_REFCURSOR
• Cursor For Loop with dynamic SQL-Statement
• Binding Parameters to Oracle Dynamic SQL

You could use dynamic SQL to insert your data into a temp table, and then simply SELECT * FROM temp_table