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

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

Pl/sql does not natively support DDL statements only DML: select, insert, update, and delete. In order to execute DDL from pl/sql code you must use dynamic SQL: execute immediate or dbms_sql package.

This information is in the manual, but it is not worded as clearly as it should be. From the version 9.2 Pl/sql manual section, Support for SQL: PL/SQL also supports dynamic SQL, an advanced programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements "on the fly" at run time.

The prior paragraph tells you that you can use DML and this one mentions DDL via dynamic execution.

HTH -- Mark D Powell -- Received on Thu Feb 12 2004 - 11:22:14 CST