| Concatenate Date to the Table Name [message #143514] |
Thu, 20 October 2005 13:17  |
stak
Messages: 20
Registered: October 2005
|
Junior Member |
|
|
Hi,
I am beginner in Oracle. Please bear with me if my question is too simple.
I want to concatenate Date(MMDDYY) to my table name(TABLE1) and I couldn't find exactly how to do this.
I thought of giving a try in this way(I'm not sure if this is the right way). I'm creating a new variable called "new_dt" with my desired format and use it with "CONCAT" function in my create table statement like mentioned below:
new_dt=to_char(sysdate,'MMDDYYYY');
CREATE TABLE CONCAT('TABLE1','NEW_DT) AS SELECT * FROM OLD_TABLE;
Can some one help me if this works or have a better and easy way to do this.
Quick reply is really appreciated..
Thanks in adv,
ST
|
|
|
|
| Re: Concatenate Date to the Table Name [message #143521 is a reply to message #143514] |
Thu, 20 October 2005 13:30  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
You would need to use dynamic sql to accomplish this, search for execute immediate. Or use a sql statement to generate a set of sql statements and spool them to a file for execution.
BUT, you probably don't really want to do what you think you want to do. Before you do, ask yourself why you are doing it, and if it is really the best way to accomplish whatever your goal is. In general in oracle you don't need to go around creating a bunch of tables dynamically and then drop them. You have some tables and you do some operations on them.
|
|
|
|
| Re: Concatenate Date to the Table Name [message #143527 is a reply to message #143514] |
Thu, 20 October 2005 14:05 |
stak
Messages: 20
Registered: October 2005
|
Junior Member |
|
|
Thanks Smartin for your quick suggestion..
But I think its not that complicated. All I need is to append date to my table in the create statement.
Ex: If my table is called TEST and I'm running the script today(10/20/05)then I need something like this:
CREATE TABLE TEST102005 AS SELECT * FROM OLD_TABLE;
But I do not want to manually hard code this date in my create statement. Is there any workaround to get it done using SYSDATE or whatever..
Hope this is bit clear..
Thanks again
ST
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
