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

DG wrote:

> 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

The correct syntax is:
EXECUTE IMMEDIATE 'create table B (theCol number)';

That said everything about what you are doing is indicative of bad design and coming to Oracle from another database where such practices are required. In Oracle you should NEVER do this. The fact that you can does not mean should.

If you need a temporary table, and the chances of that are actually very close to zero, use a global temporary table. They are a form of permanent table.

But undoubtedly you don't and are just bringing a practice you learned in another database to Oracle when what you should be doing is learning Oracle architecture and concepts and, once again, NEVER building temporary tables.

If my suppositions are correct ... state the problem you are trying to solve ... rather than the syntax for your proposed solution.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)