Re: whenever sqlerror is an unknown statement?

On 20 Jan 2006 11:28:38 -0800, jjsavage_at_gmail.com wrote:

>My overall goal is to create a table iff it doesn't exist. After more
>poking around the docs, I've got this:
>
>begin
> select * from all_tables where table_name = 'foo';
> exception
> when no_data_found then
> begin
> create table foo(bar char(5));
> end;
>end;
>
>And SQL Workshop says:
> m
>ORA-06550: line 6, column 4: 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>
><< close current delete fetch lock insert open rollback savepoint set
>sql execute commit forall merge pipe
>
>Bah! Why can't I have a create, but I can have a select or update or
>anything else?
>
> - John

create is DDL. DDL is evidently not a normal operation in SQL, ie you don't create tables on the fly.
So you need to submit them using EXECUTE IMMEDIATE. All of which is liberally documented in the PL/SQL reference manual on http://tahiti.oracle.com It is also wise to look up error messages on http://tahiti.oracle.com prior to asking questions here. So far all your questions demonstrate you are just hacking away, come from a SQL server background, and think Oracle is SQL server sold by a different vendor.
You are doomed to fail when you stick to that assumption. BTW: In Oracle one doesn't check the dictionary for existence or non-existence of tables. One traps exceptions.

--
Sybrand Bakker, Senior Oracle DBA