PLS-00103 Encountered the symbol CREATE
NGr...@gmail.com
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.
sybrandb
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
NGr...@gmail.com
Shakespeare
<NGr...@gmail.com> schreef in bericht
news:bbadce27-941c-4957...@t65g2000hsf.googlegroups.com...
You can not execute a package body; or are you CREATING a package body in
your .PKB file?
Shakespeare
Tim X
> 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 ...."
>
More often than not, this error occurs when you have a syntax error that
is confusing PL/SQL. A common cause is a missing ; or , or ) etc.
> The logic is like this
>
the 'logic' is of no use to us. The code is what matters here.
> varable ...
means what?
..
>
> 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)
>
My money is on the above being the problem!
> 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;
>
>
The 'code' you provided is obviously not the real code - it is full of
incorrect syntax and would never work.
> Please help, What i am doing wrong?
>
You are doing lots of things wrong.
1. You have not told us anything about the database or pl/sql
version
2. You ahve not provided real code.
3. You have not indicated what you have done to try and find the
problem
4. The pseudo code you provided is too poor to tell us anything.
5. What did show_errors etll you?
consider that the error message is telling you that plsql came across
the symbol 'create' (noting that such a symbol doesn't even show up in
the pseudo code you posted) when it was expecting something else, such
as a begin. This tells you to work backwards from wherever the symbol
create is in your code. (does your @script perhaps have a
create or replace package body ....
or something similar. I'm guessing it does. If this is the case, you
need to go back to the manuals and start again - the whole concept your
trying to get to work is flawed and in too many ways to explain in a NG
message. You need to go back to basics as you appear to have some very
flawed conceptual model of how sql, pl/sql and sqlplus all fit
together.
I'm not trying to be offensive or discourage you, but your a long way
off the mark here and I suspect trying to run before you have learnt to
walk. Giving you the solution to this problem isn't going to help as
your going to encounter a lot more and much more difficult ones because
you appear not to have done your homework. I recommend reading the
relevant Oracle documentation, such as the PL/SQL reference manual and
the Developer's Guide. Don't skip the introductory chapters as these
have the basic foundation concepts you appear to be missing. The Oracle
Database Concepts Guide would also be a worthwhile read.
Tim
--
tcross (at) rapttech dot com dot au
joel garry
You might look at how Oracle does things like catalog.sql and the
scripts for installing statspack, for example. Not always the best or
brightest ways to do things, but at least it can get you going. Then
you might even come up with better ways to do things like check if
things are already patched, or whether everything worked as planned,
since you know your patch requirements.
You probably don't want to get too fancy with patching, it can take on
a life of its own. You want to make it simple and obvious for others
to run and fix. It may make sense to not allow normal operations when
patching, though I've seen it be desirable to minimize downtime in a
complicated upgrade.
jg
--
@home.com is bogus.
Why does google think I'm interested in sponsored links... in Japanese?