René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback

March 24, 2006: On emulating tables with PL/SQL (Part II)  
On January 8^{th} 2006, I have demonstrated how to
emulate tables with nested tables in PL/SQL. Unfortunatly, these emulated tables have a drawback:
they can only store a number and a varchar2(15) per row because the dynamic row type is created like so:
create type row_type as object ( a number, b varchar2(15) ) /
Now, I want to be able to create truly dynamic tables with nested tables, that is, I want to control the number of columns in the table.
Therefore, the row type is now created differently:
create type dynamic_row_t as table of varchar2(50) /
A table consists of multiple rows of that dynamic row type.
create type dynamic_tbl_t as table of dynamic_row_t /
In order to demonstrate this newly created type, I define a funtion that returns a
dynamic_tbl_t .
The function can be passed the number of columns and rows (parameters cols and rows ) that the returned table must contain:
create function create_dynamic_tbl ( cols in number, rows in number ) return dynamic_tbl_t as ret dynamic_tbl_t; begin ret := dynamic_tbl_t(); ret.extend(rows); for r in 1 .. rows loop ret(r) := dynamic_row_t(); ret(r).extend(cols); for c in 1 .. cols loop ret(r)(c) := dbms_random.string('a', 10); end loop; end loop; return ret; end create_dynamic_tbl; /
I can now use this function to create a dynamic 3 columns by 7 rows table and
select * from it using the table() syntax.
select * from table(create_dynamic_tbl(3, 7));
Yet, the advantage of being able to create dynamic width tables comes with a disadvantage: Only one column (appropriatly called:
column_value) is returned. The type of the column is dynamic_row_t:
COLUMN_VALUE  DYNAMIC_ROW_T('cYUZDzopAT', 'jLWcBBcGKt', 'bVyfiGgGgs') DYNAMIC_ROW_T('jCAlmQHtjL', 'HYGNZJbsoI', 'DWjfuKfBfj') DYNAMIC_ROW_T('rXsBFVbdWl', 'NkfRGBfZXX', 'PjdQXdNgnS') DYNAMIC_ROW_T('AyRbNNuiqv', 'uocUaJYPFS', 'ivkJAPBFhs') DYNAMIC_ROW_T('obcoMaxazw', 'wZOArqtBfd', 'wvcXbAOThv') DYNAMIC_ROW_T('FjJSjGPaua', 'GUoTSZldKg', 'sxBKQPcqHt') DYNAMIC_ROW_T('VDJJPggdam', 'QzWUwOVNBm', 'hHRiFVylJr')
Of course, I am not satisfied with this, I want to be able to select each column seperatly. To do so, I need another helping function:
create function get_dynamic_col( r in dynamic_row_t, c in number ) return varchar2 as begin return r(c); end get_dynamic_col; /
I can pass this function a dynamic_row_t (corresponding to column_value in the previous select statement) and the onebased column number I am interested
in. To demonstrate this function, I create yet another function that returns a dynamic_tbl_t.
This function is passed a number (parameter
x ) and it calculates all products 1*1, 1*2, ... 1*x, 2*1, 2*2, 2*3, ... 2*x, .... (x1)*1, (x1)*2, .. (x1)*(x1), (x1)*x, x*1, x*2,.... x*(x1), x*x and returnes the the factors in the first and second column and the result in the third column:
create function mult_x_x(x in number) return dynamic_tbl_t as ret dynamic_tbl_t; begin ret := dynamic_tbl_t(); for i in 1 .. x loop for j in 1 .. x loop ret.extend; ret(ret.count) := dynamic_row_t(); ret(ret.count).extend(3); ret(ret.count)(1) := i; ret(ret.count)(2) := j; ret(ret.count)(3) := i*j; end loop; end loop; return ret; end mult_x_x; / sho err
For demonstration, I use the function to calculate all products 1*1 .. 5*5 through passing 5 as
x :
col i for a2 col j for a2 col k for a2 select get_dynamic_col(column_value,1) i, ' * ' mult , get_dynamic_col(column_value,2) j, ' = ' eq, get_dynamic_col(column_value,3) k from table(mult_x_x(5));
Here's the result:
I MUL J EQ K      1 * 1 = 1 1 * 2 = 2 1 * 3 = 3 1 * 4 = 4 1 * 5 = 5 2 * 1 = 2 2 * 2 = 4 2 * 3 = 6 2 * 4 = 8 2 * 5 = 10 3 * 1 = 3 3 * 2 = 6 3 * 3 = 9 3 * 4 = 12 3 * 5 = 15 4 * 1 = 4 4 * 2 = 8 4 * 3 = 12 4 * 4 = 16 4 * 5 = 20 5 * 1 = 5 5 * 2 = 10 5 * 3 = 15 5 * 4 = 20 5 * 5 = 25
To make a bit a more interesting (yet still contrived) application of this function, I want to find all (integer) products that yield 18:
select get_dynamic_col(column_value, 1) i, get_dynamic_col(column_value, 2) j from table(mult_x_x(18)) where get_dynamic_col(column_value,3)=18;
The result:
I J   1 18 2 9 3 6 6 3 9 2 18 1 Links
See also Emulating tables with PL/SQL (Part III).
More on OracleThis is an on Oracle article. The most current articles of this series can be found here.
