Re: Converting a 'select' into dynamic SQL

From: Jeremy <jeremy0505_at_gmail.com>
Date: Wed, 6 Dec 2006 17:10:26 -0000
Message-ID: <MPG.1fe109df5d2ae99898a38f@news.individual.net>

In article <1165424636.446633_at_bubbleator.drizzle.com>, DA Morgan says...
> Jeremy wrote:
> > In article <1165422900.197585_at_bubbleator.drizzle.com>, DA Morgan says...
> >>> 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.
> >>
> >
> > Only one - always - so should I be able to use the "into my_var" format?
>
> Yes.
>

What I thought. Can anyone help to identify what the definition of "my_var" should be in this case?

The SQL statement is:

       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
 

If I place that into e.q. var l_sql

declare

 l_sql       varchar2(4000);
 my_var      ???

begin
  l_sql := <the select statment>;
  execute immediate l_sql into my_var;
end;

Many many thanks

-- 

Received on Wed Dec 06 2006 - 11:10:26 CST