generating a date in a table name

[dklloyd]

I want to create sql that will create a table in the format table_name_mmddhh so that it has the current month, day & hour on the end of it.

Normally I would do this by generating the sql in a select statement and spooling it out to a file then @ing in the file to run the command.

Can I do this dynamically so that it can be run as a procedure without having to rely on an intermediate file

Sorry but my sql basics are basic, so I would need the full script.

Hope you can help

Thanks in advance

dklloyd

 

[Guest_imported]

Hi,

You will have to use some dynamic PL/SQL-Code:

Procedure to be created:

CREATE OR REPLACE
PROCEDURE dated_table_creator
( i_table_name_prefix IN VARCHAR2
) IS
l_string VARCHAR2( 4000 );
BEGIN
l_string :=
'CREATE TABLE ' ||
i_table_name_prefix || '_' ||
TO_CHAR( SYSDATE , 'MMDDHH' ) ||
'...and so on...(no trailing ...';
EXECUTE IMMEDIATE l_string;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.enable(1000000);
dbms_output.put_line
( 'Procedure dated_table_creator ' ||
'failed because of : ' ||
sqlerrm
);
END dated_table_creator;
/


Running the procedure:

execute dated_table_creator( 'mytable' )

 

[Guest_imported]

Easiest way to do this is to use the 'EXECUTE IMMEDIATE' construct in PL/SQL

A simple example follows :-

begin
execute immediate 'create table table_name_'||to_char(sysdate, 'mmddhh')
||'( id number, text varchar2(50))';
end;

HTH

 

[dklloyd]

Thanks for the procedures, they work great.
Useful lesson in procedures for me too!
Thanks again
dklloyd