How to create a table using a function/procedure in plsql using dynamic user input

Question

How to create a table using function/procedure in plsql so that it should take table name as a dynamic variable and create that table with provided parameters ? A) I TRIED THIS :

CREATE OR REPLACE PROCEDURE CREATE_TABLE IS
UN VARCHAR2(20) :=&TABLE_NAME;

BEGIN
    CREATE TABLE UN(CUSTOMER_ID NUMBER,ORDER_DATE VARCHAR2(20),ORDER_MODE VARCHAR2(20),ORDER_STATUS NUMBER,SALES_REP_ID NUMBER,ORDER_TOTAL NUMBER,
    PROMOTION_ID CHAR,ERROR_MESSAGE VARCHAR2(30),RECORD_STATUS CHAR);
    end;

    insert into UN(select distinct CUSTOMER_ID,TO_CHAR(TO_TIMESTAMP(ORDER_DATE),'DD-MM-YYYY'),UPPER(ORDER_MODE),UPPER(ORDER_STATUS),SALES_REP_ID,ORDER_TOTAL,PROMOTION_ID,ERROR_MESSAGE,RECORD_STATUS 
    FROM LIKHITH where ORDER_TOTAL >0 AND ORDER_TOTAL NOT LIKE '%.%');
    IF COUNT(CUSTOMER_ID)=9 THEN 
    (
    UPDATE LIKHITH SET (RECORD_STATUS='E' AND ERROR_MESSAGE='ERR') WHERE (ORDER_TOTAL<0 OR ORDER_TOTAL NOT LIKE '%.%');
    UPDATE LIKHITH SET RECORD_STATUS='P' WHERE (ORDER_TOTAL<0 AND ORDER_TOTAL NOT LIKE '%.%');
    UPDATE UN SET RECORD_STATUS='P';
    )
    END IF;
   )
END;
/

Even if PL/SQL had create table (which it doesn't), this could never compile because the table UN cannot be resolved at compile time. &TABLE_NAME however will be resolved at compile time (assuming this is run by an application that supports substitution variables and uses the & character for them), which might not be what you want. — William Robertson
– William Robertson, Commented Nov 19, 2017 at 0:09
thanks for your response, I got what you are mentioning about and modified the code, it's finally working now. — Sai Likhith
– Sai Likhith, Commented May 3, 2020 at 13:22

Kaushik Nayak · Accepted Answer · 2017-11-18 11:39:43Z

You need to use Dynamic SQL. There were many redundant parentheses in your code which was not required.

Also this line IF COUNT(CUSTOMER_ID)=9 has no meaning. You should do a select from the table and do the comparison.

CREATE OR REPLACE PROCEDURE CREATE_TABLE 
                                        (  un VARCHAR2) 
IS
        v_count NUMBER;
BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE '|| 
        UN                               || 
        '                
(                        
CUSTOMER_ID   NUMBER,                        
ORDER_DATE    VARCHAR2(20),                        
ORDER_MODE    VARCHAR2(20),                        
ORDER_STATUS  NUMBER,                        
SALES_REP_ID  NUMBER,                        
ORDER_TOTAL   NUMBER,                        
PROMOTION_ID  CHAR,                        
ERROR_MESSAGE VARCHAR2(30),                        
RECORD_STATUS CHAR                
)';
        EXECUTE IMMEDIATE 'insert into '|| 
        UN                              || 
        '        
select distinct                
CUSTOMER_ID,                
TO_CHAR(TO_TIMESTAMP(ORDER_DATE),''DD-MM-YYYY''),                
UPPER(ORDER_MODE),                
UPPER(ORDER_STATUS),                
SALES_REP_ID,                
ORDER_TOTAL,                
PROMOTION_ID,                
ERROR_MESSAGE,                
RECORD_STATUS        
FROM                
LIKHITH        
where   ORDER_TOTAL  >0                
AND ORDER_TOTAL NOT LIKE ''%.%''' ;
        EXECUTE IMMEDIATE 'SELECT COUNT(CUSTOMER_ID) FROM ' || 
        UN INTO v_count;
        IF v_count = 9 THEN
                UPDATE
                        LIKHITH 
                SET     RECORD_STATUS         ='E' ,
                        ERROR_MESSAGE         ='ERR'
                WHERE   ORDER_TOTAL           <0
                        OR ORDER_TOTAL NOT LIKE '%.%';

        ELSE
                UPDATE
                        LIKHITH
                SET     RECORD_STATUS          ='P'
                WHERE   ORDER_TOTAL            <0
                        AND ORDER_TOTAL NOT LIKE '%.%';

                EXECUTE IMMEDIATE 'UPDATE '|| 
                UN                         || 
                ' SET RECORD_STATUS=''P''';
        END IF;
END;
/

Don't pass table name during compilation as you were trying.Pass it during execution as follows.

EXEC CREATE_TABLE('&table_name');

This answer seems good and I tried it out and it worked. Thanks for the input and that executable is passed now dynamically only.

Collectives™ on Stack Overflow

How to create a table using a function/procedure in plsql using dynamic user input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related