dynamic sql

From: PHernandez <phph109_at_yahoo.es>
Date: 10 Mar 2006 13:37:35 -0800
Message-ID: <1142026654.996404.321180@j33g2000cwa.googlegroups.com>

I have a proc that is very similar to the example "Demonstration Of Dynamic SQL Statement Creation" in Morgan's library found at http://www.psoug.org/reference/nds.html

Now and then I have to extend it and due to the amount of parameters, it's getting difficult to maintain it.

Is it somehow possible to use an array-like type after "using"?

Something like this:

  type lta is table of varchar2(127) index by binary integer   l_params lta;
  i number : = 0;
  l_sql varchar2(200) := 'select....';
[...]

  if p_a is not null then
    l_sql := l_sql || ' and a = :X' || i;     l_params(i);
    i := i + 1;
  end if;

[ many more similar if blocks ]

open p_cursor for l_sql using l_params;

The above code does *not* work (in perl it would :-) ), it's just to show what I mean...

--ph Received on Fri Mar 10 2006 - 15:37:35 CST