在程序中创建表 [英] CREATE TABLE INSIDE PROCEDURE

问题描述

我似乎无法在此过程中创建表.我在网上阅读它，对于任何DDL，我都需要使用EXECUTE IMMEDIATE并尝试在线以下几个示例.但是，即使尝试了几种解决方案，它仍然会失败. '

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

错误" ORA-00904:结束":无效的标识符ORA-06512:在 "EXTRACT_AUTOMATED_CHECKS"，第89行"

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=>'[e-mail];',
V_RECIPIENT  => '[e-mail]',
V_SUBJECT => 'Automated Checks Completed',
V_MESSAGE  =>  'Automated Checks Completed' );

EXCEPTION WHEN OTHERS THEN

SOURCE.DBA_SEND_MAIL(
V_FROM=>'[e-mail];',
V_RECIPIENT  => '[e-mail]',
V_SUBJECT => 'Automated  Checks Failed',
V_MESSAGE  =>  'Automated  Checks Failed' );

RAISE;

END EXTRACT_AUTOMATED_CHECKS;

推荐答案

CREATE TABLE没有问题.

您在上次呼叫末尾错过了;以便在异常块中发送邮件.

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

SOURCE.DBA_SEND_MAIL(
V_FROM=>'[e-mail];',
V_RECIPIENT  => '[e-mail]',
V_SUBJECT => 'Automated PX Checks Failed',
V_MESSAGE  =>  'Automated PX Checks Failed' ); <<-- this ; is missing

干杯！

这篇关于在程序中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！