Re: Help Renaming Tables!!!

From: <oratune_at_aol.com>
Date: Mon, 14 Aug 2000 21:30:52 GMT
Message-ID: <8n9oec$jhs$1@nnrp1.deja.com>

In article <8n9nqb$j2l$1_at_nnrp1.deja.com>,   oratune_at_aol.com wrote:
> In article <spglbsbekn917_at_corp.supernews.com>,
> "Tommy" <motykat_at_thriftycall.com> wrote:
> > I need to create a process that renames a table that is 15 days old
 to
> > todays date.
> > This is what I have tried and can not make it work:
> >
> > Alter Table
> > (select 'OCC'|| (to_char(sysdate,'YYYYMMDD') - 15) || 'DAILY' into t
 from
> > dual)
> > Rename To
> > (select 'OCC'|| (to_char(sysdate,'YYYYMMDD')) || 'DAILY' into t from
 dual);
> >
> > Can anyone tell me how to make this work?
> >
> > Thanks
> > Tommy
> >
> >
>
> Using PL/SQL and 8i this is a fairly straightforward task:
>
> declare
> old_tname varchar2(20);
> new_tname varchar2(20);
> sql_stmt varchar2(255);
> begin
> select 'OCC'|| (to_char(sysdate,'YYYYMMDD') - 15) || 'DAILY'
> into old_tname
> from dual;
> select 'OCC'|| (to_char(sysdate,'YYYYMMDD')) || 'DAILY'
> into new_tname
> from dual;
> sql_stmt := 'alter table '||old_tname||' rename to '||new_tname;
> execute immediate sql_stmt;
> end;
> /
>
> Without 8i you'll need to use the DBMS_SQL dynamic SQL package:
>
> declare
> old_tname varchar2(20);
> new_tname varchar2(20);
> source_cursor integer;
> ret_val integer;
> begin
> select 'OCC'|| (to_char(sysdate,'YYYYMMDD') - 15) || 'DAILY'
> into old_tname
> from dual;
> select 'OCC'|| (to_char(sysdate,'YYYYMMDD')) || 'DAILY'
> into new_tname
> from dual;
> source_cursor := dbms_sql.open_cursor;
> dbms_sql.parse(source_cursor, 'alter table '||old_tname||'
 rename
> to '|| new_tname);
> ret_val := dbms_sql.execute(source_cursor);
> dbms_sql.close_cursor(source_cursor);
> exception
> when others then
> if dbms_sql.is_open(source_cursor) then
> dbms_sql.close_cursor(source_cursor);
> end if;
> raise;
> end;
> /
>
> I hope this helps.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Oops! Forgot this:

declare

       old_tname      varchar2(20);
       new_tname      varchar2(20);
       source_cursor  integer;
       ret_val        integer;
 begin
       select 'OCC'|| (to_char(sysdate,'YYYYMMDD') - 15) || 'DAILY'
  into old_tname
       from dual;
       select 'OCC'|| (to_char(sysdate,'YYYYMMDD')) || 'DAILY'
       into new_tname
       from dual;
       source_cursor := dbms_sql.open_cursor;
       dbms_sql.parse(source_cursor, 'alter table '||old_tname||' rename
to '|| new_tname, DBMS_SQL.NATIVE);
       ret_val := dbms_sql.execute(source_cursor);
       dbms_sql.close_cursor(source_cursor);
 exception
       when others then
         if dbms_sql.is_open(source_cursor) then
           dbms_sql.close_cursor(source_cursor);
         end if;
       raise;

 end;
 /

--
David Fitzjarrell
Oracle Certified DBA


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

Received on Mon Aug 14 2000 - 16:30:52 CDT