I am writing a PL/SQL procedure that loads some data from Schema A into Schema B. They are both very different schemas and I can't change the structure of Schema B.

Columns in various tables in Schema A (joined together in a view) need to be inserted into Schema B as key=>value pairs in 2 columns in a table, each on a separate row. For example, an employee's first name might be present as employee.firstname in Schema A, but would need to be entered in Schema B as:

id=>1, key=>'A123', value=>'Smith'

There are almost 100 keys, with the potential for more to be added in future. This means I don't really want to hardcode any of these keys.

Sample code:

create table schema_a_employees (
    emp_id number(8,0),
    firstname varchar2(50),
    surname varchar2(50)
);
insert into schema_a_employees values ( 1, 'James', 'Smith' );
insert into schema_a_employees values ( 2, 'Fred', 'Jones' );

create table schema_b_values (
    emp_id number(8,0),
    the_key varchar2(5),
    the_value varchar2(200)
);

I thought an elegant solution would most likely involve a lookup table to determine what value to insert for each key, and doesn't involve effectively hardcoding dozens of similar statements like....

insert into schema_b_values ( 1, 'A123', v_firstname );
insert into schema_b_values ( 1, 'B123', v_surname );

What I'd like to be able to do is have a local lookup table in Schema A that lists all the keys from Schema B, along with a column that gives the name of the column in the table in Schema A that should be used to populate, e.g. key "A123" in Schema B should be populated with the value of the column "firstname" in Schema A, e.g.

create table schema_a_lookup (
    the_key varchar2(5),
    the_local_field_name varchar2(50)
);
insert into schema_a_lookup values ( 'A123', 'firstname' );
insert into schema_a_lookup values ( 'B123', 'surname' );

But I'm not sure how I could dynamically use values from the lookup table to tell Oracle which columns to use.

So my question is, is there an elegant solution to populate schema_b_values table with the data from schema_a_employees without hardcoding for every possible key (i.e. A123, B123, etc)?

Cheers.