Re: create table in a procedure
Date: Fri, 9 Apr 2004 07:28:09 -0400
Message-ID: <hYOdnU4-58tOFevdRVn-jw_at_comcast.com>
"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 - 13:28:09 CEST