CODEWITHSUNDEEP
oracle-databasefunctionplsqlprocedure
Sai Likhith
Sai Likhith

Reputation: 21

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

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;
/

Upvotes: 0

Views: 120

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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');

Upvotes: 1

Related Questions