CREATE TABLE INSIDE PROCEDURE

Question

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 "EXTRACT_AUTOMATED_CHECKS", line 89 "

CREATE OR REPLACE PROCEDURE EXTRACT_AUTOMATED_CHECKS AS 


BEGIN

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

--CREATE TABLE, INDEX
--TABLE
   BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE extract_checks
                    (
                     Card number(19) NOT NULL PRIMARY KEY
                     ,Customer_Id number(19)
                    )';
    COMMIT;
    END;

--INDEX
    BEGIN
    EXECUTE IMMEDIATE('CREATE INDEX IDX_EXT_CHECKS extract_checks(Customer_Id)');
    COMMIT;
    END;

SOURCE.DBA_SEND_MAIL(
V_FROM=>'[email protected];',
V_RECIPIENT  => '[email protected]',
V_SUBJECT => 'Automated Checks Completed',
V_MESSAGE  =>  'Automated Checks Completed' );

EXCEPTION WHEN OTHERS THEN

SOURCE.DBA_SEND_MAIL(
V_FROM=>'[email protected];',
V_RECIPIENT  => '[email protected]',
V_SUBJECT => 'Automated  Checks Failed',
V_MESSAGE  =>  'Automated  Checks Failed' );

RAISE;

END EXTRACT_AUTOMATED_CHECKS;

I'd suggest you not to do that. In Oracle, we usually don't drop + create tables dynamically. Create it once, use it as many times as you want. — Littlefoot
Your design looks rather bad. I assume you are looking for a GLOBAL TEMPORARY TABLE. Create it once and reuse it. Also having 15 almost identical columns looks bad. Create another table "REWARDS" with a reference to table PX_extract_checks and columns VISIT, TRIGGER, WALLET and put in your data there. Use PIVOT clause if you need to show the data in columns rather than rows. — Wernfried Domscheit
"End" is in double-quotes. Maybe you meant to use two single quotes (''End'')? — William Robertson
@WilliamRobertson Hey thank you. It works now. I didn't know we can't use ". Your help is much appreciated. :) — user8740527
Indentation is totally irrelevant to the compiler. But be kind to the developer, yourself down the line or others, and develop a consistent indentation scheme and always use it - even when it doesn't seen necessary. Further, your procedure contains only DDL so commit is not necessary; Oracle automatically commits both before and after each DDL executed. However, you would do well to heed the comments by Littlefoot and Wernfried Domscheit. — Belayer

Popeye Popeye · Accepted Answer · 2019-08-13T03:22:28

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=>'[email protected];',
V_RECIPIENT  => '[email protected]',
V_SUBJECT => 'Automated PX Checks Failed',
V_MESSAGE  =>  'Automated PX Checks Failed' ); <<-- this ; is missing

Cheers!!

CREATE TABLE INSIDE PROCEDURE

1 Answers