How to create dynamic table while inserting the output of another query into it?

User_5U12Z

CREATE TABLE && as SELECT transaction_number FROM (inner query);

This is a dummy query. I want to create a dynamic table and then insert the output of that query in to the dynamic table

KayK

That works for me. But i got not the error you got.

I've got ORA-00918: column ambiguously defined because both of your tables have a column named transaction_number and you select with *, so you get all columns of both tables.

CREATE OR REPLACE PROCEDURE Pro_tbl_create( inbound_rvsl_tbl VARCHAR2)
IS
 sql_stmt VARCHAR2 (1000);    -- missing R in your code
BEGIN
 sql_stmt := 'CREATE TABLE ' || inbound_rvsl_tbl
     || ' AS SELECT transaction_number'
    || '  ,  ''D'' as status'
    || '  FROM (SELECT xth.transaction_number   -- explicit column_name
 FROM
   xla_transaction_headers xth,
   xla_transaction_reversals_int xtr
 WHERE
   xth.transaction_number = xtr.transaction_number
 AND xth.transaction_date > xtr.REVERSAL_DATE)';
 --dbms_output.put_line (sql_stmt || ' = CREATE TABLE statement in pro_tbl_create');
 EXECUTE IMMEDIATE sql_stmt;

 sql_stmt := 'INSERT INTO '    
         || inbound_rvsl_tbl  
    || ' SELECT transaction_number'
    || ' ,  ''I'' as status'
    || ' FROM (SELECT xth.transaction_number    -- explicit column_name
 FROM
   xla_transaction_headers xth,
   xla_transaction_reversals_int xtr
 WHERE
   xth.transaction_number = xtr.transaction_number
 AND xth.transaction_date > xtr.REVERSAL_DATE)';
 --dbms_output.put_line (sql_stmt || ' = INSERT statement in pro_tbl_create');
END pro_tbl_create;
/

< scott:[email protected] > execute Pro_tbl_create( 'TEST_KK_TR');

PL/SQL-Prozedur erfolgreich abgeschlossen.

< scott:[email protected] > desc TEST_KK_TR
 Name                                     Null?   Typ
 ----------------------------------------- -------- ---------
 TRANSACTION_NUMBER                                NUMBER
 STATUS                                            CHAR(1)

Frank Kulash

Hi, @User_5U12Z

CREATE TABLE && as SELECT transaction_number FROM (inner query);

creates a table with as many rows as the inner query returns. If the inner_query produces 5 rows, then the table is created with 5 rows. If the inner query produces 99 rows, then the table is created with 99 rows.

To create a table with 0 rows, use a query that produces 0 rows. Add a WHERE clause with a condition that is never true, e.g.

CREATE TABLE table_x
AS 
SELECT   transaction_number 
FROM     (inner_query)
WHERE    0 = 1;

BEDE

The idea in itself is very wrong. Once you create a table dynamically you must use dynamic SQL to manipulate data in it or create dynamically also stored procedures that manipulate data in it.

Yet, by what you have shown, it looks like you intend to use sqlplus with substitution variables and not what is dynamic SQL (that is executed with execute immediate or using dbms_sql).

If it's that you are using sqlplus or sqldeveloper, then, with: create table &tba as &xselect; you will be propted to enter manually the table name tba and the select statement.

If you really wish to learn about dynamic sql RTM on execute immediate and, eventually, on dbms_sql.

Moreover, what you show so far is prone to SQL injection.

KayK

Hi 5U,

welcome to the community.

Your statement looks good, what happens ? Ok, you need a last a name behind the &&

Something like this works for me.

< scott:[email protected] > CREATE TABLE &&new_table as SELECT ename FROM (select * from emp);
Enter value for new_table: test_kk1

Table created.

< scott:[email protected] > desc test_kk1
 Name                                     Null?   Typ
 ---------------------------------------- ------- ----------------------------
 ENAME                                            VARCHAR2(10)

regards

Kay

btw please choose a more readable name for your avatar. You're more than a number. Have a look at this: Update Your Community Display Name and Avatar!

User_5U12Z

https://community.oracle.com/tech/developers/discussion/comment/16833866#Comment_16833866

Yes, let me correct the query.

This query would be correct?

CREATE OR REPLACE PROCEDURE Pro_tbl_create

