Re: Create table in if-statement

In article <8kn029$2uh94$1_at_ID-6887.news.cis.dfn.de>,   "Kathinka Diehl" <kathinka_at_rrr.de> wrote:
>
> Kathinka Diehl <kathinka_at_rrr.de> wrote:
> >
> > [...]
> > begin
> > drop table [...]
> > execpion
> > when table_not_found do nothing
> > when others raise_application_error
> > create table [...]
> > [...]
>
> Sorry, I have looked in my manual, DDL and DCl is not possible inside
> PL/SQL. A PL/SQL-program could not drop oder create a table.
>
> I you need this, you have to use SQLPlus-scripts. If you need examples
 for
> scripts doing this, send me an email, I will send it to you. But just
> batches for NT.
>
> Regards,
> Kathinka
>
>

That is not correct. You CAN issue DDL from within a PL/SQL block, however you cannot do it directly. Use either the DBMS_SQL package and build dynamic SQL statements or build a command string and use 'execute immediate' (presuming you are using Oracle 8.x). For the latter create a varchar2 string of around 2000 characters (or whatever size you will need to hold the complete 'create table' statement) and then perform an 'execute immediate' on the string:

declare

   tabstr varchar2(2000);
begin

   tabstr := 'create table test (col1 varchar2(10), col2 number) tablespace tools storage (initial 5k next 5k pctincrease 0)';

   execute immediate tabstr;
end;
/

This is much easier than building the statements through DBMS_SQL.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.