SQL Table Name

[vr]

I have an SQL statement in which I would like to somehow place a variable for the table name, but don't quite know how.

SELECT * FROM MyTable_0205

The table name changes each month and I would rather not have to update the query each month (02=YY; 05=MM).

Can anyone give me some direction on how to make the table name a variable?

Thanks!

 

[Bimalaggarwal]

in SQL*Plus
----------

select * from &table_name;

(When prompted for table_name, enter the correct table_name)



in Pl_SQL, its also easy. Use dynamic sql;
---------------------------------------------

declare
l_table_name varcchar2....
l_temp_table l_table_name%rowtype;
begin
l_table_name := .....assignment according to your required logic

execute immediate 'select * from '||l_table_name into l_temp_table....;
end;
/

(Please check the correctness of the syntax)

 

[THoey]

The first question that comes to my mind is WHY? Why not have one table that has a date field that is used to separate the data? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[vr]

These are month end tables utilized for aging/archive purposes.

IE: (ACCOUNT0205, ACCOUNT0206, ACCOUNT0207)

The naming schema is out of my control, I just have to deal with it.

 

[andypara]

Create the script with an ambersand in from of table_name.

i.e.

select * from &table_name

Just like the guy above said.

Save the script as a .sql file in the start up path folder of sql*plus. You can find this by right clicking the icon and selecting properties.

Then just call script from sql*plus like this

@your_file_name

Then when prompted for the table_name just put in the name of the table.

 

[vr]

Thanks everyone for you help.

Bimalaggarwal, this works super!!

 

[THoey]

VR, just to let you know, I understand your reasoning now (the part about it being out of your hands). Maybe you can document the efforts you are having / will be having in the future and pass that on to those that are making you jump through hoops... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...