·
Take a backup of existing database table using
below query
o create
table locancestor_bkp as (select * from locancestor);
·
Create a new procedure like this below and
execute it.
*******************************************************************
create or replace procedure BUILD_LOCANCESTOR
as
MODULE_NAME varchar2(50) := 'LOCATIONS';
SCRIPT_NAME varchar2(50) := 'BUILD_LOCANCESTOR.sql';
TABLE_NAME varchar2(50) :=
'LOCANCESTOR';
ERROR_COUNT
number := 0; --Track no of errors
REC_COUNTER number := 0; --Track no of loaded records
R number := 200;
--Number of records to commit
V_MAX_ID number;
cursor LOCATIONS_CUR is
select L.LOCATION, L.ORGID, L.SITEID, L.SYSTEMID from LOCHIERARCHY L;
FIX_LOCATION LOCATIONS.LOCATION%type;
FIX_SITEID
LOCATIONS.SITEID%type;
/*These two variables are to hold the LOCATION and SITEID of the
location
for
which we are building the hierarchy (the entries in LOCANCESTOR table)*/
X_LOCATION LOCATIONS.LOCATION%type;
X_SITEID LOCATIONS.SITEID%type;
/*These two variables are to hold the LOCATION and SITEID of the current
location in the loop*/
X_ANCESTOR_LOCATION LOCATIONS.LOCATION%type;
X_ANCESTOR_SITEID
LOCATIONS.SITEID%type;
/*These two variables are to hold the LOCATION and SITEID of the
ancestor
of
the current location in the loop*/
begin
execute immediate ('TRUNCATE TABLE ' || TABLE_NAME);
execute immediate ('ALTER TABLE ' || TABLE_NAME || ' NOLOGGING');
--RESET_SEQUENCE(TABLE_NAME);
for
LC in LOCATIONS_CUR loop
declare
FIX_SYSTEMID LOCHIERARCHY.SYSTEMID%type;
begin
FIX_LOCATION := LC.LOCATION;
FIX_SITEID := LC.SITEID;
--These two variables will be fixed all the inner loop
FIX_SYSTEMID := LC.SYSTEMID;
X_LOCATION := LC.LOCATION;
X_SITEID := LC.SITEID;
--These two variables will be changed for each iteration of the inner
loop
loop
--Inner loop to build all LOCANCESTOR entries for the current location
(FIX_LOCATION, FIX_SITEID)
REC_COUNTER := REC_COUNTER + 1;
insert /*+ APPEND */
into LOCANCESTOR
(LOCATION, ANCESTOR, SYSTEMID,
SITEID, ORGID, LOCANCESTORID)
values
(FIX_LOCATION, --LOCATION
X_LOCATION, --ANCESTOR
FIX_SYSTEMID, --SYSTEMID
LC.SITEID, --SITEID
LC.ORGID, --ORGID
LOCANCESTORSEQ.NEXTVAL --LOCANCESTORID
);
select max(H.PARENT), max(H.SITEID)
into X_ANCESTOR_LOCATION, X_ANCESTOR_SITEID
from LOCHIERARCHY H
where H.LOCATION = X_LOCATION
and H.SITEID = X_SITEID
and H.SYSTEMID = FIX_SYSTEMID;
if (X_ANCESTOR_LOCATION is null) then
/*If there is no parent of the current location,
so it's the top level one and we have to exit the inner loop*/
exit;
else
X_LOCATION := X_ANCESTOR_LOCATION;
X_SITEID := X_ANCESTOR_SITEID;
end if;
end loop; --End the inner loop
exception
WHEN DUP_VAL_ON_INDEX THEN
null;
when others then
ERROR_COUNT := ERROR_COUNT + 1;
end;
commit;
end
loop;
commit;
execute immediate ('ANALYZE TABLE ' || TABLE_NAME ||
' COMPUTE STATISTICS');
execute immediate ('ALTER TABLE ' || TABLE_NAME || ' LOGGING');
DBMS_OUTPUT.PUT_LINE(TABLE_NAME || ' errors = ' || ERROR_COUNT);
end BUILD_LOCANCESTOR;
/
************************************************************
----------------------------------------------------------------------------
All the messagesbelow are just forwarded messages if some one feels hurt about it please add your comments we will remove the post.Host/author is not responsible for these posts.
