Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: Using variable as table name in dynamic sql

    I am attempting to produce a 'parameterized view' in the form of a pipelined table function, where arguments to the function are used to build dynamic sql (a Select statement). The output of the Select would feed into a cursor. I would then fetch the rows of the cursor and pipe them out (after a little manipulation).

    The problem that I have is that the parameters I am passing in need to be used to form the table names from which I am selecting. In other words, it's my From clause that is parameterized. All of the examples I have been able to find so far only show parameterized Where clauses.

    Every method I have attempted using Table(myVar), Table(cast(myVar as SomeTableType)), etc has failed.

    Can anyone share a working example of dynamic sql (preferably feeding a cursor), where the sql has a parameterized From clause?

    Thank you in advance for your help!

    Neil

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by das_bultz View Post
    Can anyone share a working example of dynamic sql (preferably feeding a cursor), where the sql has a parameterized From clause?
    What's wrong with the examples in the manual?

    For example this one:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDJHAHE

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by das_bultz View Post
    I am attempting to produce a 'parameterized view' in the form of a pipelined table function, where arguments to the function are used to build dynamic sql (a Select statement).
    ...
    Every method I have attempted using Table(myVar), Table(cast(myVar as SomeTableType)), etc has failed.
    For this kind of collection access, there is no need of dynamic SQL (it shall be called statically), so I just wonder, what statement did you write and what was the exact failure description. By the way, collections are described in PL/SQL User's Guide and Reference, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    http://www.dbforums.com/oracle/16342...or-pl-sql.html

    if this kind of thing satisfy your need ( nearly or even barely ) ...
    do tell

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •