Re: Reporting with dynamic sql

On 16 Aug 2006 12:20:24 -0700, "Deltones" <vibroverb_at_hotmail.com> wrote:

>----- Stuff snipped for brevity -----
>Sybrand Bakker wrote:
>> creating 'temporary tables' on the fly has 'bad idea' inscribed all
>> over it. This is Oracle. In Oracle you *don't* need temporary tables.
>>
>> What you need is a REF CURSOR.
>>
>> Like this
>>
>> variable cur REF CURSOR
>> begin
>> open :cur for
>> '<your query *without temporary tables* here';
>> end;
>> /
>> print cur
>
>Allright, I reached another wall. In the report I'm trying to fix, 3
>temp tables are created. I'm trying to find a way to convert the script
>by using ref cursor instead, like Sybrand stated above. My problem is
>that one of the temp table is created using data from another temp
>table. This is from the script I'm working on:
>
>CREATE TABLE quart_avg_da1_&&4 (quart,
> emp_name,
> pick_line,
> tmp_moy,
> nb_pick,
> tmp_moy_caisse,
> nb_caisse,
> poids)
>AS
> SELECT p.quart,
> e.emp_name,
> p.pick_line,
>
>avg(decode(sign(p.cmpl_time-p.begin_time),-1,p.cmpl_time+86399-p.begin_time,p.cmpl_time-p.begin_time))
>* 60 * 60 * 24,
> count(*),
>
>(sum(decode(sign(p.cmpl_time-p.begin_time),-1,p.cmpl_time+86399-p.begin_time,p.cmpl_time-p.begin_time))/sum(qty))
>* 60 * 60 * 24,
> sum(qty),
> sum(wght)/1000
> FROM quart_total_tmp_da1_&&4 p,
> emp e
> WHERE (e.emp_name = '&&4' or '&&4' = '$' or '&&4' = 'TOUS')
> AND e.emp_no = p.emp_no
> GROUP BY p.quart,
> e.emp_name,
> p.pick_line;
>
>
>The other temp table is quart_total_tmp_da1_&&4 as you can see in the
>FROM section(&&4 would be the employee code for example). Now, select
><fields> from <table_name> I know. But I'm not so sure that SELECT
><fields> FROM <ref cursor> is gonna work. In fact, I just tried it, and
>it doesn't. What would be the best way to proceed then? By the way,
>sorry for the formatting of the query. Got kinda mangled by google.

I'm not convinced you can't do without the temporary table, but right now we still have insufficient information.

Oracle also has inline views:

select dname, number_of_emps
from dept,
(select deptno, count(*) number_of_emps
 from emp
group by deptno) e
where e.deptno = d.deptno
/

is just one example.
There is also the WITH statement which allows you to reuse the results of a statement in a composite statement.

If that all wouldn't work, you can store your results in a pl/sql table (a pl/sql table is basically identical to a 3GL array) and that pl/sql table CASTed to an ordinary table in a REF CURSOR.

--
Sybrand Bakker, Senior Oracle DBA