Re: Converting a 'select' into dynamic SQL

Jeremy wrote:
>
> 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

You can't necessarily use INTO my_var. How many records will be returned?

If more than one you need to use the OPEN REFCURSOR FOR syntax.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org