Append sysdate to a table name in Oracle

Question

I need to append SYSDATE while creating a table in Oracle. Please let me know if there is a direct way of doing it (Using only SQL) or I have to use PL/SQL for the same.

E.g. If table name is ABC, I need to create table as ABC_20130416.

Let me know if more info is needed.

Colin 't Hart · Accepted Answer · 2013-04-16T10:11:16.420

5

If SQL*Plus will always be available, you can do this using variables, something like (untested):

SQL>col yyyymmdd for a8 new_value v
SQL>select to_char(sysdate, 'YYYYMMDD') as yyyymmdd from dual;
SQL>create table ABC_&v (col1 integer);

On the other hand, if you want to able to do it anywhere you will need PL/SQL and dynamic SQL:

declare
  l_tablename varchar2(30) := 'ABC_' || to_char(sysdate, 'YYYYMMDD')
begin
  execute immediate 'create table ' || l_tablename || ' (col1 integer)';
end;
/

Or just create the table normally first and then rename:

create table xyz (
  ... many columns ...
);

declare
  l_tablename varchar2(30) := 'ABC_' || to_char(sysdate, 'YYYYMMDD')
begin
  execute immediate 'rename xyz to ' || l_tablename;
end;
/

edited Apr 16 '13 at 10:11

answered Apr 16 '13 at 07:28

Colin 't Hart

7,372
3
28
51

the solution looks good..but i already have a create table script and its too big..ie too big to fit inside varchar2 to be used like this..thats why i asked if its possible using SQL itself – Sid Apr 16 '13 at 08:58
Why not create the table first using SQL and then a short PL/SQL block to rename it? – Colin 't Hart Apr 16 '13 at 09:01
sorry...this works good..but do let me know if there's a way using only SQL – Sid Apr 16 '13 at 09:05
just saw ur second comment..the solution seems feasible...could u pls give me an example as to how to create a table and then rename it to append date.. – Sid Apr 16 '13 at 09:07
Well since it's an SQL script I assuming you're using SQL*Plus? Then you can use the first solution. – Colin 't Hart Apr 16 '13 at 09:07
In the interest of completeness http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9019.htm but really Googling "Oracle rename" is very easy. – Colin 't Hart Apr 16 '13 at 09:09
rename wont work for me as i still cant append sysdate to the table name..as i said, this would be an automation and i dont want to enter the date manually each time...thanks for the suggestion though.. – Sid Apr 16 '13 at 09:21
Why won't rename work for you? I've updated my answer to show how it can be done. – Colin 't Hart Apr 16 '13 at 09:47

score 0 · Answer 2 · answered Apr 16 '13 at 07:32

0

With PL/SQL only:

begin
  execute immediate 'create table foo_' || your_timestamp_here ;
end;

answered Apr 16 '13 at 07:32

Michael-O

18,123
6
55
121

2

That's not a good solution. The conversion from the `DATE` (or `TIMESTAMP`) in your solution is done using *implicit data type conversion* which means it is subject to NLS setting and can (and most probably will) wind up with an illegal name for a table. Even if it worked it will most probably not be the name Sid is expecting. You should always use to_char() with a format model to convert a date/timestamp to a string. – Apr 16 '13 at 07:39
@a_horse_with_no_name, I never implied any implicit conversion. I just wanted to show that it must be done with PL/SQL. There is no way with pure SQL. – Michael-O Apr 16 '13 at 10:05
The expression `'create table foo_' || your_timestamp_here` ***is*** an implicit type conversion. – Apr 16 '13 at 11:54
@a_horse_with_no_name, not if `your_timestamp_here` is already a `VARCHAR2`. – Michael-O Apr 16 '13 at 12:18

score 0 · Answer 3 · answered Apr 16 '13 at 07:41

0

PL/SQL CODE:

declare
  tabname varchar(32);
begin
  tabname := 'abc'|| TO_CHAR(SYSDATE, 'YYYYMMDD');
  execute immediate 'create table '|| tabname ||' ( f1 number, f2 number, f3 number)';
end;

answered Apr 16 '13 at 07:41

M.J.Ahmadi

3,931
4
17
24

Append sysdate to a table name in Oracle

3 Answers3