PLS- 00103 encountered the symbol "/" and "Create"

Question

I have problem with that PLS-00103 because i completly dont know what to do with that... It looks so simply, and still making problems...

i had problem in first time with PLS-00103 "CREATE" word, and when i put that "/" in third line i got problem with PLS-00103 "/". What am i doing wrong?

create or replace package Sappe as
       Procedure initialize;
end;
/

create or replace package body Sappe as

  Procedure initialize is
  begin
    Drop table AAA;

    CREATE TABLE AAA(id int not null,
                     x1 varchar(1) default ' ',
                     x2 varchar(1) default ' ',
                     x3 varchar(1) default ' ',
                     x4 varchar(1) default ' ',
                     x5 varchar(1) default ' ',
                     x6 varchar(1) default ' ',
                     x7 varchar(1) default ' ');

  end initialize;
end sappe;
/

Answer 1

This is too much code to put into a comment so I'm writing it here.

First, checking whether a table exists or not:

SQL> DESC aaa;
ERROR:
ORA-04043: object aaa does not exist

Now, creating a package and its body - I've included control which will prevent error if table doesn't exist:

SQL> CREATE OR REPLACE PACKAGE Sappe
  2  AS
  3     PROCEDURE initialize;
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Sappe
  2  AS
  3     PROCEDURE initialize
  4     IS
  5        l_cnt   NUMBER;
  6     BEGIN
  7        SELECT COUNT (*)
  8          INTO l_cnt
  9          FROM user_tables
 10         WHERE table_name = 'AAA';
 11
 12        IF l_cnt > 0
 13        THEN
 14           EXECUTE IMMEDIATE 'Drop table AAA';
 15        END IF;
 16
 17        EXECUTE IMMEDIATE q'[CREATE TABLE AAA(id int not null,
 18                       x1 varchar(1) default ' ',
 19                       x2 varchar(1) default ' ',
 20                       x3 varchar(1) default ' ',
 21                       x4 varchar(1) default ' ',
 22                       x5 varchar(1) default ' ',
 23                       x6 varchar(1) default ' ',
 24                       x7 varchar(1) default ' ')]';
 25     END initialize;
 26  END sappe;
 27  /

Package body created.

Testing:

SQL> BEGIN
  2     sappe.initialize;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> DESC aaa;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 X1                                                 VARCHAR2(1)
 X2                                                 VARCHAR2(1)
 X3                                                 VARCHAR2(1)
 X4                                                 VARCHAR2(1)
 X5                                                 VARCHAR2(1)
 X6                                                 VARCHAR2(1)
 X7                                                 VARCHAR2(1)

SQL>

As you can see, it works. Table now exists, so - let's run the procedure once again - shouldn't fail:

SQL> BEGIN
  2     sappe.initialize;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

Seems to be OK.

Answer 2

We can't do native DDL in PL/SQL. You will have to use dynamic SQL:

create or replace package body Sappe as

  Procedure initialize is
  begin
    execute immediate 'Drop table AAA';

    execute immediate 
          q'[CREATE TABLE AAA(id int not null,
                     x1 varchar(1) default ' ',
                     x2 varchar(1) default ' ',
                     x3 varchar(1) default ' ',
                     x4 varchar(1) default ' ',
                     x5 varchar(1) default ' ',
                     x6 varchar(1) default ' ',
                     x7 varchar(1) default ' ')]';

     end initialize;
end sappe;
/

Your procedure will fail at the first step if you don't already a table AAA. There are various ways of solving this (such as checking USER_TABLES) but the basic premise of stored procedures like this is basically flawed. Outside of a few highly-specialized use cases there is no need to drop and create tables in PL/SQL. It is a common anti-pattern amongst people who are more familiar with other RDBMS products than with Oracle.