Forum Stats

  • 3,874,734 Users
  • 2,266,768 Discussions
  • 7,911,961 Comments

Discussions

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

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

Best Answer

  • KayK
    KayK Member Posts: 1,740 Bronze Crown
    Answer ✓

    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)
    


«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,758 Red Diamond
    edited Apr 27, 2022 11:31AM

    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
    BEDE Oracle Developer Member Posts: 2,475 Gold Trophy
    edited Apr 27, 2022 11:37AM

    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
    KayK Member Posts: 1,740 Bronze Crown
    edited Apr 27, 2022 11:39AM

    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!

  • AshK-Oracle
    AshK-Oracle Posts: 16 Employee
    edited Apr 27, 2022 12:38PM

    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
    KayK Member Posts: 1,740 Bronze Crown
    edited Apr 27, 2022 1:11PM

    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
    Frank Kulash Member, Moderator Posts: 42,758 Red Diamond

    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.)

  • AshK-Oracle
    AshK-Oracle Posts: 16 Employee
    edited Apr 27, 2022 1:24PM

    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
    Frank Kulash Member, Moderator Posts: 42,758 Red Diamond

    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.

  • AshK-Oracle
    AshK-Oracle Posts: 16 Employee

    Thanks for the detailed query.


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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,758 Red Diamond

    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.