Dynamically accessing member of a User-defined objects in PL/SQL

Question

Is there any way to access a dynamically accessing member of a User- defined record, object or reference cursor using a variable? E.g. Something like

  get_member(my_object, 'member name');

Or maybe

  my_object.$'member name';

EXECUTE IMMEDIATE won't work as it does not operate within the scope of my procedure.

Let me brifly explain what i am trying to accomplish. I have a mapping table M which describes how records of table A should be transformed into records of table B. The mapping must vary according the specific type of record in A (given by A.type). I wanted to perform the mapping something like this (not exactly, planning on encapsulating the mapping logic inside a stream function, but the principle remains similar):

SELECT
  ...   
  CASE 
    WHEN M.field_1_mapping IS NOT NULL THEN
      -- column of A given by value of M.field_1_mapping
    ELSE
      null -- field_1 not filled for record type
  END field_1,
  --- etc.
FROM
  table_a A,
  mapping_table M
WHERE
  A.TYPE = M.TYPE

So my question is how I might do this. Again i cannot use dynamic SQL as it has to different for each record type, but if column could be selected based on the value of the mapping field then the obovemention sql would work.

I realize that it simply might not be possible (and may go against the PL/SQL design philosophy), in which case i would welcome any suggestions you might have as to how this problem could be solved.

P.S: I suppose it would be possible to simply hard-code a mapping function e.g.:

FUNCTION get_field(field_key IN VARCHAR(32), a NOCOPY IN table_a%rowtype) RETURN VARCHAR(2000) IS
  out VARCHAR2(2000)
BEGIN
  -- ...
  IF field_key = 'field_1' THEN
    RETURN a.field_1; END IF;
  -- ..
END;

But that seems really inelegant.

Answer 1

Here is some code that will construct dynamic SQL from the mapping definitions. For simplicity I have used the EMP table as table A, with column DEPTNO serving as the type.

declare
   q long;
   rc sys_refcursor;
   first boolean := true;
   l_field1 varchar2(100);
   l_field2 varchar2(100);

   function mapcol (p_field_mapping varchar2) return varchar2
   is
      l_retval varchar2(32);
   begin
      if p_field_mapping is not null then
         l_retval := 'to_char(a.' || p_field_mapping || ')';
      else
         l_retval := 'null';
      end if;
      return l_retval;
   end;
begin
   -- Construct dynamic SQL
   for r_map in (select * from mapping_table)
   loop
      if first then
         first := false;
      else
         q := q || ' union all ';
      end if;
      q := q || 'select ';
      q := q || mapcol(r_map.field_1_mapping) || ', ';
      q := q || mapcol(r_map.field_2_mapping);
      q := q || ' from emp a where a.deptno = ' || r_map.type;
   end loop;

   -- Run SQL and show results
   dbms_output.put_line('SQL = ' || q);
   dbms_output.put_line('');
   dbms_output.put_line('Results');
   dbms_output.put_line('-------');
   open rc for q;
   loop
      fetch rc into l_field1, l_field2;
      exit when rc%notfound;
      dbms_output.put_line(l_field1 || ', ' || l_field2);
   end loop;
end;

I then created this mapping table:

SQL> create table mapping_table (type integer,
  2>   field_1_mapping varchar2(30), field_2_mapping varchar2(30));

Table created.

SQL> insert into mapping_table values (10, 'ENAME', 'SAL');

1 row created.

SQL> insert into mapping_table values (20, 'SAL', 'JOB');

1 row created.

SQL> insert into mapping_table values (30, 'JOB', 'HIREDATE');

1 row created.

SQL> commit;

Commit complete.

When I run it (with SERVEROUTPUT ON in SQL Plus) I get:

SQL = select to_char(a.ENAME), to_char(a.SAL) from emp a where a.deptno = 10
union all
select to_char(a.SAL), to_char(a.JOB) from emp a where a.deptno = 20
union all
select to_char(a.JOB), to_char(a.HIREDATE) from emp a where a.deptno = 30

Results
-------
CLARK, 7450
KING, 10000
TEST,
MILLER, 6500
BINNSY, 100
FARMER, 123
7975, MANAGER
4566, ANALYST
8000, ANALYST
5000, janitor
SALESMAN,
SALESMAN, 22-FEB-1981
SALESMAN, 28-SEP-1981
MANAGER, 01-MAY-1981
SALESMAN, 08-SEP-1981
MANAGER, 19-JUL-2008

PL/SQL procedure successfully completed.