0

I have an oracle database and I am running the below query

select table_name
from db_test.test
where table_name like '%20170128'

This returns me a column with all the tables with the specific date at the end. How can I take this list and query them?

Improve this question
6
  • 1
    You have to use dynamic SQL -- think execute immediate. Commented Apr 20, 2021 at 11:01
  • 1
    As an aside, I'd point out that if you have incorporated dates into your table names, then you have a severe design flaw in your system. Instead of creating different tables just to hold different dates worth of data, you should have a single table with a DATE column to distinguish the data by date. Commented Apr 20, 2021 at 13:59
  • I get your point, you are totally correct in hive I have partitioned by date. Although the table in the oracle is like this : id_code_date and store records for each date for a specific id_code. Thanks a lot for this point of view. Commented Apr 20, 2021 at 16:07
  • 1
    It might be easiest to create a view dynamically and then query that however you want. Years ago (before partitioning) I saw a system that rebuilt a view of the last 30 days' date-specific tables (using union all) every night as a batch job. Would something like that work? Commented Apr 20, 2021 at 23:07
  • 1
    There's no URL - this was a system at a telco where I worked in the mid 90s. Partitioning didn't exist so instead they had a table for each day, and every night they rebuilt a view that was a UNION ALL of all the tables for individual dates. Then you could select * from v_calls_last_30_days where call_date = date '2021-04-21'. I could probably work up a demo, though your example suggests you need to go back 4 years so this may not be practical for your case. Commented Apr 21, 2021 at 12:46

1 Answer 1

Reset to default
1

You'd need dynamic SQL. If you're running a simple query against each table (I'm just doing a count(*) in this example), something like this would work

declare
  l_cnt integer;
  l_sql varchar2(1000);
begin 
  for t in (select table_name
              from db_test.test
             where table_name like '%20170128')
  loop
    l_sql := 'select count(*) from ' || t.table_name;
    execute immediate l_sql
                 into l_cnt;
    dbms_output.put_line( t.table_name || ' has ' || l_cnt || ' rows.' );
  end loop;
end;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks a lot, I want to select * . Is it possible please to explain me the logic?
@DrGenius - What are you going to do with the results? If you're going to do an execute immediate, you'd need to have somewhere to put the results which would require that you know the structure of the results in advance. Generally, that's not possible if you're building a query at runtime but maybe all the tables in your system have a fixed structure.
The structure is fixed. I want to fetch with sqoop data from oracle db into hive. But instead of every time write the table name I want to make it more automatic.
@DrGenius - OK. So every table that query returns has the identical structure? Having table names with dates embedded in them and having a bunch of tables with the same structure seems a really weird way to structure a database. In the loop, you could fetch the data into a local collection defined to match the structure of the table. But if the goal is to return the data to a client application, this seems like a fundamentally poor approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.