Home » SQL & PL/SQL » SQL & PL/SQL » Create dynamic table_Name
Create dynamic table_Name [message #277141] Mon, 29 October 2007 01:57 Go to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Hi
Oracle 10

I want to create table that take current month & year in table name i.e. My_Tab_Oct_07

Like
CREATE TABLE (SELECT 'My_Tab_'|| To_Char(SYSDATE,'Mon_yyyy') FROM dual)
AS SELECT * FROM My_Old_Tab

Please advise how to include month & year in table name.

Wishes
J a w a d
Re: Create dynamic table_Name [message #277149 is a reply to message #277141] Mon, 29 October 2007 02:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This is considered VERY bad practice in Oracle.

It cannot be done with native SQL. To do it, you will need to use dynamic SQL. In PL/SQL, this would involve the EXECUTE IMMEDIATE statement; it differs from language to language.

Ross Leishman
Re: Create dynamic table_Name [message #277151 is a reply to message #277141] Mon, 29 October 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't do that, investigate on partitioned table if you need such thing.

Regards
Michel
Re: Create dynamic table_Name [message #277303 is a reply to message #277141] Mon, 29 October 2007 08:32 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Even if you do not use partitioning, what you are doing makes no sense. Simply have an indexed date column in your table and you can pull out any months data with ease.
Previous Topic: ORA-00904: invalid identifier, when calling a function inside the own schema
Next Topic: Date Difference
Goto Forum:
  


Current Time: Mon Dec 05 15:05:44 CST 2016

Total time taken to generate the page: 0.11390 seconds