CREATE TABLE INSIDE PROCEDURE

I can't seem to create the table inside this procedure. I read it online that for any DDL, I need to use EXECUTE IMMEDIATE and tried following few examples online. However, even after trying several solutions it keeps failing. '

Error ""ORA-00904: "End": invalid identifier ORA-06512: at "RPT_MART.PX_EXTRACT_AUTOMATED_CHECKS", line 89 "

CREATE OR REPLACE PROCEDURE PX_EXTRACT_AUTOMATED_CHECKS AS 


BEGIN

--DROP TABLE
   BEGIN 
   EXECUTE IMMEDIATE ('DROP TABLE PX_extract_checks') ;    
   EXCEPTION 
   WHEN OTHERS THEN NULL;
   END;

--CREATE TABLE, INDEX
--TABLE
   BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE PX_extract_checks
                    (
                     Card_Number number(19) NOT NULL PRIMARY KEY
                    ,Tier VARCHAR2(50) DEFAULT("MyPanera 2.0 Loyalty")
                    ,Reset_Counters VARCHAR2(10) DEFAULT("Yes")
                    ,Visit_Credit_Resets number
                    ,Reward1_Visit number
                    ,Reward1_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward1_Wallet VARCHAR2(50)
                    ,Reward2_Visit number
                    ,Reward2_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward2_Wallet VARCHAR2(50)
                    ,Reward3_Visit number
                    ,Reward3_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward3_Wallet VARCHAR2(50)
                    ,Reward4_Visit number
                    ,Reward4_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward4_Wallet VARCHAR2(50)
                    ,Reward5_Visit number
                    ,Reward5_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward5_Wallet VARCHAR2(50)
                    ,Reward6_Visit number
                    ,Reward6_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward6_Wallet VARCHAR2(50)
                    ,Reward7_Visit number
                    ,Reward7_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward7_Wallet VARCHAR2(50)
                    ,Reward8_Visit number
                    ,Reward8_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward8_Wallet VARCHAR2(50)
                    ,Reward9_Visit number
                    ,Reward9_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward9_Wallet VARCHAR2(50)
                    ,Reward10_Visit number
                    ,Reward10_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward10_Wallet VARCHAR2(50)
                    ,Reward11_Visit number
                    ,Reward11_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward11_Wallet VARCHAR2(50)
                    ,Reward12_Visit number
                    ,Reward12_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward12_Wallet VARCHAR2(50)
                    ,Reward13_Visit number
                    ,Reward13_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward13_Wallet VARCHAR2(50)
                    ,Reward14_Visit number
                    ,Reward14_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward14_Wallet VARCHAR2(50)
                    ,Reward15_Visit number
                    ,Reward15_Trigger VARCHAR2(10) DEFAULT("End")
                    ,Reward15_Wallet VARCHAR2(50)
                    ,Customer_Id number(19)
                    )';
    COMMIT;
    END;

--INDEX
    BEGIN
    EXECUTE IMMEDIATE('CREATE INDEX IDX_PAY_EXT_CHECKS ON **strong text**RPT_MART.PX_extract_checks(Customer_Id)');
    COMMIT;
    END;

SOURCE.DBA_SEND_MAIL(
V_FROM=>'Notification@Company.com;',
V_RECIPIENT  => 'employee@company.com',
V_SUBJECT => 'Automated PX Checks Completed',
V_MESSAGE  =>  'Automated PX Checks Completed' );

EXCEPTION WHEN OTHERS THEN

SOURCE.DBA_SEND_MAIL(
V_FROM=>'Notification@Company.com;',
V_RECIPIENT  => 'employee@company.com',
V_SUBJECT => 'Automated PX Checks Failed',
V_MESSAGE  =>  'Automated PX Checks Failed' )

RAISE;

END PX_EXTRACT_AUTOMATED_CHECKS;

1 answer

  • answered 2019-08-13 03:22 Tejash

    There is no issue with CREATE TABLE.

    you missed ; at the end of last call to send mail in exception block.

    SOURCE.DBA_SEND_MAIL(
    V_FROM=>'Notification@Company.com;',
    V_RECIPIENT  => 'employee@company.com',
    V_SUBJECT => 'Automated PX Checks Failed',
    V_MESSAGE  =>  'Automated PX Checks Failed' ); <<-- this ; is missing
    

    Cheers!!