Home » SQL & PL/SQL » SQL & PL/SQL » How to return dynamic # of columns from stored procedure? (Oracle 9.2)
How to return dynamic # of columns from stored procedure? [message #429989] Fri, 06 November 2009 13:32 Go to next message
astehman
Messages: 5
Registered: November 2009
Location: Pittsburgh
Junior Member
I am new to writing my own PL/SQL and have tried to find how to do this, but think I am not searching with the right terminology. (Please pardon me if I don't get the terminology right here either!)

What I need to do:
1. select multiple columns from table A (master table), which will return only 1 row
2. select 1 column from table B (a one-to-many table), which will return N rows
3. return the data as if it were all one row

e.g. (this is a very simple example compared to what I am actually trying to do)

select shapeId, shape, numSides from shapes where shapeId=1

shapeID shape numSides
-------------------------
3 square 4

select color from shape_colors where shapeId=1

color
--------
red
blue
purple
green


The returning result set needs to look like

shapeId shape numSides color1 color2 color3 color4
----------------------------------------------------------------
3 square 4 red blue purple green



The purpose is because there are variable number of rows for the master ID in the one-to-many table, so for example the result sets returned could vary like so:

shapeId shape numSides color1 color2
------------------------------------------------
1 circle 0 white blue

shapeId shape numSides
--------------------------------
7 hexagon 6

shapeId shape numSides color1 color2 color3
----------------------------------------------------------------
9 parallellogram 4 red blue purple




THANK YOU to anyone who can provide any references examples etc. (My DBA thanks you as well!)

Alexx
Re: How to return dynamic # of columns from stored procedure? [message #429991 is a reply to message #429989] Fri, 06 November 2009 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/294381/102589/#msg_294381

Regards
Michel
Re: How to return dynamic # of columns from stored procedure? [message #430191 is a reply to message #429989] Mon, 09 November 2009 07:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there a maximum number of colours that you will need to display?
Re: How to return dynamic # of columns from stored procedure? [message #430195 is a reply to message #430191] Mon, 09 November 2009 07:18 Go to previous messageGo to next message
astehman
Messages: 5
Registered: November 2009
Location: Pittsburgh
Junior Member
Thanks for the link Michel.

JRowBottom - the example I provided is a little more simplistic than what I actually need to do. There is no maximum - conceptually - but I suppose one could be defined, if that makes things easier?

The actual need, without going into excruciating detail (and maybe this is how I should have shown the example in the first place), is that there is a document table which has a 1-many table containing the CC's for that document. Only one row will ever be returned from the master doc table, and 0-n rows from the CC table. There could be no CC's for the doc, or there could be any number (realistically not more than say 10?). I need to be able to return all the columns from the document table PLUS the CC's as if they were all one row in a table

So example (simplistic) a doc with all its fields and n # of cc's

DOC_ID | DOC_TYPE | DOC_OWNER | <etc> | CC_1 | CC_2 | ... | CC_n

or a doc with all its fields and no cc's

DOC_ID | DOC_TYPE | DOC_OWNER | <etc>


I hope this makes some sense.

Thank you!
Re: How to return dynamic # of columns from stored procedure? [message #430207 is a reply to message #430195] Mon, 09 November 2009 08:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is this - if you want the results in seperate columns, then you need to have a defined number of columns.
If you don't mind having all the results in a single column, as a comma seperated list, then it's easy - just use the user defined STRAGG aggregate.
Re: How to return dynamic # of columns from stored procedure? [message #430215 is a reply to message #430207] Mon, 09 November 2009 09:11 Go to previous messageGo to next message
astehman
Messages: 5
Registered: November 2009
Location: Pittsburgh
Junior Member
I was thinking if I could execute a query first to count the number of rows in the CC table (N), could I use that count to then dynamically construct a record type with N number of columns, and then populate the data into the record for return?
Re: How to return dynamic # of columns from stored procedure? [message #430216 is a reply to message #430215] Mon, 09 November 2009 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I use that count to then dynamically construct a record type with N number of columns, and then populate the data into the record for return?
But first you would have to dynamically construct PL/SQL that would be able to handle the variable number of returned columns.

Can't you see that this is a very BAD idea?
Re: How to return dynamic # of columns from stored procedure? [message #430217 is a reply to message #430216] Mon, 09 November 2009 09:27 Go to previous messageGo to next message
astehman
Messages: 5
Registered: November 2009
Location: Pittsburgh
Junior Member
Like I said, I'm a pl/sql newbie - which is why I'm posting. If I could handle this outside of pl/sql, I would, but legacy constraints are forcing my hand to a stored procedure.

I've since learned that there is no way to dynamically create a record type, such as to declare a record and then subsequently in a loop add column defs to it.

So is there something else I could do, such as declare a temporary table with the desired structure, populate the data into it, and then read it back into a result set? I'm sure I'm not the first person attempting to solve this problem, so what do other people do?

[Updated on: Mon, 09 November 2009 10:10]

Report message to a moderator

Re: How to return dynamic # of columns from stored procedure? [message #430219 is a reply to message #430217] Mon, 09 November 2009 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>2. select 1 column from table B (a one-to-many table), which will return N rows
Return a REF_CURSOR so the desired rows can be processed as needed

I have never seen any business requirement stating data must be in a single row; only homework assignments.
Re: How to return dynamic # of columns from stored procedure? [message #430220 is a reply to message #430217] Mon, 09 November 2009 09:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's a bad idea, because it's unscalable - either you have a single type that gets created and deleted, causing a bottleneck in a multi user application, or you create and alter many many different types.
Either way is resource intensive.

I'd probable use an array for this - create a user defined table type in SQL, load the data into that and return it.

Or, failiing that, I'd use @BlackSwans approach and return a ref cursor that the calling code could use to retrieve the values.
Re: How to return dynamic # of columns from stored procedure? [message #430222 is a reply to message #430220] Mon, 09 November 2009 10:25 Go to previous messageGo to next message
astehman
Messages: 5
Registered: November 2009
Location: Pittsburgh
Junior Member
JRowBottom, thanks for your reply. I also appreciate the fact you are not condescending. I am a JEE consultant, neither DBA nor student, looking to implement a change to the enterprise system that has minimal impact to a legacy component that will go away in a year, a Microsoft Word component which does require that data be returned in one call, one row. (Yes, it sounds ridiculous. I didn't design it, I wasn't here.)

The alternative I was thinking about, which I realize is also resource intensive, is to dynamically generate SQL to create a temporary table and select the data into it, then pull the whole row back.

Re: How to return dynamic # of columns from stored procedure? [message #430225 is a reply to message #430222] Mon, 09 November 2009 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>a Microsoft Word component which does require that data be returned in one call, one row. (Yes, it sounds ridiculous. I didn't design it, I wasn't here.)

WORD does not know or care about multiple data elements.
Just concatenate together into single VARCHAR2(32000) variable
Re: How to return dynamic # of columns from stored procedure? [message #430400 is a reply to message #430222] Tue, 10 November 2009 05:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given that this is a fix for something that will go away in a year, I'm guessing we can live with a solution that is 99% perfect.

You said:Quote:
There could be no CC's for the doc, or there could be any number (realistically not more than say 10?)


So, in this case, let's go for 20 - 100% margin of error.

Here's a sample of how to do it:
create table shape (shape_id number, shape varchar2(30), num_sides number);

create table shape_colours (shape_id number, colour  varchar2(30));

insert into shape values (1,'square',4);
insert into shape values (2,'hexagon',6);

insert into shape_colours values (1,'red');
insert into shape_colours values (1,'reddish brown');
insert into shape_colours values (1,'reddish green');
insert into shape_colours values (1,'pale red');
insert into shape_colours values (1,'dark red');

insert into shape_colours values (2,'blue');
insert into shape_colours values (2,'purple');
insert into shape_colours values (2,'sky blue');
insert into shape_colours values (2,'pale blue');
insert into shape_colours values (2,'dark blue');
insert into shape_colours values (2,'dark green');

commit;

select shape_id
      ,shape
      ,num_sides
      ,max(case when rnum = 1 then colour else null end) colour_1
      ,max(case when rnum = 2 then colour else null end) colour_2
      ,max(case when rnum = 3 then colour else null end) colour_3
      ,max(case when rnum = 4 then colour else null end) colour_4
      ,max(case when rnum = 5 then colour else null end) colour_5
      ,max(case when rnum = 6 then colour else null end) colour_6
      ,max(case when rnum = 7 then colour else null end) colour_7
      ,max(case when rnum = 8 then colour else null end) colour_8      
from (select s.shape_id
            ,s.shape
            ,s.num_sides
            ,sc.colour
            ,row_number() over (partition by s.shape_id order by null) rnum
      from   shape s
            ,shape_colours sc
      where  s.shape_id = sc.shape_id)
group by shape_id
        ,shape
        ,num_sides
order by shape_id;


Previous Topic: ORA-01848
Next Topic: Group by Hours - Show Missing Hours
Goto Forum:
  


Current Time: Fri Dec 09 03:34:46 CST 2016

Total time taken to generate the page: 0.05237 seconds