Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Challenging Query Problem!

Re: Challenging Query Problem!

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: 2000/04/11
Message-ID: <8cvj1v$u2i$1@nnrp1.deja.com>#1/1

That query works for a finite number of assignments, but what I think he's asking for is a query that works when there are n assignments and n users. In other words, a query that returns a variable number of columns, which cannot be done with traditional SQL. It has to be created dynamically.

I'm sure someone out there can figure out a means by which to use DBMS_SQL, but it my case I needed to pass back a REF CURSOR for the web application (developed in Delphi) to loop through (don't ask me about that side of it; I have no idea how they handled it, but they did). This can NOT be done with DBMS_SQL. That's when I found Native Dynamic SQL in the oracle documentation (new to version 8.1.5 I think). It will allow you to open a cursor dynamically and pass back a reference to it. Keep in mind that since the number of columns (and their aliases so you know what the column is) will change, the SGA will have to parse and store a version of each. I have found said overhead to be miniscule when compared to the alternative of writing, handling, and maintaining separate SQL statements.

Eddie, I know you were probably hoping for something a little easier, but this is the best way I have found, but it may require you to learn more PL/SQL than it's worth to you as a Web Application Developer. Before you dive in though, make absolutely certain, within your web development language, you can handle/loop through a cursor reference (especially one where you won't know the number of columns, as well as their names, being returned until run-time)...

Michael J. Ort

P.S.
I've also used native dynamic SQL to create what I call 'private views' (variables holding SQL declared in a package body that are concatenated into the from clauses of other dynamic SQL procedures in the package), but that's for another post. You get the picture though. The addition of REF CURSOR and native dynamic SQL open up some interesting possibilities...

In article <8cuplt$rss$2_at_news7.svr.pol.co.uk>,   "Gerrit-Jan Linker" <linker_at_oraxcel.com> wrote:
> Base table:
>
> MyTable:
> Name Assignment Mark
> ==== ======== ====
> name1 assignment1 10
> name2 assignment1 9
> name3 assignment1 9
> name1 assignment2 12
> name2 assignment2 15
> name3 assignment2 11
>
> select name, assignment1, assignment2 from
> ( select name, mark assignment1 from MyTable
> where assignment = 'ASSIGNMENT1') a
> ( select name, mark assignment2 from MyTable
> where assignment = 'ASSIGNMENT2') b
> ( select distinct name from MyTable) c
> where a.name = c.name
> and b.name = c.name
>
> Will output
>
> NAME Assignment1 Assignment2
> name1 10 12
> name2 9 15
> name3 9 11
>
> Have a look at inline views in the Oracle documentation. Inline views
 are
> the subqueries I've done in the from clause.
>
> Hope this helps,
>
> --
> Gerrit-Jan Linker
>
> web: http://www.oraxcel.com
> email: gjlinker_at_oraxcel.com
>
> Oraxcel: Linking Oracle to Excel
> OraCodes: Explaining ORA- error codes
> OraSQL: Explaining Oracle SQL syntax
> OraDebug: PL/SQL debugger
> OraWeb: Oracle access from IE
>
> Eddie <edawad_at_hotmail.com> wrote in message
> news:8ctmg5$296$1_at_nnrp1.deja.com...
> > Hi, I have a query which has the following output:
> >
> > name1 assignment1 10
> > name2 assignment1 9
> > name3 assignment1 9
> > name1 assignment2 12
> > name2 assignment2 15
> > name3 assignment2 11
> >
> > What I want is the following:
> >
> > NAME Assignment1 Assignment2
> > name1 10 12
> > name2 9 15
> > name3 9 11
> > I have absolutely no idea how do this. Neither the number of
 students
> > (name1, name2....) is fixed nor the number of assignments. Any help
 is
> > greatly appreciated. Thanks.
> >
> > --
> > Eddie
> > Web Application Developer
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 11 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US