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  |
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 #430195 is a reply to message #430191] |
Mon, 09 November 2009 07:18   |
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 #430217 is a reply to message #430216] |
Mon, 09 November 2009 09:27   |
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 #430220 is a reply to message #430217] |
Mon, 09 November 2009 09:54   |
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   |
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 #430400 is a reply to message #430222] |
Tue, 10 November 2009 05:29  |
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;
|
|
|
Goto Forum:
Current Time: Sat Feb 15 13:54:38 CST 2025
|