Re: Help Renaming Tables!!!

From: <oratune_at_aol.com>
Date: Mon, 14 Aug 2000 21:20:16 GMT
Message-ID: <8n9nqb$j2l$1@nnrp1.deja.com>

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.

Received on Mon Aug 14 2000 - 16:20:16 CDT