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

"Create" is not a PL/SQL command. Create is DDL, in PL/SQL you can only use DML. But fear not, there is a way to do it:

IF thecount = 0 then
  execute immediate 'create table B (theCol number)'; end if;

execute immeidate is the mechanism used to execute DDL statements in PL/SQL. It can also be used to execute DML commands where table names and/or column names are not known until run time.

Ken Denny Received on Thu Feb 12 2004 - 12:08:43 CST