Foray into Table functions – dynamically creating rows

 

Today I ran into the challenge of having to dynamically create rows in a query – I wanted to take a set of rows and based on certain values in the each row turn it into one or more rows. So in a result set of 3 rows I might want to end up with 53 rows. In my scenario my result set was configuration parameters that describe how to set up my buckets. For example my config table might have 3 rows

Row#         Duration                  Bucket_size
1               168                        4
2               80                          8
3               24                         24

Row 1 I want to become 42 rows representing 42 buckets of 4 hours each – row 2 I want to become 10 rows representing 8 hours each and row 3 becomes 1 row representing 24 hours. Now I racked my brain trying to figure out how I might do this using connect by or some other logic, but couldn’t come up with a way – that method depends on you having all the rows you need and just linking to them by certain criteria – I needed to create rows based on the content of the specific row. I knew that I could always take the parameter data (shown above) and create the rows in temp tables or something like that using PL/SQL – but that required a processing routine and the storage of the data and I really wanted to be able to do this in one SQL statement. That is when I turned to Tom Kyte’s books – We have largely been an 8.1.7.x shop for a long time and are just beginning to move systems to 10g. I am fortunate enough to be able to work on a project using 10g and all the new features so I had some more options at my disposal to solve the problem.

Table functions were the answer – if you haven’t seen or heard of table functions I recommend looking at the Oracle docs or even better Tom’s books which explain them much better than I ever could – I will however give you an example of how I used it.

First I created the Table function (Tom’s examples were key in helping me come up with this)
TYPE refcur_vt IS REF CURSOR RETURN P_PARAMETERLIST%ROWTYPE;
TYPE virtual_table_type is table of number;
FUNCTION createVirtualTable(p_ParamTable refcur_vt) return virtual_table_type PIPELINED;

FUNCTION createVirtualTable(p_ParamTable refcur_vt) return virtual_table_type PIPELINED is
l_vt_type virtual_table_type := virtual_table_type();
in_rec p_ParamTable%ROWTYPE;
num_of_buckets number;
bucket_counter number := 0;
BEGIN
Loop
fetch p_ParamTable into in_rec;
exit when p_ParamTable%NOTFOUND;
num_of_buckets := round(to_number(in_rec.prm_level1_value)/to_number(in_rec.parameter_value),0);
for i in 1..num_of_buckets loop
l_vt_type.extend();
l_vt_type(l_vt_type.count) := bucket_counter + (i * to_number(in_rec.parameter_value));
PIPE ROW(l_vt_type(l_vt_type.count));
end loop;
bucket_counter := bucket_counter + (num_of_buckets * to_number(in_rec.parameter_value));
end loop;
close p_ParamTable;
return;
END createVirtualTable;

What happens here is you pass into the function a result set representing the parameter table (those three rows listed above) – I fetch the row – based on two values in that row I figure out how many rows I need to create and iterate through – creating them and adding them to the collection – note that the function is pipelined so I return NULL at the end of the function, but as I generate the new values for the collection I send them back to the client (effectively returning them) using the PIPE ROW call – The way I think of that to make sense out of it is it is like reading one of the rows from disk and returning it back to the client while you go get the next row (or in our case generate the next row). So that is the logic to create the rows – now lets see how we use that function in a SQL statement to get what we want.

select
sysdate + (to_number((lag(column_value,1,0) over (order by to_number(column_value))))/24) as Begin_Date,
sysdate + (to_number(column_value)/24) as End_Date,
to_number(column_value) - to_number((lag(column_value,1,0) over (order by to_number(column_value)))) as Bucket_Size,
rownum as Bucket_Number from
table(mypackage.createVirtualTable(
cursor(select * from parameter_table where parameter_name = 'My Parameters' order by to_number(param_order))
));

The Table function part can be found in this section – table(mypackage.createVirtualTable(
cursor(select * from parameter_table where parameter_name = ‘My Parameters’ order by to_number(param_order))
))
. It shows up in the from clause – you put table({stuff}) and inside that you put the function you are calling – in our case the function takes a cursor as a variable so we use Cursor({sql statement}) to grab a cursor of the stuff we need (in this case the parameter table) and pass it to the function – the return is a set of rows with a single column with the heading column_value. In the query above I do some manipulation to take the buckets after I have created and then put a start_date and end_date associated with them to give the bins a date value. Below I copy both the simple output (just the single column output from the table function) and then the output from the query above with the data values.

