1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

dynamically select from t1 or from t2 table due to user's input

Discussion in 'SQL PL/SQL' started by ecivgamer, Feb 10, 2012.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all!

    Please suggest me correct syntax of dynamic sql for pipelined function.

     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (Text):

    with t1 as
    (select 1 as t from dual
    ),
    t2 as
    (select 2 as t from dual
    ),
    t3 as (
    select * from TABLE(user's input)
    )
    select * from t3;

     
    But pipelined function can't take the value from t1 or t2 as they are not the DB objects.
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    hi,

    we can write that in following way also:
    select * From &a; --we can give the table name as which one we need as output.

    (or)

    with t1 as
    (select 1 as t from dual
    ),
    t2 as
    (select 2 as t from dual
    ),
    t3 as (
    select * from &t -- here too we can give table name what ever we need.
    )
    select * from t3;

    But if you use different tables rather than those 1 and 2 then to reduce the number of lines in code then we can it in first written way, else you can follow second way. The second way is already said by rajavu in previous post.