Converting a 'select' into dynamic SQL

From: Jeremy <jeremy0505_at_gmail.com>
Date: Wed, 6 Dec 2006 16:16:58 -0000
Message-ID: <MPG.1fe0fd48630d497a98a38d@news.individual.net>

Oracle 9iR2

Hi folks, in the pl/sql excerpt below I want to be able to replace the subquery starting "select c02_show label, sum(c03_show) value ..." with some dynamic (by which I mean that part of the query needs to be built at run time, the rest remains static) SQL.

  begin
    for i in (select xmlelement

                     ("chart",
                     xmlattributes (p_caption as "caption",
                                    p_subcaption as "subcaption",
                                    p_xaxisname as "xAxisName",
                                    p_yaxisname as "yAxisName"),
                     xmlagg (xmlelement 
                             ("set",
                             xmlattributes(x.label as "label",
                                           x.value as "value")
                              )             
                         )                 
                     ) as result


              --

              -- this bit coming up needs to be variable

              --

              from (select  c02_show label, sum(c03_show) value 
                    from ic_grid_rows
                    where grid_id = p_grid_id
                    group by c02_show) x
              ) 
    loop
      l_clob := xmltype.extract(i.result,'/').getclobval;
      exit;

    end loop;
  end;

As far as I have been able to ascertain, I can't simply turn this into an

  execute immediate 'select ..... ' into my_var;

Unless I am being very stupid (and perhaps I am!) I had thought I might be able to define a variable (e.g. my_var) and associate it with a "type" that could match the output of this select statement below.

How should I define my_var in order to be able to achieve this?

thanks for any input

-- 
jeremy

 ============================================================
   ENVIRONMENT:                                             
   Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8 
 ============================================================

Received on Wed Dec 06 2006 - 10:16:58 CST