Re: "Re-sequencing" a pseudo-key
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- Date: Thu, 9 Oct 2014 22:55:18 +0200
Oh yes, this I will definitely make "meta-data driven".
I'll make one or more tables defining which tables to do the operation on,
which columns are affected, the "referential keys", which mviews depend on
the table, etc. Then I will write code that generates code from these
meta-data.
Trying to craft such a task by handwriting all code needed for 650 tables
would be almost certain to make errors ;-)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx
@kibeha
On Thu, Oct 9, 2014 at 10:10 PM, Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
wrote:
> Yes, lots of issues to consider--such as complexity, redo, risk, and
> downtime--before you pick the solution that works best for you and finalize
> the details.
>
> Automation will mitigate the complexity. For example, you could write a
> script to generate dynamic SQL.
>
> [oracle@localhost ~]$ cat script.sql
> set echo on
>
> create table test_table (recid integer not null primary key, ref_recid_1
> integer not null, ref_recid_2 integer not null);
>
> insert into test_table values (1, 2, 3);
> insert into test_table values (11, 12, 13);
>
> alter table test_table add (new_recid integer null, new_ref_recid_1
> integer null, new_ref_recid_2 integer null);
>
> select * from test_table;
>
> define lower_limit=1;
> define upper_limit=20;
>
> create table translation (recid integer primary key, new_recid integer not
> null);
>
> truncate table translation;
> insert into translation
> select recid, rownum from
> (
> select recid from test_table
> where recid between &&lower_limit and &&upper_limit
> union select ref_recid_1 from test_table
> where ref_recid_1 between &&lower_limit and &&upper_limit
> union select ref_recid_2 from test_table
> where ref_recid_2 between &&lower_limit and &&upper_limit
> order by 1
> );
>
> select * from translation;
>
> set serveroutput on size 1000000;
>
> declare
>
> procedure translate2(
> p_original in varchar2,
> p_primary_key in varchar2,
> p_col_X in varchar2,
> p_translated_col_X in varchar2,
> p_lower_limit in integer,
> p_upper_limit in integer
> ) is
>
> dml_command varchar2(2048);
>
> begin
>
> dml_command := 'update' ||
> ' ( ' ||
> ' select ' || p_original || '.' || p_primary_key || ' as pk, ' ||
> p_original || '.' || p_translated_col_X || ' as translated_col_X,
> translation.new_recid as new_recid' ||
> ' from ' || p_original || ', translation' ||
> ' where ' || p_original || '.' || p_col_X || ' between ' ||
> p_lower_limit || ' and ' || p_upper_limit ||
> ' and translation.recid between ' || p_lower_limit || ' and ' ||
> p_upper_limit ||
> ' and ' || p_original || '.' || p_col_X || ' = translation.recid'
> ||
> ' )' ||
> ' set translated_col_X = new_recid';
>
> dbms_output.put_line(dml_command);
>
> begin
> execute immediate dml_command;
> dbms_output.put_line(SQL%ROWCOUNT || ' row(s) updated');
> exception
> when others then dbms_output.put_line('sqlcode = ' || sqlcode ||
> ', sqlerrm = ' || sqlerrm);
> end;
>
> end;
>
> begin
>
> translate2('test_table', 'recid', 'recid', 'new_recid', 1, 10);
> translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1', 1,
> 10);
> translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2', 1,
> 10);
>
> translate2('test_table', 'recid', 'recid', 'new_recid', 11, 20);
> translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1', 11,
> 20);
> translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2', 11,
> 20);
>
> end;
> /
>
> select * from test_table;
> [oracle@localhost ~]$ sqlplus
>
> SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 9 13:03:06 2014
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
> Enter user-name: hr
> Enter password:
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> @script
> SQL>
> SQL> create table test_table (recid integer not null primary key,
> ref_recid_1 integer not null, ref_recid_2 integer not null);
>
> Table created.
>
> SQL>
> SQL> insert into test_table values (1, 2, 3);
>
> 1 row created.
>
> SQL> insert into test_table values (11, 12, 13);
>
> 1 row created.
>
> SQL>
> SQL> alter table test_table add (new_recid integer null, new_ref_recid_1
> integer null, new_ref_recid_2 integer null);
>
> Table altered.
>
> SQL>
> SQL> select * from test_table;
>
> RECID REF_RECID_1 REF_RECID_2 NEW_RECID NEW_REF_RECID_1
> NEW_REF_RECID_2
> ---------- ----------- ----------- ---------- ---------------
> ---------------
> 1 2 3
> 11 12 13
>
> SQL>
> SQL> define lower_limit=1;
> SQL> define upper_limit=20;
> SQL>
> SQL> create table translation (recid integer primary key, new_recid
> integer not null);
>
> Table created.
>
> SQL>
> SQL> truncate table translation;
>
> Table truncated.
>
> SQL> insert into translation
> 2 select recid, rownum from
> 3 (
> 4 select recid from test_table
> 5 where recid between &&lower_limit and &&upper_limit
> 6 union select ref_recid_1 from test_table
> 7 where ref_recid_1 between &&lower_limit and &&upper_limit
> 8 union select ref_recid_2 from test_table
> 9 where ref_recid_2 between &&lower_limit and &&upper_limit
> 10 order by 1
> 11 );
> old 5: where recid between &&lower_limit and &&upper_limit
> new 5: where recid between 1 and 20
> old 7: where ref_recid_1 between &&lower_limit and &&upper_limit
> new 7: where ref_recid_1 between 1 and 20
> old 9: where ref_recid_2 between &&lower_limit and &&upper_limit
> new 9: where ref_recid_2 between 1 and 20
>
> 6 rows created.
>
> SQL>
> SQL> select * from translation;
>
> RECID NEW_RECID
> ---------- ----------
> 1 1
> 2 2
> 3 3
> 11 4
> 12 5
> 13 6
>
> 6 rows selected.
>
> SQL>
> SQL> set serveroutput on size 1000000;
> SQL>
> SQL> declare
> 2
> 3 procedure translate2(
> 4 p_original in varchar2,
> 5 p_primary_key in varchar2,
> 6 p_col_X in varchar2,
> 7 p_translated_col_X in varchar2,
> 8 p_lower_limit in integer,
> 9 p_upper_limit in integer
> 10 ) is
> 11
> 12 dml_command varchar2(2048);
> 13
> 14 begin
> 15
> 16 dml_command := 'update' ||
> 17 ' ( ' ||
> 18 ' select ' || p_original || '.' || p_primary_key || ' as pk,
> ' || p_original || '.' || p_translated_col_X || ' as translated_col_X,
> translation.new_recid as new_recid' ||
> 19 ' from ' || p_original || ', translation' ||
> 20 ' where ' || p_original || '.' || p_col_X || ' between ' ||
> p_lower_limit || ' and ' || p_upper_limit ||
> 21 ' and translation.recid between ' || p_lower_limit || ' and '
> || p_upper_limit ||
> 22 ' and ' || p_original || '.' || p_col_X || ' =
> translation.recid' ||
> 23 ' )' ||
> 24 ' set translated_col_X = new_recid';
> 25
> 26 dbms_output.put_line(dml_command);
> 27
> 28 begin
> 29 execute immediate dml_command;
> 30 dbms_output.put_line(SQL%ROWCOUNT || ' row(s) updated');
> 31 exception
> 32 when others then dbms_output.put_line('sqlcode = ' ||
> sqlcode || ', sqlerrm = ' || sqlerrm);
> 33 end;
> 34
> 35 end;
> 36
> 37 begin
> 38
> 39 translate2('test_table', 'recid', 'recid', 'new_recid', 1, 10);
> 40 translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1',
> 1, 10);
> 41 translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2',
> 1, 10);
> 42
> 43 translate2('test_table', 'recid', 'recid', 'new_recid', 11, 20);
> 44 translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1',
> 11, 20);
> 45 translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2',
> 11, 20);
> 46
> 47 end;
> 48 /
> update ( select test_table.recid as pk, test_table.new_recid as
> translated_col_X, translation.new_recid as new_recid from test_table,
> translation where test_table.recid between 1 and 10 and
> translation.recid
> between 1 and 10 and test_table.recid = translation.recid ) set
> translated_col_X = new_recid
> 1 row(s) updated
> update ( select test_table.recid as pk, test_table.new_ref_recid_1 as
> translated_col_X, translation.new_recid as new_recid from test_table,
> translation where test_table.ref_recid_1 between 1 and 10 and
> translation.recid between 1 and 10 and test_table.ref_recid_1 =
> translation.recid ) set translated_col_X = new_recid
> 1 row(s) updated
> update ( select test_table.recid as pk, test_table.new_ref_recid_2 as
> translated_col_X, translation.new_recid as new_recid from test_table,
> translation where test_table.ref_recid_2 between 1 and 10 and
> translation.recid between 1 and 10 and test_table.ref_recid_2 =
> translation.recid ) set translated_col_X = new_recid
> 1 row(s) updated
> update ( select test_table.recid as pk, test_table.new_recid as
> translated_col_X, translation.new_recid as new_recid from test_table,
> translation where test_table.recid between 11 and 20 and
> translation.recid
> between 11 and 20 and test_table.recid = translation.recid ) set
> translated_col_X = new_recid
> 1 row(s) updated
> update ( select test_table.recid as pk, test_table.new_ref_recid_1 as
> translated_col_X, translation.new_recid as new_recid from test_table,
> translation where test_table.ref_recid_1 between 11 and 20 and
> translation.recid between 11 and 20 and test_table.ref_recid_1 =
> translation.recid ) set translated_col_X = new_recid
> 1 row(s) updated
> update ( select test_table.recid as pk, test_table.new_ref_recid_2 as
> translated_col_X, translation.new_recid as new_recid from test_table,
> translation where test_table.ref_recid_2 between 11 and 20 and
> translation.recid between 11 and 20 and test_table.ref_recid_2 =
> translation.recid ) set translated_col_X = new_recid
> 1 row(s) updated
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> select * from test_table;
>
> RECID REF_RECID_1 REF_RECID_2 NEW_RECID NEW_REF_RECID_1
> NEW_REF_RECID_2
> ---------- ----------- ----------- ---------- ---------------
> ---------------
> 1 2 3 1 2
> 3
> 11 12 13 4 5
> 6
>
> SQL> exit
> Disconnected from Oracle Database 11g Enterprise Edition Release
> 11.2.0.2.0 - Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
>
Other related posts:
