Like this:
-- ************************************************************************************
-- CSUPGRD-727 Michael Nitschke 31/05/2011
-- Correct situation where Students and Organisations have duplicate IDs.
-- This has occurred where users have overwritten the 'NEW' value when adding an Org.
-- ************************************************************************************
BEGIN
-- Build working tables for this process.
-- This one stores all the Records that will potentially be updated.
-- OCCURSCOUNT1 stores rows of data to be updated
-- OCCURSCOUNT2 stores rows of data to be updated *after* the fix has been run. Should be 0.
FOR i IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PS_AMS_DUPID_RECNM') LOOP
EXECUTE IMMEDIATE 'DROP TABLE PS_AMS_DUPID_RECNM';
END LOOP;
EXECUTE IMMEDIATE '
CREATE TABLE PS_AMS_DUPID_RECNM (
DESCR VARCHAR2(100) NOT NULL,
RECNAME VARCHAR2(15) NOT NULL,
FIELDNAME VARCHAR2(18) NOT NULL,
OCCURSCOUNT1 SMALLINT NOT NULL,
OCCURSCOUNT2 SMALLINT NOT NULL)
TABLESPACE SAAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80'
;
-- This one stores the old EXT_ORG_ID and the new EXT_ORG_ID for each Organisation that is having its ID updated.
-- May be useful if something unexpected happens.
FOR i IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PS_AMS_DUPID_OLDNW') LOOP
EXECUTE IMMEDIATE 'DROP TABLE PS_AMS_DUPID_OLDNW';
END LOOP;
EXECUTE IMMEDIATE '
CREATE TABLE PS_AMS_DUPID_OLDNW (
EXT_ORG_ID_OLD VARCHAR2(11) NOT NULL,
EXT_ORG_ID VARCHAR2(11) NOT NULL)
TABLESPACE SAAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80'
;
END;
/
-- This procedure takes a given Record and finds all Records that use it as a prompt, where that prompt field is not EXT_ORG_ID (as record would be picked up in base select already).
-- Note that this function is recursive. Use the maxDepth value to set how far down the tree to go.
-- Too high a value and you will run out of cursors.
CREATE OR REPLACE PROCEDURE FindWhereRecUsedAsPrompt(varRecname VARCHAR2, currentDepth integer) IS
CURSOR cRecPrompts IS
SELECT 'PROMPT' X, A.RECNAME AS RECNAME, A.FIELDNAME AS FIELDNAME
FROM
PSRECFIELD A
, PSRECDEFN B
, PSDBFIELD C
WHERE A.FIELDNAME <> 'EXT_ORG_ID'
AND A.EDITTABLE = varRecname
AND B.RECNAME = A.RECNAME
AND B.RECTYPE = 0 -- SQL Table
AND C.FIELDNAME = A.FIELDNAME
AND C.FIELDTYPE = 0; -- Character
depth integer;
maxDepth integer;
BEGIN
maxDepth:= 1; -- Set how deep you want to traverse down the prompt tables
depth:= currentDepth;
FOR recP in cRecPrompts
LOOP
EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_RECNM (DESCR, RECNAME, FIELDNAME, OCCURSCOUNT1, OCCURSCOUNT2) VALUES (''' || recP.x || '(depth: ' || depth || ', parent:' || varRecname || ')'' , ''' || recP.RECNAME || ''', ''' || recP.FIELDNAME || ''' ,0 ,0)';
IF depth < maxDepth THEN
FindWhereRecUsedAsPrompt(recP.RECNAME, depth+1);
END IF;
END LOOP;
depth:= depth-1;
END FindWhereRecUsedAsPrompt;
/
-- This procedure counts number of rows in affected tables.
-- It uses the temp table PS_AMS_DUPID_RECNM
-- OCCURSCOUNT1 stores the number of rows beofore the fix/update is applied.
-- OCCURSCOUNT2 stores the number of rows after the fix/update has been applied (should = 0).
CREATE OR REPLACE PROCEDURE analyseRowCount(fldUpdate VARCHAR2) IS
CURSOR cTableList IS
SELECT DISTINCT RECNAME, FIELDNAME, OCCURSCOUNT1 FROM PS_AMS_DUPID_RECNM
;
sqlStatement VARCHAR2(300);
rowCount INTEGER;
BEGIN
FOR rec in cTableList
LOOP
-- Two steps for legibility's sake.
BEGIN
If substr(rec.RECNAME, 1, 2) = 'PS'
THEN
sqlStatement:= 'SELECT COUNT(*) FROM ' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN (SELECT SUBSTR(EXT_ORG_ID,1,7) FROM PS_EXT_ORG_TBL B WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN (SELECT A.EMPLID FROM PS_PERSONAL_DATA A WHERE A.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))';
-- dbms_output.put_line('SELECT COUNT(*) FROM ' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN ');
ELSE
sqlStatement:= 'SELECT COUNT(*) FROM PS_' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN (SELECT SUBSTR(EXT_ORG_ID,1,7) FROM PS_EXT_ORG_TBL B WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN (SELECT A.EMPLID FROM PS_PERSONAL_DATA A WHERE A.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))';
-- dbms_output.put_line('SELECT COUNT(*) FROM PS_' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN ');
END IF;
EXECUTE IMMEDIATE sqlStatement INTO rowCount;
sqlStatement:= 'UPDATE PS_AMS_DUPID_RECNM SET ' || fldUpdate || ' = ' || rowCount || ' WHERE RECNAME = ''' || rec.RECNAME || ''' AND FIELDNAME = ''' || rec.FIELDNAME || '''';
EXECUTE IMMEDIATE sqlStatement;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error at analyseRowCount(): ' || sqlstatement);
END;
END LOOP;
END analyseRowCount;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- << MAIN PROGRAM. >>
DECLARE
-- Duplicate IDs.
CURSOR cDuplicateIDs IS
SELECT
EXT_ORG_ID
FROM PS_EXT_ORG_TBL A
WHERE A.EXT_ORG_ID IN
(SELECT B.EXT_ORG_ID
FROM PS_EXT_ORG_TBL B
WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN
(SELECT C.EMPLID
FROM PS_PERSONAL_DATA C
WHERE C.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))
--and rownum < 4 -- << LOOK!!! used for building.
;
-- Base tables.
-- Tables that have EXT_ORG_ID field.
CURSOR cBaseTables IS
SELECT
'Base' X
, A.RECNAME RECNAME
, A.FIELDNAME FIELDNAME
FROM
PSRECFIELD A
, PSRECDEFN B
WHERE A.FIELDNAME = 'EXT_ORG_ID'
AND B.RECNAME = A.RECNAME
AND B.RECTYPE = 0
AND B.RECNAME NOT LIKE '%TMP'
AND NOT EXISTS -- Not used as an audit record.
(SELECT 1
FROM PSRECDEFN
WHERE AUDITRECNAME = RECNAME)
;
CURSOR cTableList IS
SELECT DISTINCT RECNAME, FIELDNAME, OCCURSCOUNT1 FROM PS_AMS_DUPID_RECNM
;
sqlStatement VARCHAR2(300);
rowCount INTEGER;
newExtOrgId PS_EXT_ORG_TBL.EXT_ORG_ID%type;
duplicateID INTEGER;
BEGIN
-- Loop through the base tables that contain EXT_ORG_ID.
FOR rec in cBaseTables
LOOP
-- Insert into working table a list of both the base tables and any tables that use those base tables as a prompt.
-- These are the list of tables that potentially need to be updated.
EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_RECNM (DESCR, RECNAME, FIELDNAME, OCCURSCOUNT1, OCCURSCOUNT2) VALUES (''' || rec.x || ''', ''' || rec.RECNAME || ''', ''' || rec.FIELDNAME || ''',0 ,0)';
FindWhereRecUsedAsPrompt(rec.RECNAME, 1);
END LOOP;
-- Now we have a list of tables built we'll analyse how many rows we expect to update.
-- PS_AMS_DUPID_RECNM.OCCURSCOUNT1 stores the number of rows that will be updated.
analyseRowCount('OCCURSCOUNT1');
-- Update the duplicate IDs (this is the really exciting part).
For rec in cDuplicateIDs
LOOP
-- Create a new ID.
duplicateID := 1;
WHILE duplicateID <> 0
LOOP
SELECT EMPLID_LAST_EMPL + 1 INTO newExtOrgId FROM PS_INSTALLATION;
EXECUTE IMMEDIATE 'SELECT count(*) FROM PS_PERSONAL_DATA WHERE EMPLID = ''' || newExtOrgId || '''' INTO duplicateID;
IF duplicateID = 0
THEN
EXECUTE IMMEDIATE 'SELECT count(*) FROM PS_EXT_ORG_TBL WHERE EXT_ORG_ID = ''' || newExtOrgId || '''' INTO duplicateID;
--dbms_output.put_line('duplicateID= ' || duplicateID || ', newExtOrgId= ' || newExtOrgId);
END IF;
END LOOP;
UPDATE PS_INSTALLATION SET EMPLID_LAST_EMPL = newExtOrgId;
-- Update the old value to the new value in every record that has at least one affected row.
FOR recUpd in cTableList
LOOP
If recUpd.OCCURSCOUNT1 > 0 THEN
If substr(recUpd.RECNAME, 1, 2) = 'PS'
THEN
sqlStatement:= 'UPDATE ' || recUpd.RECNAME || ' SET ' || recUpd.FIELDNAME || ' = ''' || newExtOrgId || ''' WHERE ' || recUpd.FIELDNAME || ' = ''' || rec.EXT_ORG_ID || '''';
ELSE
sqlStatement:= 'UPDATE PS_' || recUpd.RECNAME || ' SET ' || recUpd.FIELDNAME || ' = ''' || newExtOrgId || ''' WHERE ' || recUpd.FIELDNAME || ' = ''' || rec.EXT_ORG_ID || '''';
END IF;
BEGIN
EXECUTE IMMEDIATE sqlStatement;
--dbms_output.put_line(sqlStatement);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error at (main program): ' || sqlstatement);
END;
END IF;
END LOOP;
-- Record the old and new values.
EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_OLDNW (EXT_ORG_ID_OLD, EXT_ORG_ID) VALUES (''' || rec.EXT_ORG_ID || ''', ''' || newExtOrgId || ''')';
END LOOP;
-- Analyse the number of rows again. We expect 0.
-- OCCURSCOUNT2 stores the number of rows post update/fix. Should be 0.
analyseRowCount('OCCURSCOUNT2');
END;
/
-- Show the analysis/working tables.
select * from PS_AMS_DUPID_OLDNW;
SELECT * FROM PS_AMS_DUPID_RECNM where occurscount1 > 0;
Posts
