thanks for all the help!
Tommy
<oratune_at_aol.com> wrote in message news:8n9p0b$k05$1_at_nnrp1.deja.com...
> In article <8n9oec$jhs$1_at_nnrp1.deja.com>,
> oratune_at_aol.com wrote:
> > 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.
> >
>
> 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.
Received on Mon Aug 14 2000 - 17:04:34 CDT
