Re: Conditional Table Creation

From: Lando <MarkL_at_quebim.com>
Date: 1997/09/26
Message-ID: <342C546B.AA4B484@quebim.com>#1/1

Kind of, but you will have to use dynamic sql, for (a simple) example:

create or replace procedure Create_Tbl(Object_id in varchar2) as

   rc                      integer;
   c1                     integer;

   create_string varchar2(240);

   begin

       if (object_id='footable')
         then
            create_string:='create table footable (a int, b int)';
         elsif
         (object_id = 'footable_xyz')
             then
                 create_string:='create...';
         else
             do_something _else;
      end if;
      do_some_checks;
      If (everything_is_ok)
        then
           -- open the cursor, parse the create string, and create the
table
           c1:=dbms_sql.open_cursor;
           dbms_sql.parse(c1,create_string, dbms_sql.v7);
            rc:=dbms_sql.execute(c1);
           dbms_sql.close_cursor(c1);
       end if;

End create_tbl;

Also, make sure the owner of this procedure has create any table system priviliges.

mbarbieri_at_atrco.com wrote:

> In Transact SQL, I can conditionally create tables using the following
>
> script:
>
> if object_id('footable') is null
> create table footable ( a int, b int null )
> else
> alter table footable add b int null
> go
>
> Is there any way in Oracle to use conditional statements to perform
> different actions under different circumstances?
>
> Mark Barbieri
> mbarbieri_at_atrco.com
>
> -------------------==== Posted via Deja News
> ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Fri Sep 26 1997 - 00:00:00 CDT