Re: create table in a procedure

"DottingTheNet" <dottingthenet_at_hotmail.com> wrote in message news:11bf8d5a.0404082019.6fbd8738_at_posting.google.com...
| help plz!!
| when i try to cretae a table in a procedure like
|
| create procedure blah
| is
| begin
| create table.....
| .....
| end;
|
| i get error PLS-00103 stating that it encountered CREATE when
| expecting everything else..
|
| thnx!!

pl/sql directly supports DML only

to execute DDL (creates, etc) you need to dynamic SQL (execute immediate or DBMS_SQL). you will also want to look into Autonomous Transactions.

but, as indicated by other posters, DDL in a production application is rare in Oracle -- objects typically should all exist ahead of time, even 'temporary' tables (Oracle's concept is that the table is permanent, but the data is temporary -- set CREATE ... GLOBAL TEMPORARY TABLE)

;-{ mcs Received on Fri Apr 09 2004 - 06:28:09 CDT