Re: HELP: Why doesn't this query work?

From: Dave <davidr21_at_hotmail.com>
Date: 12 Feb 2004 09:47:32 -0800
Message-ID: <8244b794.0402120947.6653f9cc@posting.google.com>

necron1999_at_hotmail.com (DG) wrote in message news:<eb556de4.0402120340.72117c5_at_posting.google.com>...
> Hi, yes, I'm an Oracle newbie:
>
> When I run this query:
>
> declare
> thecount number ;
>
> begin
>
> select count(*) into thecount from user_tables where table_name = 'B';
> if thecount = 0 then
> create table B (theCol number);
> end if;
> end;
>
> I get this error:
>
> PLS-00103: Støtte på symbolet CREATE der ett av følgende var ventet:
> ....blah blah...
>
> why is this? When I change the statement in the "IF" block
> to..say..an Insert statement it works ok...
>
> HELP!
>
> Thank you

Hi -

Your CREATE TABLE statement is a DDL statement not DML. DDL cannot be used directly in PL/SQL. You can however use the command EXECUTE IMMEDIATE 'CREATE TABLE .....'; Two things to note...
1.) Your EXECUTE IMMEDIATE statement is not evaluated until run-time, so no compile time checks occur.
2.) All DDL statements are implicitly wrapped in commits. I believe what really happens when you issue a DDL statement is this...

       COMMIT;
         DDL STATEMENT;
       COMMIT;
     So you must realize your previous work gets committed regardless

of whether or not the DDL Statement succeeds. Rollback will not remove the prior work. In your example, this does not seem to matter, but it is important to understand.

Dave Received on Thu Feb 12 2004 - 11:47:32 CST