IS

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE &&inbound_rvsl_val AS SELECT transaction_number,status FROM (inner query);'

END; 

Also in the above query I always want the status to be 'D'. Status column does not come from the inner query, how could I do this?

Also thanks for such and detailed explanation.

KayK

Maybe this produces your status 'D'

EXECUTE IMMEDIATE 'CREATE TABLE &&inbound_rvsl_val AS SELECT transaction_number, ''D'' as status FROM (inner query)'

Frank Kulash

Hi, @User_5U12Z

Also in the above query I always want the status to be 'D'. Status column does not come from the inner query, how could I do this?

Here's one way:

CREATE TABLE &&inbound_rvsl_val 
AS 
SELECT  transaction_number,
,       'D'  AS status
FROM (inner_query)
;

This will not affect rows added to the table later. You may want to add a CHECK constraint, or use a view to ensure that status is always 'D' on rows added later.

An EXECUTE IMMEDIATE statement ends with a semicolon, but the dynamic string that you're executing does not. (That is, you don't want a semicolon inside the single-quotes.)

User_5U12Z

https://community.oracle.com/tech/developers/discussion/comment/16833877#Comment_16833877

This is the query below.

CREATE OR REPLACE PROCEDURE Pro_tbl_create(inbound_rvsl_tbl VARCHAR2)

IS

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE &&inbound_rvsl_tbl AS SELECT transaction_number,''D'' as status FROM (select * from xla_transaction_headers)';

EXECUTE IMMEDIATE 'INSERT INTO TABLE &&inbound_rvsl_tbl AS SELECT transaction_number,''I'' as status FROM (select * from xla_transaction_reversals_int)';

END;

How do we call such procedures?

Execute Pro_tbl_create(inbound_rvsl_tbl); This command isnt working. The parameter I am passing is the table name. 'inbound_rvsl_tbl' is the table name I want to use for creating the table.

Frank Kulash

Hi,

EXECUTE IMMEDIATE 'CREATE TABLE &&inbound_rvsl_val AS SELECT transaction_number,status FROM (inner query);'

The parameter inbound_rvsl_val (given when you call the procedure) is completly independent of the substitution variable &&inboud_rvsl_val (given when you compile the procedure). I'm guessing ypu don't want a substitution varibale her at all; instead, ypou want something like this:

CREATE OR REPLACE PROCEDURE Pro_tbl_create(inbound_rvsl_tbl VARCHAR2)
IS
  sql_stmt  VACHAR2 (1000);
BEGIN
  sql_stmt := 'CREATE TABLE ' || inbound_rvsl_tbl 
  	   || ' AS SELECT transaction_number'
	   || '    ,    ''D''  as status'
	   || '    FROM  (select * from xla_transaction_headers)';
   dbms_output.put_line (sql_stmt || ' = CREATE TABLE statement in pro_tbl_create') -- FOR DEBUGGING ONLY
   EXECUTE IMMEDIATE sql_stmt;

  sql_stmt := 'INSERT INTO '      -- TABLE doesn't belong here
           || inbound_rvsl_tbl    -- AS doesn't belong here
	   || ' SELECT transaction_number'
	   || ' ,    ''I'' as status'
	   || ' FROM  (select * from xla_transaction_reversals_int)';
   dbms_output.put_line (sql_stmt || ' = INSERT statement in pro_tbl_create') -- FOR DEBUGGING ONLY
END  pro_tbl_create;
/

As always, seek alternate rputes. Think carefully before using dynamic SQL, especially before using it to create tables.

If ypu really do need to use dynamic SQL, always put the entire SQL statement into a string variable (such as sql_stmt above), and display it while you are writing the procedure and during initial tests. The calls to dbms_output should be commented out before moving the procedure out of development.

User_5U12Z

https://community.oracle.com/tech/developers/discussion/comment/16833889#Comment_16833889

Thanks for the detailed query.

Wont there be "EXECUTE IMMEDIATE sql_stmt;" even after the INSERT INTO statement?

Frank Kulash

Hi, @User_5U12Z

Wont there be "EXECUTE IMMEDIATE sql_stmt;" even after the INSERT INTO statement?

Yes. Since you didn't post any sample data (CREATE TABLE and INSERT statements for all the tables involved), nobody can test any of the replies you receive, and you can expect them to have silly mistakes.