A picture is worth 1K words...
Here is an example script of what you can do:
Code:
DROP TABLE T1;
CREATE TABLE T1(
PK NUMBER,
F0 NUMBER,
F1 NUMBER,
F2 NUMBER,
F3 NUMBER,
F4 NUMBER,
F5 NUMBER,
F6 NUMBER,
F7 NUMBER,
F8 NUMBER,
F9 NUMBER
);
INSERT INTO T1 VALUES(100, 1, 2, 3, 4, 5, 6, 7, 8, 9,10);
INSERT INTO T1 VALUES(101,11,12,13,14,15,16,17,18,19,20);
INSERT INTO T1 VALUES(102,21,22,23,24,25,26,27,28,29,30);
INSERT INTO T1 VALUES(103,31,32,33,34,35,36,37,38,39,40);
INSERT INTO T1 VALUES(104,41,42,43,44,45,46,47,48,49,50);
INSERT INTO T1 VALUES(105,51,52,53,54,55,56,57,58,59,60);
INSERT INTO T1 VALUES(106,61,62,63,64,65,66,67,68,69,70);
INSERT INTO T1 VALUES(107,71,72,73,74,75,76,77,78,79,80);
COMMIT;
DROP TABLE T2;
SET pages 0 trims on term off ver off feed off echo off embed on recsep off head off
SPO cr_t2.sql
SELECT DECODE (column_id,
1, 'CREATE TABLE T2 ('|| CHR (10)
|| ' '|| column_name || ' NUMBER' || CHR (10),
','|| column_name || ' VARCHAR2(16)' || CHR (10)
)
FROM user_tab_columns
WHERE table_name = 'T1'
ORDER BY column_id;
PROMPT );
PROMPT /
SPO off
SET pages 99 term on ver on feed on echo on head on
@cr_t2
INSERT INTO T2 (PK) SELECT PK FROM T1;
COMMIT;
CREATE OR REPLACE FUNCTION T1_CHG (P_VAL NUMBER)
RETURN VARCHAR2 IS
BEGIN
-- Your transformation here --
RETURN p_val||','||p_val||','||p_val;
END;
/
SET pages 0 trims on term off ver off feed off echo off embed on recsep off head off
SPO up_t2.sql
SELECT 'UPDATE T2 SET '|| column_name || ' = (SELECT T1_CHG('||column_name
||') FROM T1 WHERE T1.PK = T2.PK);'
FROM user_tab_columns
WHERE table_name = 'T1' and column_id > 1
ORDER BY column_id;
SPO off
SET pages 99 term on ver on feed on echo on head on
@up_t2
;)