This discussion is archived
14 Replies Latest reply: Oct 7, 2013 4:43 AM by AkhileshB RSS

How to Use dyanamic table in query?

Swagh Newbie
Currently Being Moderated

Hi,

 

can you please tell.in query -

 

1] if we define a varibale and in that varibale we have to speacfy the dynamic table name can it's possible.

2] I tried but it's not working

3] Every year i have taken a backup table and from that backup table i feteched the data.But i want to do this programmatically

 

e.g. Table name is pf_emp_pf_txn20122013 but this is "20122013" this dynamic .How we use in query that dyanamic table .

 

Kindly Suggest,

 

 

Regards,

Sachin

  • 1. Re: How to Use dyanamic table in query?
    Newbie
    Currently Being Moderated

    Your EXEC executes in a different context, therefore it is not aware of any variables that have been declared in your original context. You should be able to use a temp table instead of a table variable as shown in the simple demo below.

    create table #t (id int)

    declare @value nchar(1) set @value = N'1' 

    declare @sql nvarchar(max)

    set @sql = N'insert into #t (id) values (' + @value + N')'

    exec (@sql)

    select * from #t

    drop table #t

  • 2. Re: How to Use dyanamic table in query?
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    hi,

    1) did you hear about dynamic sql???

    2) do you search your question on google?

    3) did you hear about partitioning table?? Have you got it?

     

     

    ----

    Ramin Hashimzade

  • 3. Re: How to Use dyanamic table in query?
    Karthick_Arp Guru
    Currently Being Moderated
    >1] if we define a varibale and in that varibale we have to speacfy the dynamic table name can it's possible.
    YES
    >2] I tried but it's not working
    Show us what you have tried. Then we can tell you why it dint work.
    >3] Every year i have taken a backup table and from that backup table i feteched the data.But i want to do this programmatically
    >e.g. Table name is pf_emp_pf_txn20122013 but this is "20122013" this dynamic .How we use in query that dyanamic table .
    Look for EXECUTE IMMEDIATE Statement and DBMS_SQL. One of them can be used to build Dynamic SQL and execute it.
  • 4. Re: How to Use dyanamic table in query?
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    this is not the answer he is looking for...

     

    ---

    Ramin Hashimzade

  • 5. Re: How to Use dyanamic table in query?
    Swagh Newbie
    Currently Being Moderated

    Hi,

     

    thats mean we have to crate the one table #TempMaster

     

    e.g my table query is as follow -

     

    select

      p51_pf_num

    , 0

    , p51_eps_amt

    , p51_basic_da

    , p51_prl_mth

    , to_number(to_char(add_months(to_date(p51_prl_mth,'yyyymm'), 1),'yyyymm'))

    , p51_eps_amt

    , decode(p51_basic_da,greatest(p51_basic_da,6500),6500,p51_basic_da)

    from pf_emp_pf_txn20122013

    where p51_comp_man in ('C','M')

    and p51_br in (41,51)

     

    Table 'pf_emp_pf_txn_20122013'.I want to change this table dynamically  menns next year its ''pf_emp_pf_txn_20132014'.

    in code howi define this without changing manually.

     

    kindly suggest

  • 6. Re: How to Use dyanamic table in query?
    Karthick_Arp Guru
    Currently Being Moderated

  • 7. Re: How to Use dyanamic table in query?
    BluShadow Guru Moderator
    Currently Being Moderated

    Swagh wrote:

     

    Hi,

     

    can you please tell.in query -

     

    1] if we define a varibale and in that varibale we have to speacfy the dynamic table name can it's possible.

    2] I tried but it's not working

    3] Every year i have taken a backup table and from that backup table i feteched the data.But i want to do this programmatically

     

    e.g. Table name is pf_emp_pf_txn20122013 but this is "20122013" this dynamic .How we use in query that dyanamic table .

     

    Kindly Suggest,

     

     

    Regards,

    Sachin

     

    Take a step back and rethink this.

    Creating table names that are based on dates is wrong.  This is not how you design databases correctly.

    A "date" is a piece of data, and this should be stored as data on the table, not as part of the table/object name.

     

    What is the purpose of 'backing up' data to another table?  Backups should be done to tape or disk mirroring or mirrored servers or data recovery sites etc.

     

    If it's actually archiving of data, rather than duplicating it, then you should consider just keeping the data in the main table and using date partitions on the table so that the performance of queries (I'm assuming you're talking large volumes of data) can target the correct partitions and quickly get to the 'current' data rather than the 'archived' partitions.

     

    It is generally bad design to move data from the main table(s) to other tables as a backup/archive.  What happens when there comes another requirement to report on all the historical and current data, and aggregate it?  You'll end up having to union the data together from multiple tables, and then the query cannot optimally perform aggregations of data as easily, etc. which will effect performance.  On top of that, if the table names are created dynamically based on dates, then you won't be able to have static code that can access that data, and that becomes even more problematic.

     

    So, rethink your design, and look to do it properly with static table names and partitions etc.  Remember, Oracle is perfectly capable of handling very very large volumes of data, if the tables and indexes have been designed properly.

  • 8. Re: How to Use dyanamic table in query?
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    RHELSENSEI wrote:

     

    Your EXEC executes in a different context, therefore it is not aware of any variables that have been declared in your original context. You should be able to use a temp table instead of a table variable as shown in the simple demo below.

    create table #t (id int)

    declare @value nchar(1) set @value = N'1' 

    declare @sql nvarchar(max)

    set @sql = N'insert into #t (id) values (' + @value + N')'

    exec (@sql)

    select * from #t

    drop table #t

     

    Epic. Fail.

     

    SQL-Server syntax and features for an Oracle problem. Features that are not relevant at all to Oracle, but the very reason why SQL-Server is technically inferior to Oracle...

     

    Are you a professional idiot, or just a very gifted amateur?

  • 9. Re: How to Use dyanamic table in query?
    Newbie
    Currently Being Moderated

    pleace try this

    You probably want something like this (may want to check syntax exactly):

    EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL WHERE ROWNUM = 1' RETURNING INTO secondnumber;

    If you need a dynamic table name you could do the following:

    EXECUTE IMMEDIATE 'SELECT 1 FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(pTableName) || ' WHERE ROWNUM = 1' RETURNING INTO secondnumber;

    However, one should ALWAYS question the need for dynamic SQL.

  • 10. Re: How to Use dyanamic table in query?
    Hoek Guru
    Currently Being Moderated

    2] I tried but it's not working

    Explain why instead of being vague..

    Read: http://tkyte.blogspot.nl/2005/06/how-to-ask-questions.html

    and: Re: 2. How do I ask a question on the forums?

  • 11. Re: How to Use dyanamic table in query?
    AkhileshB Newbie
    Currently Being Moderated

    Hello,

     

    The following statement would give the required filename. This can be stored in a variable and this variable can then be used in the dynamic SQL query.

     

    select 'pf_emp_pf_txn_'||to_char(trunc(sysdate,'yyyy')-1,'yyyy')||to_char(trunc(sysdate,'yyyy'),'yyyy') from dual;

     

    execute immediate

    'select

      p51_pf_num

    , 0

    , p51_eps_amt

    , p51_basic_da

    , p51_prl_mth

    , to_number(to_char(add_months(to_date(p51_prl_mth,''yyyymm''), 1),''yyyymm''))

    , p51_eps_amt

    , decode(p51_basic_da,greatest(p51_basic_da,6500),6500,p51_basic_da)

    from' ||v_filename||'

    where p51_comp_man in (''C'',''M'')

    and p51_br in (41,51)';

  • 12. Re: How to Use dyanamic table in query?
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    In Oracle, this type of dynamic usage of tables are done using reference cursors.

     

    HOWEVER.. the specifics of what you are doing raise some serious concerns ito fundamental database and Oracle concepts.

     

    That said, here is the basic Oracle approach to this type of problem:

    create or replace procedure FunkyFoo( c out sys_refcursor, day date ) is
      tableName varchar2(30);
    begin
      tableName := 'pf_emp_pf_txn'||to_char(day,'yyyymmdd');
      open c for
      'select  .. from '||tableName;
    end;
  • 13. Re: How to Use dyanamic table in query?
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Why use SQL with a select against DUAL to assign a value to a PL/SQL variable?

     

    This is silly:

    declare
      d date;
    begin
      select sysdate into d from dual;
      ..
    end;

     

    This is how it should be done:

    declare
      d date;
    begin
      d := sysdate;
      ..
    end;
  • 14. Re: How to Use dyanamic table in query?
    AkhileshB Newbie
    Currently Being Moderated

    ohhh .. yes my mistake 

Legend

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