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.
