PLS-00103: Encountered the symbol "CREATE" when expecting one of the-CodePudding

I am trying to create a stored procedure in oracle 12c database and I am getting error when I am running code to store the procedure.

PLS-00103: Encountered the symbol "CREATE" when expecting one of the

There are multiple stack overflow question already asked on this topic. but they suggest some different syntax. which is deviation from actual oracle documentation. and even those didn't worked for me

I checked for documentation on multiple website including oracle documentation. oracle documetation suggest syntax as following Oracle STORED PROCEDURE DOCUMENTATION so I as per the syntax I wrote the following procedure.

CREATE PROCEDURE  PDD_PROC_BASE
AS

 --DROP TABLE BASE;

CREATE TABLE  BASE as 
    SELECT idno
        ,DATE
        ,diff
        ,SUBSTR(idNO,7,2) AS PRD
        ,COMPLETED
        ,CATG
        ,OP_Number

    FROM table1
    WHERE = date >= '30-JUN-2018' 
        AND STATUS = 'G' 

 END;

and I got the following error.

Procedure PDD_PROC_BASE compiled

Errors: check compiler log
Errors for PROCEDURE AN_5043152.PDD_PROC_BASE:

 LINE/COL ERROR
 -------- ------------------------------------------------------------------------------
 7/5      PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
     
     ( begin case declare exit for goto if loop mod null pragma
     raise return select update while with <an identifier>
     <a double-quoted delimited-identifier> <a bind variable> <<
     continue close current delete fetch lock insert open rollback
     savepoint set sql execute commit forall merge pipe purge
     json_exists json_value json_query json_object json_array

I checked other resources as well but still didn't understood what went wrong.

I even tried code example from Oracle documentation and got similar error.

I am using SQLdeveloper tool as client

CodePudding user response：

You cant use directly sql ddl statements in plsql block, you can do the same thing using dynamic sql with the "EXECUTE IMMEDIATE" statement like this:

begin
execute immediate 'create table test_table1 (test_column1 varchar2(40))';--your create table statement here
end;

CodePudding user response：

In Oracle in stored procedures you can use DDL statements only as dynamic SQL, that means as EXECUTE IMMEDIATE 'CREATE TABLE ' ...

Check here, for example: http://www.dba-oracle.com/t_using_ddl_create_index_table_plsql.htm