This discussion is archived
11 Replies Latest reply: Apr 3, 2012 4:48 AM by BluShadow RSS

Table name in from clause

846947 Newbie
Currently Being Moderated
Hi Gurus,

Each day I am creating a table dynamically and the table name is stored in log table.
End of the day I want to see that tables data(want to create a report based on that table).

select * from (
select table_name from my_log
where created_on=sysdate and table_name like '%sunil%');

As we know, it will not work in sql, and I dont want to use plsql.
Is there any way to get my requirements only using sql.

Thanks,
Sunil
  • 1. Re: Table name in from clause
    prakash Pro
    Currently Being Moderated
    hi,

    take the output of this query and run it .

    SELECT 'select * from  ' || table_name || ';'
      FROM my_log
     WHERE created_on = SYSDATE AND table_name LIKE '%sunil%' ;
    :)

    Thanks,
    P Prakash

    Edited by: prakash on Apr 3, 2012 1:20 AM
  • 2. Re: Table name in from clause
    Karthick_Arp Guru
    Currently Being Moderated
    Sunil Jena wrote:
    Hi Gurus,

    Each day I am creating a table dynamically and the table name is stored in log table.
    End of the day I want to see that tables data(want to create a report based on that table).

    select * from (
    select table_name from my_log
    where created_on=sysdate and table_name like '%sunil%');

    As we know, it will not work in sql, and I dont want to use plsql.
    Is there any way to get my requirements only using sql.

    Thanks,
    Sunil
    SQL is static. If you want it Dynamic you need to go for PL/SQL. If you dont want to use PL/SQL then you need to specify each table name from your log table manually and run your query.
  • 3. Re: Table name in from clause
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Sunil Jena wrote:

    Each day I am creating a table dynamically and the table name is stored in log table.
    Not the best of options. If you have purchased the Partitioning Option, use that instead. If not - give serious consideration to getting it as it is worth every cent.
    End of the day I want to see that tables data(want to create a report based on that table).

    select * from (
    select table_name from my_log
    where created_on=sysdate and table_name like '%sunil%');
    Not possible like that.

    It can be done using a pipeline table functions. Which would be the wrong choice IMO.

    A better option would be a partitioned view. This feature has been introduced with Oracle 7.3 I think it was. It supports partition-like pruning and enables you to have a view on several tables, but only the relevant table being hit for the actual query.

    Here's an example on 11.2.0.2. Note how the CBO uses the filter condition NULL is not NULL to remove no-relevant tables from the query.
    SQL> declare
      2          tabName         varchar2(30);
      3          nameList        TStrings;
      4          pviewSql        varchar2(32767);
      5  
      6          procedure W( line varchar2 ) is
      7          begin
      8                  DBMS_OUTPUT.put_line( nvl(line,chr(10) ) );
      9          end;
     10  
     11          procedure ExecSQL( sqlStmnt varchar2 ) is
     12          begin
     13                  W( 'SQL> '||sqlStmnt );
     14                  W( '' );
     15                  execute immediate sqlStmnt;
     16          exception when OTHERS then
     17                  W( SQLERRM(SQLCODE) );
     18          end;
     19  begin
     20          --// need to create some sample tables - will grab dictionary objects from
     21          --// the following schemas to populate the sample tables
     22          nameList := new TStrings( 'XDB','SYS','BILLY','APEX_040100','FLOWS_FILES' );
     23  
     24          --// format of the sampe table, e.g. TAB_1
     25          tabName := 'TAB_%n';
     26  
     27          --// drop the sample table if exists
     28          for i in 1..nameList.Count loop
     29                  ExecSQL( 'drop table '||replace(tabName,'%n',i)||' purge' );
     30          end loop;
     31  
     32          --// create the sample tables
     33          for i in 1..nameList.Count loop
     34                  ExecSQL(
     35  'create table '||replace(tabName,'%n',i)||'(
     36          object_id primary key,
     37          object_owner not null check(object_owner='''||nameList(i)||'''),
     38          object_type not null,
     39          object_name not null
     40  )
     41  nologging as
     42  select
     43          object_id,
     44          owner,
     45          object_type,
     46          object_name
     47  from       all_objects
     48  where      owner = '''||nameList(i)||''''
     49                  );
     50          end loop;
     51  
     52          --// create the partition view
     53          pviewSql := 'create or replace view PVIEW_TAB as ';
     54          for i in 1..nameList.Count loop
     55                  pviewSQL := pviewSQL || 'select * from '||replace(tabName,'%n',i);
     56                  if i < nameList.Count then
     57                          pviewSQL := pviewSQL || ' union all ';
     58                  end if;
     59          end loop;
     60  
     61          ExecSQL( pviewSQL );
     62  end;
     63  /
    SQL> drop table TAB_1 purge
    
    
    SQL> drop table TAB_2 purge
    
    
    SQL> drop table TAB_3 purge
    
    
    SQL> drop table TAB_4 purge
    
    
    SQL> drop table TAB_5 purge
    
    
    SQL> create table TAB_1(
            object_id primary key,
            object_owner not null check(object_owner='XDB'),
            object_type not null,
            object_name not null
    )
    nologging
    as
    select
            object_id,
            owner,
            object_type,
            object_name
    from    all_objects
    where   owner = 'XDB'
    
    
    SQL> create table TAB_2(
            object_id primary key,
            object_owner not null check(object_owner='SYS'),
            object_type not null,
            object_name not null
    )
    nologging
    as
    select
            object_id,
            owner,
            object_type,
            object_name
    from    all_objects
    where   owner = 'SYS'
    
    
    SQL> create table TAB_3(
            object_id primary key,
            object_owner not null check(object_owner='BILLY'),
            object_type not null,
            object_name not null
    )
    nologging
    as
    select
            object_id,
            owner,
            object_type,
            object_name
    from    all_objects
    where   owner = 'BILLY'
    
    
    SQL> create table TAB_4(
            object_id primary key,
            object_owner not null check(object_owner='APEX_040100'),
            object_type not null,
            object_name not null
    )
    nologging
    as
    select
            object_id,
            owner,
            object_type,
            object_name
    from    all_objects
    where   owner = 'APEX_040100'
    
    
    SQL> create table TAB_5(
            object_id primary key,
            object_owner not null check(object_owner='FLOWS_FILES'),
            object_type not null,
            object_name not null
    )
    nologging
    as
    select
            object_id,
            owner,
            object_type,
            object_name
    from    all_objects
    where   owner = 'FLOWS_FILES'
    
    
    SQL> create or replace view PVIEW_TAB as select * from TAB_1 union all select * from TAB_2 union all select * from TAB_3 union all select * from TAB_4 union all select * from TAB_5
    
    
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> set autotrace on explain
    SQL> --// example of using the partition view, but querying only a single table in it
    SQL> select count(*) from pview_tab where object_owner = 'BILLY' and object_type = 'TABLE';
    
      COUNT(*)
    ----------
            27
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3160219530
    
    -----------------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |    28 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |           |     1 |    28 |            |          |
    |   2 |   VIEW                | PVIEW_TAB |    31 |   868 |     3   (0)| 00:00:01 |
    |   3 |    UNION-ALL          |           |       |       |            |          |
    |*  4 |     FILTER            |           |       |       |            |          |
    |*  5 |      TABLE ACCESS FULL| TAB_1     |     1 |    28 |     3   (0)| 00:00:01 |
    |*  6 |     FILTER            |           |       |       |            |          |
    |*  7 |      TABLE ACCESS FULL| TAB_2     |     2 |    56 |    51   (2)| 00:00:01 |
    |*  8 |     TABLE ACCESS FULL | TAB_3     |    27 |   756 |     3   (0)| 00:00:01 |
    |*  9 |     FILTER            |           |       |       |            |          |
    |* 10 |      TABLE ACCESS FULL| TAB_4     |     1 |    28 |     3   (0)| 00:00:01 |
    |* 11 |     FILTER            |           |       |       |            |          |
    |* 12 |      TABLE ACCESS FULL| TAB_5     |     1 |    28 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(NULL IS NOT NULL)
       5 - filter("OBJECT_OWNER"='BILLY' AND "OBJECT_TYPE"='TABLE')
       6 - filter(NULL IS NOT NULL)
       7 - filter("OBJECT_OWNER"='BILLY' AND "OBJECT_TYPE"='TABLE')
       8 - filter("OBJECT_OWNER"='BILLY' AND "OBJECT_TYPE"='TABLE')
       9 - filter(NULL IS NOT NULL)
      10 - filter("OBJECT_OWNER"='BILLY' AND "OBJECT_TYPE"='TABLE')
      11 - filter(NULL IS NOT NULL)
      12 - filter("OBJECT_OWNER"='BILLY' AND "OBJECT_TYPE"='TABLE')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> select count(*) from pview_tab where object_owner = 'SYS' and object_type = 'TABLE';
    
      COUNT(*)
    ----------
            33
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1404131350
    
    -----------------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |    28 |    51   (2)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |           |     1 |    28 |            |          |
    |   2 |   VIEW                | PVIEW_TAB |    15 |   420 |    51   (2)| 00:00:01 |
    |   3 |    UNION-ALL          |           |       |       |            |          |
    |*  4 |     FILTER            |           |       |       |            |          |
    |*  5 |      TABLE ACCESS FULL| TAB_1     |     1 |    28 |     3   (0)| 00:00:01 |
    |*  6 |     TABLE ACCESS FULL | TAB_2     |    11 |   308 |    51   (2)| 00:00:01 |
    |*  7 |     FILTER            |           |       |       |            |          |
    |*  8 |      TABLE ACCESS FULL| TAB_3     |     1 |    28 |     3   (0)| 00:00:01 |
    |*  9 |     FILTER            |           |       |       |            |          |
    |* 10 |      TABLE ACCESS FULL| TAB_4     |     1 |    28 |     3   (0)| 00:00:01 |
    |* 11 |     FILTER            |           |       |       |            |          |
    |* 12 |      TABLE ACCESS FULL| TAB_5     |     1 |    28 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(NULL IS NOT NULL)
       5 - filter("OBJECT_OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
       6 - filter("OBJECT_OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
       7 - filter(NULL IS NOT NULL)
       8 - filter("OBJECT_OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
       9 - filter(NULL IS NOT NULL)
      10 - filter("OBJECT_OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
      11 - filter(NULL IS NOT NULL)
      12 - filter("OBJECT_OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> select count(*) from pview_tab where object_type = 'TABLE';
    
      COUNT(*)
    ----------
            78
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1006798202
    
    ----------------------------------------------------------------------------------
    | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |           |     1 |    11 |    62   (2)| 00:00:01 |
    |   1 |  SORT AGGREGATE      |           |     1 |    11 |            |          |
    |   2 |   VIEW               | PVIEW_TAB |    57 |   627 |    62   (2)| 00:00:01 |
    |   3 |    UNION-ALL         |           |       |       |            |          |
    |*  4 |     TABLE ACCESS FULL| TAB_1     |    17 |   187 |     3   (0)| 00:00:01 |
    |*  5 |     TABLE ACCESS FULL| TAB_2     |    11 |   121 |    51   (2)| 00:00:01 |
    |*  6 |     TABLE ACCESS FULL| TAB_3     |    27 |   297 |     3   (0)| 00:00:01 |
    |*  7 |     TABLE ACCESS FULL| TAB_4     |     1 |    11 |     3   (0)| 00:00:01 |
    |*  8 |     TABLE ACCESS FULL| TAB_5     |     1 |    11 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter("OBJECT_TYPE"='TABLE')
       5 - filter("OBJECT_TYPE"='TABLE')
       6 - filter("OBJECT_TYPE"='TABLE')
       7 - filter("OBJECT_TYPE"='TABLE')
       8 - filter("OBJECT_TYPE"='TABLE')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> set autotrace off
    Partition views are no longer actively supported. It could be removed in future Oracle versions. However, it is a useful and existing feature, that is worth considering in some cases.
  • 4. Re: Table name in from clause
    MichaelS Guru
    Currently Being Moderated
    Is there any way to get my requirements only using sql.
    If it is just about the data, you can display them easily in xml format:
    select table_name, x.*
      from (select table_name, 'ora:view("' || table_name || '")' t
              from my_log
             where created_on=sysdate and table_name like '%sunil%'),
           xmltable (t) x
  • 5. Re: Table name in from clause
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    That is... evil...


    ;-)
  • 6. Re: Table name in from clause
    846947 Newbie
    Currently Being Moderated
    Hi Billy ,

    Thanx, but I don't to use plsql, because I need only sql.

    Hi Michele,
    Thanx
    Your code is generating xml data, but I need in tabular way.
    Is there any way, I will convert from there to normal tabular way.
    Thanks,
    Sunil
  • 7. Re: Table name in from clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Sunil Jena wrote:
    Hi Billy ,

    Thanx, but I don't to use plsql, because I need only sql.

    Hi Michele,
    Thanx
    Your code is generating xml data, but I need in tabular way.
    Is there any way, I will convert from there to normal tabular way.
    Thanks,
    Sunil
    No, not without using PL/SQL.
    As already explained to you, SQL cannot have dynamic table names.
    For dynamic queries you must use PL/SQL.
  • 8. Re: Table name in from clause
    MichaelS Guru
    Currently Being Moderated
    Is there any way, I will convert from there to normal tabular way
    Not without the price of loosing datatype and column_name. But you can define a max size of gerneric columns all varchar2:
    SQL> select table_name, x.*
      from (select table_name, 'ora:view("' || table_name || '")' t
              from user_tables my_log
             where table_name in ('DEPT', 'EMP')),
           xmltable (t columns col1 varchar2(10) path '*[1]',
                               col2 varchar2(20) path '*[2]',
                               col3 varchar2(20) path '*[3]',
                               col4 varchar2(20) path '*[4]',
                               col5 varchar2(20) path '*[5]') x
    /
    TABLE_NAME COL1            COL2                           COL3                           COL4                           COL5                          
    ---------- --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
    DEPT       10              ACCOUNTING                     NEW YORK                                                                                    
    DEPT       20              RESEARCH                       DALLAS                                                                                      
    DEPT       30              SALES                          CHICAGO                                                                                     
    DEPT       40              OPERATIONS                     BOSTON                                                                                      
    DEPT       50              SALES                          MUNICH                                                                                      
    EMP        7900            JAMES                          CLERK                          7698                           1981-12-03                    
    EMP        7788            SCOTT                          ANALYST                        7566                           1987-04-19                    
    EMP        7369            SMITH                          CLERK                          7902                           1980-12-17                    
    EMP        7499            ALLEN                          SALESMAN                       7698                           1981-02-20                    
    EMP        7521            WARD                           SALESMAN                       7698                           1981-02-22                    
    EMP        7566            JONES                          MANAGER                        7839                           1981-04-02                    
    EMP        7654            MARTIN                         SALESMAN                       7698                           1981-09-28                    
    EMP        7698            BLAKE                          MANAGER                        7839                           1981-05-01                    
    EMP        7782            CLARK                          MANAGER                        7839                           1981-06-09                    
    EMP        7839            KING                           PRESIDENT                      1981-11-17                     5000                          
    EMP        7844            TURNER                         SALESMAN                       7698                           1981-09-08                    
    EMP        7876            ADAMS                          CLERK                          7788                           1987-05-23                    
    EMP        7902            FORD                           ANALYST                        7566                           1981-12-03                    
    EMP        7934            MILLER                         CLERK                          7782                           1982-01-23                    
    
    19 rows selected.
    And as you see, all gets screwed up if some columns have NULL values in them ;)
  • 9. Re: Table name in from clause
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Sunil Jena wrote:
    Hi Billy ,

    Thanx, but I don't to use plsql, because I need only sql.
    The example shows how a partition view works in SQL.

    The PL/SQL code is used only to create the sample tables and the sample partition view, in order to demonstrate how a partitioned view works in SQL.

    Do not confuse the feature (partition views) with the code used to create the SQL objects to demonstrate the feature.
  • 10. Re: Table name in from clause
    carmac Newbie
    Currently Being Moderated
    When i run your script i got an error like table or view doesn't exit " xmltable"
  • 11. Re: Table name in from clause
    BluShadow Guru Moderator
    Currently Being Moderated
    924902 wrote:
    When i run your script i got an error like table or view doesn't exit " xmltable"
    What database version are you using? XMLTABLE has been available for many years since 10g

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points