Re: Reporting with dynamic sql

On 16 Aug 2006 12:20:24 -0700, "Deltones" <vibroverb@xxxxxxxxxxx>
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
.

Relevant Pages

  • Re: Reporting with dynamic sql
    ... Sybrand Bakker wrote: ... In Oracle you *don't* need temporary tables. ... What you need is a REF CURSOR. ... variable cur REF CURSOR ...
    (comp.databases.oracle.misc)
  • Re: Thank you
    ... >> Thank you both for pointing out this isn't a worldwide forum. ... >> Sybrand Bakker, Senior Oracle DBA ... > Oracle Open World is an event that is sponsored by Oracle Corporation. ... Also, nearly all the relatives I had in Vegas have moved elsewhere, so ...
    (comp.databases.oracle.server)
  • Re: Avoiding generating redo logs
    ... triangles on top of a column, indicating sort order, and the ... their knowledge of Oracle. ... Sybrand Bakker, Senior Oracle DBA ... Generally this is, as Sybrand states, a sign of bringing habits from ...
    (comp.databases.oracle.server)
  • Re: newbie passing parameters from Web page to Stored Procedure
    ... I think it is in my First SQL statement. ... I'm using Oracle 9i. ... CREATE OR REPLACE PACKAGE pkgpending AS ... What you likely want to do is return a REF CURSOR. ...
    (comp.databases.oracle.server)
  • Re: Handling exceptions from table columns values and emailing ASAP Please
    ... I am using Oracle 9 and UTL_SMTP pacakge. ... Sybrand Bakker, Senior Oracle DBA ... If someone did not respond after my second plea for help then I ... question or quite possibly forgot to respond and my second response ...
    (comp.databases.oracle.misc)