Oracle using dynamic sql when table name is a parameter

Question

I have a SQL query (a store procedure ) that i want to convert to PLSQL I already conver most of the store procedure but i cant convert the following part :

DECLARE lookupTableRow CURSOR FOR
  SELECT TableName FROM SYS_LookUpTable
  OPEN lookupTableRow
  FETCH NEXT FROM lookupTableRow INTO @tableName
  WHILE @@FETCH_STATUS=0
  BEGIN

  SET @sql='SELECT * FROM '+@tableName
EXECUTE sp_executesql @sql

  IF @counter=0
  BEGIN
  INSERT INTO T_TABLE_MAPPING VALUES('P_MAIN_METADATA', 'Table', @tableName)
  END
  ELSE
  BEGIN
  INSERT INTO T_TABLE_MAPPING 
      VALUES(  'P_MAIN_METADATA', 
               'Table' + CONVERT(NVARCHAR(10),@counter), 
               @tableName)
  END

  SET @counter=@counter+1
  FETCH NEXT FROM lookupTableRow INTO @tableName
  END
  CLOSE lookupTableRow
  DEALLOCATE lookupTableRow

As i understand i can't use ORACLE dynamic sql (execute immediate) when the table name is a parameter.

Furthermore when i execute this dynamic query in my SQL store procedure each SELECT statement return me as a result the relevant table rows , those result are different in each loop .

Please advice for any solution * how can i use dynamic sql with table name as parameter ? * how can i use a "dynamic" cursor, in order to be able to display the dynamic results ?

Thanks for the advice

what does this 'Table' + CONVERT(NVARCHAR(10),@counter) do on this code? — Jorge Campos
– Jorge Campos, Commented Dec 30, 2013 at 12:55
It appears you're trying to obfuscate actual table names and assign them a name of Table# in a T_TableMapping. if so would something like this work? Select owner, table_name, 'Table' || to_char(rownum) from all_tables order by owner, table_name — xQbert
– xQbert, Commented Dec 30, 2013 at 13:06
But on SQL the following code is a SELECT statement . This SELECT is changing on each loop . The tables result from the SELECT are fill into a data set (in the c# code) So i need to find a way to how to do this : SET @sql='SELECT * FROM '+@tableName EXECUTE sp_executesql @sql — Refael Cohen
– Refael Cohen, Commented Dec 30, 2013 at 13:47
@RefaelCohen I have a SQL query (a store procedure ) SQL is programming language which is industry standard for many RDBMS. You probably mean Transact-SQL or T-SQL stored procedure which is part of MS SQL Server product. Please use proper terms to avoid confusion. — Yaroslav Shabalin
– Yaroslav Shabalin, Commented Dec 30, 2013 at 14:25

haki · Accepted Answer · 2013-12-30 12:55:47Z

1

If the only thing you don't know how to do here is using a table name dynamically then this is how you can accomplish that

  1  declare
  2     n number;
  3     table_name varchar2(30) := 'dual';
  4  begin
  5     execute immediate 'select count(*) from ' || table_name into n;
  6     dbms_output.put_line(n);
  7* end;
SQL> /
1

answered Dec 30, 2013 at 12:55

haki

9,81115 gold badges67 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Refael Cohen Over a year ago

I solve this problem in the "old way" I ran this select statement : SELECT TableName FROM SYS_LookUpTable In order to see all the relevant table name Then i simple create a select statement for each table name Yes i know that this not the elegant way, and i know that i wrote about 41 select statement in order to achieve the same result like i get in the sql server But i didnt find other way to do this simple query in ORACLE

haki Over a year ago

I wouldent say its ugly in oracle. Maybe say what you are trying to do and we can help you get there in "the oracle way"

xQbert · Accepted Answer · 2013-12-30 13:16:11Z

0

If you're simply trying to populate a table that obfuscates table names to something else, why do it sequentially forcing the db to change contexts and slow down?

Create table t_table_mapping as 
(    Select 'P_MAIN_METADATA' "P_MAIN_METADATA", 
      'Table' || to_char(rownum) as "Table", table_name 
     from all_tables)

However this doesn't take into account schema names so what owner. So you need to add a where clause to limit to the specific schema you're wanting to do this for.

answered Dec 30, 2013 at 13:16

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

Oracle using dynamic sql when table name is a parameter

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related