Hi,
i have an issue about dynamically transfer values from different columns in two given variables of rowtype-type.
My intention is to transfer the values from table1 to table2.
Basically i've declared to vars of v_table1 and v_table2 % rowtype, each of them contains columns with the same and also individual column names.
v_table2 % rowtype should be initialized with the default values.
I would like to insert values of v_table2 into table2 later by doing "INSERT INTO TABLE2 VALUES v_table2".
Before inserting i need to modify several column values by different rules, but those rules aren't relevant for my actual request.
The point is, how can i transfer the values of v_table1 into v_table2 for those columns hanving the same column-name, without hard coding the column names because those two tables contain a lot of columns.
Furthermore values in v_table2 initialized with default_data values shouldn't be overwritten by non-existing data in the same named column of v_table1.
Example: If v_table1.D is NULL it shouldn't overwrite the former initialized default value 'Monday'.
Conditions:
Table1 contains the following values:
| Column Name | Value |
|---|
| A | '1' |
| B | '2' |
| C | NULL |
| D | 'somedata' |
| E | NULL |
| F | '3' |
After transferring the values of v_table1 into v_table2 i assume the following data:
| Column Name | Value | Source |
|---|
| C | NULL | Table1.C |
| D | 'somedata' | Table1.D |
| E | 'Tuesday' | DATA_DEFAULT of Table2 |
| F | '3' | Table1.F |
| G | NULL | |
| H | NULL | |
Right now i'm already able to fill v_table2 with the default values:
declare
lv_query varchar2(1000);
type cur_typ is ref cursor;
rcur_typ cur_typ;
v_table2 table2%rowtype;
begin
for rec in (select *
from user_tab_columns
where table_name = 'TABLE2'
order by column_id
)
loop
if rec.data_default is null then
lv_query := lv_query||', null';
elsif rec.data_type = 'NUMBER' then
lv_query := lv_query||', '||rec.data_default;
elsif rec.data_type = 'DATE' then
lv_query := lv_query||', '||rec.data_default;
else
lv_query := lv_query||', '||rec.data_default||'';
end if;
end loop;
lv_query := 'SELECT'||substr(lv_query, 2)||' FROM DUAL';
dbms_output.put_line(lv_query);
open rcur_typ for lv_query;
fetch rcur_typ into v_table2;
close rcur_typ;
end;
I have no idea how to achieve the transfer from v_table1 to v_table2 without overriding the default values of table2.
As far as i know i can find out those columns with matching columns names by querying:
select column_name from user_tab_cols where table_name='TABLE1' intersect select column_name from user_tab_cols where table_name='TABLE2'
Maybe you can give me some advices.
Thank you very much.
Sample data:
CREATE TABLE "TABLE1"
( "A" VARCHAR2(20 BYTE),
"B" VARCHAR2(20 BYTE),
"C" VARCHAR2(20 BYTE),
"D" VARCHAR2(20 BYTE),
"E" VARCHAR2(20 BYTE),
"F" VARCHAR2(20 BYTE)
);
CREATE TABLE "TABLE2"
( "C" VARCHAR2(20 BYTE),
"D" VARCHAR2(20 BYTE) DEFAULT 'Monday',
"E" VARCHAR2(20 BYTE) DEFAULT 'Tuesday',
"F" VARCHAR2(20 BYTE) DEFAULT 'Wednesday',
"G" VARCHAR2(20 BYTE) DEFAULT 'Thursday',
"H" VARCHAR2(20 BYTE)
);
INSERT INTO "TABLE1" (A, B, D, F) VALUES ('1', '2', 'somedata', '3');
INSERT INTO "TABLE1" (A, B, D, F) VALUES ('1', '2', 'somedata', '3');
Banner:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production