Re: PLS-00103 Encountered the symbol CREATE

On 22 okt, 11:26, NGry..._at_gmail.com wrote:
> Please help,
> I amd trying to create a script for patching the database.
>
> the script works with INSERT or SELECT, but when i try to execute a
> package (*pkb). I get an error: "PLS-00103 Encountered the symbol
> CREATE when expecting on of the following: begin ...."
>
> The logic is like this
>
> varable ...
> ..
>
> declare ...
>
> begin
>
> Select c INTO a FROM test_table
>
> if :a=:b then
>
> @PG_TEST.pkb; (if in place of pkb i have simple script with INSERT or
> SELECT there are no problems)
>
> insert into test_table values((select max(id) from test_table) + 1,
> 'success');
>
> else
> ...
>
> end if;
> commit;
>
> Exception when others then
> rollback work;
> end;
> .
> /
> exit;
>
> Please help, What i am doing wrong?
>
> Thank you in advance.

You are in a PL/SQL anonymous block.
Any CREATE, or DROP, or GRANT statement is considered DDL (Data Definition Language).
PL/SQL does NOT allow DDL directly. This is why you are getting the error: PL/SQL only recognizes SELECT, INSERT, UPDATE, DELETE It does allow DDL using execute immediate. However, you should notice two things
1 DDL preforms an implicit commit, which may disturb your code 2 Using DDL in a stored procedures or anonymous blocks is considered 'Creating objects on the fly'. In a properly maintained environment this is a capital sin, which will result in DBAs all over the globe attempting to lynch you.

You may want to reconsider your strategy. It results in code with side effects, which is out of control.

--
Sybrand Bakker
Senior Oracle DBA