DB Wiki

User Tools

Site Tools

Trace:
sequence10

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
sequence10 [2016/05/29 10:11] – created z0hpvksequence10 [2025/03/08 22:24] (current) – external edit 127.0.0.1
Line 1: Line 1:
-<code>-- 10g Package +==== PL/SQL Code for 10g Database ==== 
-create or replace package MOL_SEQUENCES+ 
 +This package should be installed as the GGATE user on the 10g Database (Source)\\ 
 +Before installing you need to create the database link and tables from [[sequenceother|here]]\\ 
 +\\ 
 +Function MOL_SEQUENCE_CHECK_LINK contains a database link that may need to be renamed. 
 + 
 +<code>create or replace package MOL_SEQUENCES
 AUTHID CURRENT_USER AUTHID CURRENT_USER
 as as
Line 13: Line 19:
 create or replace package body MOL_SEQUENCES as create or replace package body MOL_SEQUENCES as
  
--- Check Sequence Value on 10g Database 
 FUNCTION mol_sequence_check (seq_owner in varchar2, seq_name in varchar2) FUNCTION mol_sequence_check (seq_owner in varchar2, seq_name in varchar2)
 RETURN NUMBER RETURN NUMBER
Line 30: Line 35:
 end mol_sequence_check; end mol_sequence_check;
  
--- Check Sequence Value on 12c Database 
 FUNCTION mol_sequence_check_link (seq_owner IN varchar2, seq_name IN varchar2) FUNCTION mol_sequence_check_link (seq_owner IN varchar2, seq_name IN varchar2)
 RETURN NUMBER RETURN NUMBER
Line 37: Line 41:
 begin begin
 select last_number into seq_number select last_number into seq_number
-from all_sequences@ggpoc+from all_sequences@ggmolbb
 where sequence_owner = upper(seq_owner)  where sequence_owner = upper(seq_owner) 
 and sequence_name = upper(seq_name); and sequence_name = upper(seq_name);
Line 47: Line 51:
 end mol_sequence_check_link; end mol_sequence_check_link;
  
--- Find difference in values between 10g and 12c. If greater than sync_value then move sequence value closer 
 PROCEDURE mol_sequence_sync (seq_owner IN varchar2, seq_name IN varchar2, sync_value IN number) PROCEDURE mol_sequence_sync (seq_owner IN varchar2, seq_name IN varchar2, sync_value IN number)
 AS AS
Line 54: Line 57:
 BEGIN BEGIN
 -- Get difference between both sequence values -- Get difference between both sequence values
-select seq_check_link(seq_owner, seq_name) - seq_check(seq_owner, seq_name)+select mol_sequence_check_link(seq_owner, seq_name) - mol_sequence_check(seq_owner, seq_name)
 into v_seq_diff into v_seq_diff
 from dual; from dual;
Line 68: Line 71:
 END mol_sequence_sync; END mol_sequence_sync;
  
--- Create Sequence Value table for named schema 
 PROCEDURE mol_seqoddeven (seq_owner IN varchar2) PROCEDURE mol_seqoddeven (seq_owner IN varchar2)
 AS AS
Line 83: Line 85:
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by 2';   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by 2';
   execute immediate 'select ' || seq_owner || '.' || seq_name || '.nextval  from dual' into seq_val;   execute immediate 'select ' || seq_owner || '.' || seq_name || '.nextval  from dual' into seq_val;
-  insert into mol_gg_sequences (seq_owner, seq_name, valueint) values (upper(seq_owner), seq_name, seq_val);+  insert into mol_gg_sequences (owner, sequence_name, valueint) values (upper(seq_owner), seq_name, seq_val);
   commit;   commit;
 END LOOP; END LOOP;
Line 89: Line 91:
 END mol_seqoddeven; END mol_seqoddeven;
  
--- Reset all sequences back to increment 1 
 PROCEDURE mol_inc1 (seq_owner IN varchar2) PROCEDURE mol_inc1 (seq_owner IN varchar2)
 AS AS
sequence10.1464516704.txt.gz · Last modified: (external edit)