Re: Oracle Reports & Stored Procedures

>Please Help: I'm creating a report that is pretty complex. I just
>discovered that a stored procedure exist that will handle all the
>complexities. I want to have one query in my report that makes a call to
>this procedure. Can this be done and how?
>
>Thanks!!!
>

You can use temp tables, as Ian suggested, but you get into problems with multiple users, reports that died/were killed, etc.

If you can make a wrapper or directly use the stored procedures as functions, (with the appropriate PRAGMAs), you can use the function as a column. What you really need is a 1-to-1 correspondence between your reports and the row that generates it...

An pseudo code example:

 you have or write a package...
package heavy_comp

function col1(id integer) return something1;
function col2(id integer) return something2;
function col3(id integer) return something3;
function col4(id integer) return something4;

(Columns are evaluated in lexical order; so you can use col1 to create some package variable used by col2, col3, col4 if you want or need to)

Then you can do this:
select id,col1(id),col2(id),col3(id),col4(id) from the_table;

Then the report can be defined using this query, with out the mess and overhead of temp tables.

Hope this helps

Dan Hekimian-Williams Received on Wed Dec 02 1998 - 05:10:05 CET