Btukfyl

In Oracle 12c R2, I have a function which receives a row type as a variable. In the function I want to read a table which contains a column name and a value, I want to then populate the row type variable passed in using the column name and the data from the table I read.

Here is a simplistic idea of what I want to do;

CREATE TABLE table_to_be_updated

(

  key_value number,

  cola varchar2(2),

  colb varchar2(2),

  colc varchar2(2),

  cold varchar2(2),

  cole varchar2(2),

  colf varchar2(2)

);



CREATE TABLE table_default_value

(

  default_stuff number,

  column_name varchar(30),

  column_default_value varchar2(2)

);



function do_defaults(in_table table_to_be_updated%rowtype, in_value number) return table_to_be_updated%rowtype

is

  out_table table_to_be_updated%rowtype := in_table;

  cursor my_curs

  is

    select * from table_default_value where default_stuff = in_value;

begin

  for default_rec in my_curs

  loop

    out_table.[default_rec.column_name] := default_rec.column_default_value

  end loop;

  return out_table;

end;

insert into table_default_value (default_stuff,column_name,column_default_value) values (1,'cola','xx'));

insert into table_default_value (default_stuff,column_name,column_default_value) values (1,'colc','aa'));

insert into table_default_value (default_stuff,column_name,column_default_value) values (1,'cole','bb'));

In the line;

out_table.[default_rec.column_name] := [default_rec.column_default_value]

[default_rec.column_name] would be the column name, from the cursor, in out_table name I want to move data to.

and

[default_rec.column_default_value] is the value from the cursor I want to move into that column.

I suspect that what I want to do is impossible in PL/SQL, but I thought I'd ask.

There are other ways to accomplish updating the table directly, specifically using dynamic SQL with execute immediate, but I have a number of similar tables which all need to have the same things done to them, and I would prefer a single function to work on a record and then pass it back to have the calling routine update the proper table.

Here is the best I can come up with;

function do_defaults(in_table table_to_be_updated%rowtype, in_value number) return table_to_be_updated%rowtype

is

  TYPE DEFAULT_TYPE IS TABLE OF VARCHAR2(2)

       INDEX BY VARCHAR2(30);

  DEFAULT_ARRAY DEFAULT_TYPE;



  out_table table_to_be_updated%rowtype := in_table;

  cursor my_curs

  is

    select * from table_default_value where default_stuff = in_value;

begin

  DEFAULT_ARRAY('cola') := null;

  DEFAULT_ARRAY('colb') := null;

  DEFAULT_ARRAY('colc') := null;

  DEFAULT_ARRAY('cold') := null;

  DEFAULT_ARRAY('cole') := null;

  DEFAULT_ARRAY('colf') := null;

  for default_rec in my_curs

  loop

    DEFAULT_ARRAY(default_rec.column_name) := default_rec.column_default_value

  end loop;

  out_table.cola := DEFAULT_ARRAY('cola');

  out_table.colb := DEFAULT_ARRAY('colb');

  out_table.colc := DEFAULT_ARRAY('colc');

  out_table.cold := DEFAULT_ARRAY('cold');

  out_table.cole := DEFAULT_ARRAY('cole');

  out_table.colf := DEFAULT_ARRAY('colf');

  return out_table;

end;