Found the symbol "CREATE" instead of-CodePudding

CREATE OR REPLACE PROCEDURE P_DUP_DEL_CREATE_TAB IS
BEGIN                                           
  CREATE OR REPLACE TABLE T_DUPLICATE_TABLE (   
  F_NUMBER NUMBER(2),
  S_NUMBER NUMBER(1),
  CONSTRAINT ID PRIMARY KEY (F_NUMBER, S_NUMBER)
  );

END P_DUP_DEL_CREATE_TAB;

I don't know why it gives me this error:

Error(3,3): PLS-00103: Found the symbol "CREATE" instead of one of the following: ( begin case declare exit for goto if ...

I tried using the backslash, but it doesn't solve the problem, any help?

CodePudding user response：

You cannot use DDL statements in PL/SQL. You need to use EXECUTE IMMEDIATE and cannot use CREATE OR REPLACE:

CREATE OR REPLACE PROCEDURE P_DUP_DEL_CREATE_TAB
IS
  TABLE_DOES_NOT_EXIST EXCEPTION;
  PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_DUPLICATE_TABLE';
EXCEPTION
  WHEN TABLE_DOES_NOT_EXIST THEN
    EXECUTE IMMEDIATE 'CREATE TABLE T_DUPLICATE_TABLE (   
      F_NUMBER NUMBER(2),
      S_NUMBER NUMBER(1),
      CONSTRAINT ID PRIMARY KEY (F_NUMBER, S_NUMBER)
    )';
END P_DUP_DEL_CREATE_TAB;
/

db<>fiddle here