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 8th 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 one-based 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, .... (x-1)*1, (x-1)*2, .. (x-1)*(x-1), (x-1)*x, x*1, x*2,.... x*(x-1), 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 Oracle

This is an on Oracle article. The most current articles of this series can be found here.

Comments

Leave a comment! An empty line creates a new paragraph. Urls are recognized if they either start with www. or with http://. In order to post code in non-proportional font, enclose the code in [code] and [/code]. Be sure that [code] and [/code] are both on a single line with nothing else. See also this link for an example.

Name: