2

I have tables as follows in Oracle DB: abc20190101,abc20190102,etc. Each have same columns data type and same number of columns just populated with different data. I have a scheduler to run a query on these tables everyday at particular time. But table name should be appended with sysdate. Actually I need to perform only select and join operations on 2 tables like abc20190101,xyz20190101

I tried this :

select * from (select concat('abc',(SELECT to_char(sysdate-1,'yyyymmdd') from dual)) as "Table" from dual) ;

But this just returns the table name as output instead of the data values inside the table.

I have also tried this PL/SQL procedure:

declare tabname varchar(32); begin tabname := 'abc'|| TO_CHAR(SYSDATE, 'YYYYMMDD'); execute immediate 'select * from '|| tabname ; end;

But this gives syntax error. As I am new to PL/SQL stored procedures, I couldn't find where the error is. Will be helpful if someone can tell how to run select query on table name with dynamic sysdate appended to it

katipra
  • 55
  • 7
  • 2
    This seem to be an **awful** data model. Why did you do that? There should be a single table with a DATE datatype column so that you could distinguish rows by date. Then you'd simply `select * from your_table where date_column = date '2019-01-01'`. If possible, abandon what you're currently doing, it just doesn't scale and brings problems wherever you look. – Littlefoot Feb 04 '19 at 16:46
  • @Littlefoot This is how people do partitions without paying for licence – Kacper Feb 04 '19 at 16:55
  • I'm sure they "enjoy" in their way of partitioning data. – Littlefoot Feb 04 '19 at 16:58
  • @Littlefoot - given the cost of Oracle Partitioning license some organisations have no choice but to "enjoy" it. Although an organisation which has 60m rows per table i.e. per partition really should see if they can scrape the budget together. – APC Feb 04 '19 at 18:09

2 Answers2

2

Trying dynamic SQL is proper way but if you do select you need to specify how would you like to return results. So you need something like:

declare
  tabname varchar(32);
  result YOUR_TYPE;
begin
  tabname := 'abc'|| TO_CHAR(SYSDATE, 'YYYYMMDD');
  execute immediate 'select * from ' || tabname into result;
end;

This will work if your query return single result so I guess you are not happy with that. You can select into collection or type with bulk collect

Probably you want to do something with your selected data so you can go for returning results as ref cursor or iterating results in loop

Kacper
  • 4,798
  • 2
  • 19
  • 34
  • will bulk collect have any performance issue if each of my table has around 60 million records ? – katipra Feb 04 '19 at 17:09
  • @katipra - are you actually running queries which return 60 million rows? i.e. no joins or filters? Hmmm, you're going to need to do something fancy to cater for that. You're on a voyage of discovering why organisation decide to pay Oracle's Partitioning licence fees. – APC Feb 04 '19 at 18:15
  • @APC most of the daily activities are carried out by triggers which capture request and response and put it into temporary tables which get purged after some hours.Only a few times we need to query the main tables as is required.Anyways, I don't have the knowledge of the exact architecture. But is there anything "fancy" which can be done in this particular case ? – katipra Feb 04 '19 at 19:50
  • @katipra the best performance you'll get using bult collect with limit and process by mini batches. But generally I agree that such architecture is weird and it won't be easy task to perform queries without understaning architecture or even changing it. If this is 60m every single index is meaningful and there are no general solutions – Kacper Feb 05 '19 at 09:45
0

Below code snippet will solve your problem.

        set SERVEROUTPUT ON;
    declare
      tabname varchar(32);
      query_sql varchar2(300);
      result1 varchar2(20);
    begin
      tabname := 'tab_'|| TO_CHAR(SYSDATE, 'YYYYMMDD'); -- use your table and date
      query_sql := 'select account_name from '|| tabname; --modify query as per your requirement
      execute immediate query_sql into result1;
      dbms_output.put_line(result1);
    exception 
    when others then
        dbms_output.put_line(sqlerrm);
    end;
    /
pushpendu dhara
  • 97
  • 7