Dynamic typing or generics in Oracle PL / SQL

Question

For some reason, I have certain fields in the table that are collections of characters. Scarves of different lengths. Example:

create or replace type t_charray_1 as varray(5) of char(1); create or replace type t_charray_2 as varray(5) of char(2); create or replace type t_charray_3 as varray(5) of char(3); create or replace type t_charray_4 as varray(5) of char(4); create table mytable ( field1 number, field2 t_charray_1, field3 t_charray_3, 

In addition, I have a function that returns a string representation (fixed length) of mytable . This function calls other functions that return a string representation of the specified field, entered as a set. Examples:

  function to_chr( p_array in t_charray_1, pad_length in number, p_list_length in number ) return char as v_res varchar2(255) := ''; begin for i in 1 .. p_list_length loop if p_array is not null and p_array.exists(i) and p_array(i) is not null then v_res := v_res || rpad(p_array(i), pad_length, ' '); else v_res := v_res || rpad(' ', pad_length, ' '); end if; end loop; return v_res; end to_chr; ------------------------------------------------------------------------------ function to_chr( p_array in t_charray_2, pad_length in number, p_list_length in number ) return char as v_res varchar2(255) := ''; begin for i in 1 .. p_list_length loop if p_array is not null and p_array.exists(i) and p_array(i) is not null then v_res := v_res || rpad(p_array(i), pad_length, ' '); else v_res := v_res || rpad(' ', pad_length, ' '); end if; end loop; return v_res; end to_chr; 

Please note that these functions are overloaded versions of each other. The only difference in their signature is the type of the p_array argument.

Also note that the bodies of these functions are identical.

Motivation

I want to remove duplicate code. What are my options?

EDIT I heard about sys.anydata, but never used it. Could this be a solution?

Answer 1

You can write a procedure that takes the largest type and explicitly skip the smaller types into the larger type before passing them. Please note that CAST can only be used in SQL.

 DECLARE x t_charray_1 := t_charray_1(); y t_charray_2 := t_charray_2(); PROCEDURE foo( p_foo t_charray_2 ) AS BEGIN FOR i IN p_foo.FIRST..p_foo.LAST loop dbms_output.put_line( p_foo(i) ); END LOOP; END; BEGIN x.EXTEND; x.EXTEND; x(1) := 'A'; x(2) := 'B'; y.EXTEND; y.EXTEND; y(1) := 'AA'; y(2) := 'BB'; foo(y); SELECT CAST(x AS t_charray_2) INTO y FROM dual; foo(y); END; / 

Answer 2

Try:

 create or replace function to_chr(p_array in anydata, pad_length in number, p_list_length in number) return char as v_res varchar2(255) := ''; x number; v_array t_charray_4; v_array1 t_charray_1; v_array2 t_charray_2; v_array3 t_charray_3; begin dbms_output.put_line(p_array.GetTypeName); case p_array.GetTypeName when '<schema>.T_CHARRAY_1' then x := p_array.GetCollection(v_array1); select cast(v_array1 as t_charray_4) into v_array from dual; when '<schema>.T_CHARRAY_2' then x := p_array.GetCollection(v_array2); select cast(v_array2 as t_charray_4) into v_array from dual; when '<schema>.T_CHARRAY_3' then x := p_array.GetCollection(v_array3); select cast(v_array3 as t_charray_4) into v_array from dual; when '<schema>.T_CHARRAY_4' then x := p_array.GetCollection(v_array); end case; for i in 1 .. p_list_length loop if v_array is not null and v_array.exists(i) and v_array(i) is not null then v_res := v_res || rpad(v_array(i), pad_length, ' '); else v_res := v_res || rpad(' ', pad_length, ' '); end if; end loop; return v_res; end to_chr; 

you can run it as follows:

 declare p_array anydata; v_array t_charray_3 := new t_charray_3('aaa', 'bbb'); v_res varchar2(255); begin p_array := anydata.convertcollection(v_array); v_res := to_chr(p_array => p_array, pad_length => 2, p_list_length => 3); end;