PLS-00103: Encountered the symbol “CREATE”

PLS-00103: Encountered the symbol “CREATE” error occurs while running a CREATE block using script, you have not used / (front slash) to let SQLPlus know when the block completes, as a PL/SQL block may contain many instances of ; (semicolon). The CREATE block in the script should be terminated with a front slash (/) to inform Oracle that the code block has been completed.

The error occurs if you try to create two or more PL/SQL objects (procedure, function, package, type, etc.) at the same time. In a single sql session, the PL/SQL objects cannot be created together. You attempt to create several PL/SQL objects at the same time. The error PLS-00103: Encountered the symbol “CREATE” occurs in oracle

The PL/SQL objects such as procedure, functions, package, type etc are container objects that can be used in another objects. A procedure might be called from another procedure. A function can be used within a process. If you try to create two PL/SQL container objects, you’ll get an error PLS-00103: Encountered the symbol “CREATE”.

The two or more ddl, dml, dcl commands can execute in a single session. If two or more PL/SQL objects can not be created in a single command. The oracle sql commands are independent that can not be used in another sql command. The PL/SQL commands can be stored in local variable and can be used for the manipulation.

The Problem

The PL/SQL objects can be used in another PL/SQL objects. For example, the function can be called within the procedure. A procedure can be used within the function. If you try to create PL/SQL objects together, all the object are stored in buffer that can be rolled back. Oracle will not allow to create a PL/SQL object that refers to an another object in the buffer. Oracle forces the object to be persisted.

create type type_student;
create type type_course;

Error

Type TYPE_STUDENT compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
2/1       PLS-00103: Encountered the symbol "CREATE" 
Errors: check compiler log

Solution 1

When creating two or more PL/SQL objects, such as a procedure, function, package, or type, the PL/SQL objects should be terminated with a “/.” If Oracle detects a “/” terminator in the PL/SQL code, the objects in the oracle buffer are persisted in the database before going to the next code. Each PL/SQL object will be executed in a separate Oracle buffer, one after the other. PL/SQL objects are saved as soon as they are created.

The ; terminates a SQL query, whereas the / executes whatever is currently in the “buffer.” As a result, when you use a ; and a /, the statement is executed twice.

create type type_student;
/
create type type_course;

Output

Type TYPE_STUDENT compiled

Type TYPE_COURSE compiled

Solution 2

If you try to create a few PL/SQL objects in the PL/SQL code, the PL/SQL objects can be created separately. By executing each PL/SQL object individually, the objects may be created one at a time. Run each command in sequence, ensuring that the objects are created before executing the next PL/SQL code.

Code

create type type_student;

Output

Type TYPE_STUDENT compiled

Code

create type type_course;

Output

Type TYPE_COURSE compiled