Re: Reporting with dynamic sql

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 17 Aug 2006 08:53:41 -0700
Message-ID: <1155830022.745899@bubbleator.drizzle.com>

Deltones wrote:
> DA Morgan wrote:

>> Deltones wrote:
>>> G Quesnel wrote:
>>>> BTW / just a little side note - you are not using TEMP tables.
>>> These tables are dropped at the end of the script.
>> This is horrible. Please pull over to the side of the road and ask
>> for help. This is like watching two kids street racing and knowing
>> what is going to happen sooner or later. The suspense is killing me.
>> --

>
> That's exactly what I'm trying to do on this thread (the asking for
> advice, not the racing part ;)
>
> If I wanted to do the concept below, how would you go about it?
>
> variable mycur refcursor;
> variable yourcursor refcursor;
>
> begin
> open :mycur for
> 'select emp_name, emp_no from emp';
>
> open :yourcursor for
> 'select a.emp_name, b.salary from :mycursor a, salary b';
> end;
>
> As you can see, that's basically the kind of construct from the "create
> table" query you saw earlier, but I'm trying to figure out how to do it
> using Sybrand's advice on using refcursor instead. But the selecting
> from another refcursor, as seen in the "open :yourcursor" section
> doesn't work.
>
> Denis

Ed it correct. And from my experience teaching Oracle at the U ... the beginning students inevitably make things far more complicated than they need be. The more experience you get the more likely solutions will be simplified and general.

Be sure in the future to give your version number but you need to look at, and understand, the difference between STRONGLY TYPED and WEAKLY TYPED ref cursors.

In the examples above, and the one suggested by Ed, it would be better to use strongly typed.

You can find demos of each at www.psoug.org in Morgan's Library.

-- 
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

Received on Thu Aug 17 2006 - 10:53:41 CDT