Help Renaming Tables!!!

Help Renaming Tables!!!

Post by Tomm » Sun, 31 Dec 1899 09:00:00



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

 
 
 

Help Renaming Tables!!!

Post by orat.. » Sun, 31 Dec 1899 09:00:00




Quote:> 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.

 
 
 

Help Renaming Tables!!!

Post by orat.. » Sun, 31 Dec 1899 09:00:00






> > 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.

 
 
 

Help Renaming Tables!!!

Post by orat.. » Sun, 31 Dec 1899 09:00:00








> > > 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.

One final correction, in the original logic of the select statements:

declare
       old_tname      varchar2(20);
       new_tname      varchar2(20);
       source_cursor  integer;
       ret_val        integer;
 begin
       select 'OCC'|| (to_char(sysdate - 15,'YYYYMMDD')) || '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;
 /

The subtraction should be with the sysdate call, not after -- the code
listed above will give 20000730 for a date, whereas the original code
will give 20000799 -- not a valid date!

Sorry that wasn't caught earlier.
--
David Fitzjarrell
Oracle Certified DBA

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

 
 
 

Help Renaming Tables!!!

Post by Tomm » Sun, 31 Dec 1899 09:00:00


thanks for all the help!
Tommy







> > > > 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.

> One final correction, in the original logic of the select statements:

> declare
>        old_tname      varchar2(20);
>        new_tname      varchar2(20);
>        source_cursor  integer;
>        ret_val        integer;
>  begin
>        select 'OCC'|| (to_char(sysdate - 15,'YYYYMMDD')) || '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;
>  /

> The subtraction should be with the sysdate call, not after -- the code
> listed above will give 20000730 for a date, whereas the original code
> will give 20000799 -- not a valid date!

> Sorry that wasn't caught earlier.
> --
> David Fitzjarrell
> Oracle Certified DBA

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

 
 
 

Help Renaming Tables!!!

Post by Jonathan Gennic » Sun, 31 Dec 1899 09:00:00


For one thing, you cannot embed a SELECT within an ALTER
TABLE statement. How are you trying to implement this
process? Are you writing PL/SQL code on the server, or are
you executing these statements from a client? If you're
writing PL/SQL on the server, you'll need to use dynamic SQL
(the DBMS_SQL package) to execute your ALTER TABLE
statement.

Jonathan

_____________________________________________________

http://gennick.com
Brighten the Corner Where You Are

On Mon, 14 Aug 2000 15:32:14 -0500, "Tommy"


>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?

 
 
 

1. Need help renaming table columns

I have changed the name of a column in a jet database in a newer
version of my software.  I would like to change the column name
in older versions of the database when the new software is run.
Is there a way to do this using jet?  I'm looking for something like the
"ALTER TABLE ... RENAME COLUMN" available in foxpro or "sp_rename" in
SQL Server.

2. Can't replicate because systemaccount needs internet access

3. Error renaming table file to a temp table table caused ingres to crash

4. How to create a word index?

5. Help with system table: sysservers on renamed SQL 7 Host

6. On-line database services

7. Copying and renaming paradox v5.0 Tables - help !!!

8. field level audit trail

9. rename table with vb

10. Renaming table in SQL 7.0

11. rename table in vb code

12. Renaming a table

13. Renaming Tables