COLUMN_VALUE
4
8
12
16
20
24
28
32
36
40
44
48
52
56
60
64
68
72
76
80
84
88
92
96
100
104
108
112
116
120
124
128
132
136
140
144
148
152
156
160
164
168
176
184
192
200
208
216
224
232
240
248
272
BEGIN_DATE	END_DATE	BUCKET_SIZE	BUCKET_NUMBER
09-Aug-2005 1:17:53	09-Aug-2005 5:17:53	4	1
09-Aug-2005 5:17:53	09-Aug-2005 9:17:53	4	2
09-Aug-2005 9:17:53	09-Aug-2005 13:17:53	4	3
09-Aug-2005 13:17:53	09-Aug-2005 17:17:53	4	4
09-Aug-2005 17:17:53	09-Aug-2005 21:17:53	4	5
09-Aug-2005 21:17:53	10-Aug-2005 1:17:53	4	6
10-Aug-2005 1:17:53	10-Aug-2005 5:17:53	4	7
10-Aug-2005 5:17:53	10-Aug-2005 9:17:53	4	8
10-Aug-2005 9:17:53	10-Aug-2005 13:17:53	4	9
10-Aug-2005 13:17:53	10-Aug-2005 17:17:53	4	10
10-Aug-2005 17:17:53	10-Aug-2005 21:17:53	4	11
10-Aug-2005 21:17:53	11-Aug-2005 1:17:53	4	12
11-Aug-2005 1:17:53	11-Aug-2005 5:17:53	4	13
11-Aug-2005 5:17:53	11-Aug-2005 9:17:53	4	14
11-Aug-2005 9:17:53	11-Aug-2005 13:17:53	4	15
11-Aug-2005 13:17:53	11-Aug-2005 17:17:53	4	16
11-Aug-2005 17:17:53	11-Aug-2005 21:17:53	4	17
11-Aug-2005 21:17:53	12-Aug-2005 1:17:53	4	18
12-Aug-2005 1:17:53	12-Aug-2005 5:17:53	4	19
12-Aug-2005 5:17:53	12-Aug-2005 9:17:53	4	20
12-Aug-2005 9:17:53	12-Aug-2005 13:17:53	4	21
12-Aug-2005 13:17:53	12-Aug-2005 17:17:53	4	22
12-Aug-2005 17:17:53	12-Aug-2005 21:17:53	4	23
12-Aug-2005 21:17:53	13-Aug-2005 1:17:53	4	24
13-Aug-2005 1:17:53	13-Aug-2005 5:17:53	4	25
13-Aug-2005 5:17:53	13-Aug-2005 9:17:53	4	26
13-Aug-2005 9:17:53	13-Aug-2005 13:17:53	4	27
13-Aug-2005 13:17:53	13-Aug-2005 17:17:53	4	28
13-Aug-2005 17:17:53	13-Aug-2005 21:17:53	4	29
13-Aug-2005 21:17:53	14-Aug-2005 1:17:53	4	30
14-Aug-2005 1:17:53	14-Aug-2005 5:17:53	4	31
14-Aug-2005 5:17:53	14-Aug-2005 9:17:53	4	32
14-Aug-2005 9:17:53	14-Aug-2005 13:17:53	4	33
14-Aug-2005 13:17:53	14-Aug-2005 17:17:53	4	34
14-Aug-2005 17:17:53	14-Aug-2005 21:17:53	4	35
14-Aug-2005 21:17:53	15-Aug-2005 1:17:53	4	36
15-Aug-2005 1:17:53	15-Aug-2005 5:17:53	4	37
15-Aug-2005 5:17:53	15-Aug-2005 9:17:53	4	38
15-Aug-2005 9:17:53	15-Aug-2005 13:17:53	4	39
15-Aug-2005 13:17:53	15-Aug-2005 17:17:53	4	40
15-Aug-2005 17:17:53	15-Aug-2005 21:17:53	4	41
15-Aug-2005 21:17:53	16-Aug-2005 1:17:53	4	42
16-Aug-2005 1:17:53	16-Aug-2005 9:17:53	8	43
16-Aug-2005 9:17:53	16-Aug-2005 17:17:53	8	44
16-Aug-2005 17:17:53	17-Aug-2005 1:17:53	8	45
17-Aug-2005 1:17:53	17-Aug-2005 9:17:53	8	46
17-Aug-2005 9:17:53	17-Aug-2005 17:17:53	8	47
17-Aug-2005 17:17:53	18-Aug-2005 1:17:53	8	48
18-Aug-2005 1:17:53	18-Aug-2005 9:17:53	8	49
18-Aug-2005 9:17:53	18-Aug-2005 17:17:53	8	50
18-Aug-2005 17:17:53	19-Aug-2005 1:17:53	8	51
19-Aug-2005 1:17:53	19-Aug-2005 9:17:53	8	52
19-Aug-2005 9:17:53	20-Aug-2005 9:17:53	24	53
 
Technorati tags: , ,
Advertisements
This entry was posted in Technology. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s