Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

Table Naming Conversion

Hi All,

We need to backup one oracle table every week before it is dropped. I wrote one scripts as:

CREATE TABLE a1backup
AS SELECT * FROM al;

I need to run this as cron every week. My question is how to specify the name of backup table(table name + date)? Like albackup03212003, a1backup31042003, ….

Thanks
Terry
0
terrywong
Asked:
terrywong
1 Solution
 
ypwitkowCommented:
You must use dynamic SQL.
First create procedure:

CREATE OR REPLACE PROCEDURE cr_tab AS
sql_text  VARCHAR2(100);
BEGIN
  sql_text := 'CREATE TABLE a1backup'||
              to_char(sysdate,'mmddyyyy')||
              ' AS SELECT * FROM a1';
  execute immediate sql_text;
END;
/

Then call this procedure in your script.
HTH,
Lucy
0
 
walterecookCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: ypwitkow {http:#8216223}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now