Thanks for the question, Peter.
Asked: September 08, 2016 - 3:34 pm UTC
Last updated: September 08, 2016 - 4:01 pm UTC
Version: 11g
Viewed 1000+ times
You Asked
I have a script that calls a table from our database but that table is replaced by newer tables quarterly. The format for the tables is like TABLENAME_YYYYMMDD. I would like to call the most current table in the script so that I don't have to update the script every time I get a new table.
-- THE DATABASE CONTAINS SEVERAL TABLES WITH SIMILAR NAMES.
CREATE TABLE ESL_20160623 (ID INT, ADDRESS_NUMBER INT, STREET_NAME NVARCHAR2(80));
-- WITH THIS DATA IN IT:
INSERT INTO ESL_20160623 VALUES (1, 1, 'NEW MAIN STREET');
INSERT INTO ESL_20160623 VALUES (1, 2, 'NEW MAIN STREET');
CREATE TABLE ESL_20160106 (ID INT, ADDRESS_NUMBER INT, STREET_NAME NVARCHAR2(80));
-- WITH THIS DATA IN IT:
INSERT INTO ESL_20160106 VALUES (1, 11, 'OLD MAIN STREET');
INSERT INTO ESL_20160106 VALUES (1, 12, 'OLD MAIN STREET');
-- I'D LIKE TO BE ABLE TO CALL THE NEW TABLE INTO MY QUERY BASED ON THE DATE PORTION OF THE NAME.
-- BELOW IS WHAT I TRIED:
SELECT *
FROM
(
SELECT TABLE_NAME
FROM DBA_TABLES
WHERE REGEXP_LIKE(TABLE_NAME, 'ESL_\d+{8}$')
AND SUBSTR(TABLE_NAME, 5, 8) =
(
SELECT MAX(SUBSTR(TABLE_NAME, 5, 8))
FROM DBA_TABLES
WHERE REGEXP_LIKE(TABLE_NAME, 'ESL_\d+{8}$')
)
)
;
Thank you
and Chris said...
You can't pass queries as table names. To do this you'll need dynamic SQL.
Using your tables it would look something like this:
declare
sql_stmt varchar2(1000);
table_name user_tables.table_name%type;
rw esl_20160623%rowtype; --assumes all tables have same structure
begin
select max(table_name)
into table_name
from user_tables
where regexp_like ( table_name, 'ESL_\d+{8}$' );
sql_stmt := 'select id, address_number , street_name from ' || table_name ||
' where rownum = 1'; -- replace with your where clause
execute immediate sql_stmt into rw;
dbms_output.put_line(
rw.id || ',' || rw.address_number || ',' || rw.street_name
);
end;
/
1,1,NEW MAIN STREET
If you want to know more about dynamic SQL, check out:
http://stevenfeuersteinonplsql.blogspot.co.uk/2016/07/a-quick-guide-to-writing-dynamic-sql-in.html http://www.oracle.com/technetwork/issue-archive/2015/15-may/o35plsql-2541606.html But there is a better way. Stop creating date specific tables! Instead have a single ESL table with a date column separating the values. Then you can use regular static SQL instead of messing around with the dynamic stuff.
For example:
create table esl (
load_quarter date, id int, address_number int, street_name nvarchar2 ( 80 )
) ;
insert into esl values ( date'2016-06-23', 1, 1, 'NEW MAIN STREET') ;
insert into esl values ( date'2016-06-23', 1, 2, 'NEW MAIN STREET') ;
insert into esl values ( date'2016-01-06', 1, 11, 'OLD MAIN STREET') ;
insert into esl values ( date'2016-01-06', 1, 12, 'OLD MAIN STREET') ;
select * from esl
where load_quarter = date'2016-01-06';
LOAD_QUARTER ID ADDRESS_NUMBER STREET_NAME
06-JAN-2016 00:00:00 1 11 OLD MAIN STREET
06-JAN-2016 00:00:00 1 12 OLD MAIN STREET
You may want check out partitioning to help you manage this. This effectively splits your single ESL table into several smaller ones. But with the upside that you access them like a one table.