CREATE OR REPLACE PROCEDURE Arc2Trezor
/*
Version: 1.1
Date : 28.2.2006
(c) IPESOFT 1993, 2006. All rights reserved.
Usage:
SET SERVEROUTPUT ON <- turn on writing lines
SET LINE 260 <- set line width
SPOOL c:\Arc2Trezor_out.sql <- set output file
EXEC Arc2Trezor; <- execute this procedure
SPOOL OFF <- close output file
and continue according to instructions in output file..
*/
AS
/* -----------------------------------------------------------------*/
/* Start of configuration. Set following parameters */
TrezorId NUMBER := 1; /* ID of trezor (1,2,3 etc) */
AppName VARCHAR(20) := 'MYAPP'; /* Name of application */
/* Following parameters are described in D2000 manual */
TrezorPath VARCHAR(80) := '/opt/ora9/oradata/D2000/'; /* Path for trezor datafiles */
TrezorCountDatafiles NUMBER := 1; /* Number of trezor datafiles */
TrezorStartDatafileSize VARCHAR(10) := '10M'; /* Initial size of datafile ('10M','3000K' etc) */
TrezorMaxDatafileSize VARCHAR(10) := '2000M'; /* Maximum size of datafile (or '' if not extendible) */
TrezorDatafileSizeStep VARCHAR(10) := '5M'; /* Increment of datafile (or '' if db default ) */
/* Period for trezors in UTC time (time in archive is UTC+2hours) */
StartDateUTC DATE := TO_DATE('2006-01-01 10:00:00','YYYY-MM-DD HH24:MI:SS');
EndDateUTC DATE := TO_DATE('2006-02-01 10:00:00','YYYY-MM-DD HH24:MI:SS');
/* If trezor tablespaces are the same database as archive, do not modify anything */
/* after this line. Otherwise continue: */
/* if trezor tablespaces are in separate database, set following constant to FALSE */
TrezorsInArchiveDatabase BOOLEAN := TRUE;
/* logon info (user/password@tns_name) for archive database */
ArchivDBLogonInfo VARCHAR (80) := 'MYAPP_ARCHIV/swx@D2000';
/* logon info (user/password@tns_name) for trezor database */
TrezorDBLogonInfo VARCHAR (80) := 'MYAPP_ARCHIV/swx@D2000TRZ';
/* OS type where sqlplus runs: Windows=TRUE,Unix=FALSE */
OSTypeIsWindows BOOLEAN := TRUE;
/* Parallel LOAD/UNLOAD: if TRUE, export of N-th table runs */
/* concurrently with import of (N+1)-th table. Otherwise all data */
/* is exported and afterwards imported */
ParallelLoading BOOLEAN := FALSE;
/* End of configuration. Do not modify anything after this line !!! */
/* -----------------------------------------------------------------*/
AppNameU VARCHAR(20) := Upper(AppName);
CommentStr VARCHAR (8); /* REM or # */
BackgroundStart VARCHAR (8); /* START or '' */
BackgroundEnd VARCHAR (8); /* '' or & */
TrezorTablespace VARCHAR(80) := AppNameU || '_TS_TREZOR' || TrezorId;
TrezorStartDatafileName VARCHAR(200);
DataN VARCHAR (10) := 'DATA' || TrezorId;
PROCEDURE CreateTargetTablespace IS
AppNamePadded CHAR (27) := AppNameU || '_ARCHIV';
BEGIN
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */');
IF TrezorsInArchiveDatabase THEN
DBMS_OUTPUT.PUT_LINE('/* Execute following commands in a database */');
ELSE
DBMS_OUTPUT.PUT_LINE('/* Start of CreateTrezor.sql */');
DBMS_OUTPUT.PUT_LINE('/* Execute following commands in target database */');
END IF;
DBMS_OUTPUT.PUT_LINE('/* where trezor is to be located */');
DBMS_OUTPUT.PUT_LINE('/* Execute them as user ' || AppNamePadded || '*/');
DBMS_OUTPUT.PUT_LINE('/* who must be granted CONNECT and DBA roles */');
DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* create tablespace ' || TrezorTablespace || ' */');
DBMS_OUTPUT.PUT_LINE('CREATE TABLESPACE "' || TrezorTablespace || '" LOGGING DATAFILE ');
FOR i IN 1 .. TrezorCountDatafiles LOOP
DBMS_OUTPUT.PUT( '''' || TrezorPath || TrezorTablespace);
IF TrezorCountDatafiles>1 THEN
DBMS_OUTPUT.PUT( '_' || i);
END IF;
DBMS_OUTPUT.PUT('.ORA'' SIZE ' || TrezorStartDatafileSize);
IF Length(TRIM(TrezorMaxDatafileSize)) <> 0 THEN
DBMS_OUTPUT.PUT(' AUTOEXTEND ON');
IF LENGTH(TRIM(TrezorDatafileSizeStep)) <>0 THEN
DBMS_OUTPUT.PUT(' NEXT ' || TrezorDatafileSizeStep);
END IF;
DBMS_OUTPUT.PUT(' MAXSIZE ' || TrezorMaxDatafileSize);
END IF;
IF i <> TrezorCountDatafiles THEN
DBMS_OUTPUT.PUT_LINE(', ');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* create table TABVER' || TrezorId || ' */');
DBMS_OUTPUT.PUT_LINE('CREATE TABLE TABVER' || TrezorId ||
'("NAME" CHAR(20 Byte) NOT NULL,' ||
' "VERSION" SMALLINT NOT NULL,' ||
' "PREV_VER" SMALLINT NOT NULL)' ||
' TABLESPACE '|| TrezorTablespace || ';');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* create table ' || DataN || ' */');
DBMS_OUTPUT.PUT_LINE('CREATE TABLE ' || DataN);
DBMS_OUTPUT.PUT_LINE('("ID" INTEGER NOT NULL,');
DBMS_OUTPUT.PUT_LINE(' "CAS" TIMESTAMP NOT NULL,');
DBMS_OUTPUT.PUT_LINE(' "VALUE" FLOAT NOT NULL,');
DBMS_OUTPUT.PUT_LINE(' "FLAGS" SMALLINT,');
DBMS_OUTPUT.PUT_LINE(' "STATUS" INTEGER,');
DBMS_OUTPUT.PUT_LINE(' "ARCHIV_STATUS" SMALLINT,');
DBMS_OUTPUT.PUT_LINE(' "LIMIT_STATUS" SMALLINT,');
DBMS_OUTPUT.PUT_LINE(' "ROW" INTEGER,');
DBMS_OUTPUT.PUT_LINE(' "COL" INTEGER,');
DBMS_OUTPUT.PUT_LINE(' CONSTRAINT TrDtIdx' || TrezorId || ' PRIMARY KEY ("ID","COL","ROW","CAS"))');
DBMS_OUTPUT.PUT_LINE(' ORGANIZATION INDEX TABLESPACE ' || TrezorTablespace || ';');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* set TABVER for ' || DataN || ' table */');
DBMS_OUTPUT.PUT_LINE(' INSERT INTO TABVER' || TrezorId ||
'("NAME","VERSION","PREV_VER") VALUES ' || '( ''DATA'',1,0);');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* create table ARCHIV_DEF' || TrezorId || ' */');
DBMS_OUTPUT.PUT_LINE('CREATE TABLE ARCHIV_DEF' || TrezorId);
DBMS_OUTPUT.PUT_LINE('("ID" INTEGER NOT NULL,');
DBMS_OUTPUT.PUT_LINE('"NAME" CHAR(65 byte) NOT NULL,');
DBMS_OUTPUT.PUT_LINE('"DESCRIPT" CHAR(129 byte),');
DBMS_OUTPUT.PUT_LINE('"VALUE_TYPE" SMALLINT NOT NULL,');
DBMS_OUTPUT.PUT_LINE('"TECH_UNIT" CHAR(13 byte),');
DBMS_OUTPUT.PUT_LINE('"SOURCE_NAME" CHAR(65 byte),');
DBMS_OUTPUT.PUT_LINE('"SOURCE_DESCRIPT" CHAR(129 byte),');
DBMS_OUTPUT.PUT_LINE('"ARCHIV" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"DEEP_TIME" INTEGER NOT NULL,');
DBMS_OUTPUT.PUT_LINE('"PERIOD" INTEGER,');
DBMS_OUTPUT.PUT_LINE('"OFFSET" INTEGER,');
DBMS_OUTPUT.PUT_LINE('"START_INT" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"WRITE_METHOD" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"FILTER_VALUE" FLOAT,');
DBMS_OUTPUT.PUT_LINE('"HIGH_LIMIT" FLOAT,');
DBMS_OUTPUT.PUT_LINE('"HIGH_FILTER" FLOAT,');
DBMS_OUTPUT.PUT_LINE('"LOW_LIMIT" FLOAT,');
DBMS_OUTPUT.PUT_LINE('"LOW_FILTER" FLOAT,');
DBMS_OUTPUT.PUT_LINE('"FUNCTION" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"VALID_PERC" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"K_INT" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"WRITE_DATA" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"ACTIVE_OBJ" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"TREZOR" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"SOURCE_ROW" SMALLINT,');
DBMS_OUTPUT.PUT_LINE('"SOURCE_COL" SMALLINT)');
DBMS_OUTPUT.PUT_LINE(' TABLESPACE ' || TrezorTablespace || ';');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* set TABVER for ARCHIV_DEF' || TrezorId || ' table */');
DBMS_OUTPUT.PUT_LINE(' INSERT INTO TABVER' || TrezorId ||
'("NAME","VERSION","PREV_VER") VALUES ' || '( ''ARCHIV_DEF'',1,0);');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* create temporary procedure to create TREZORS table (if it does not exist) */');
DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE PROCEDURE D2CreateAndFillTrezorsTable AS');
DBMS_OUTPUT.PUT_LINE(' tblTrezors NUMBER;');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE(' SELECT COUNT(*) INTO tblTrezors FROM TABVER WHERE NAME=''TREZORS'';');
DBMS_OUTPUT.PUT_LINE(' IF tblTrezors = 0 THEN');
DBMS_OUTPUT.PUT_LINE(' EXECUTE IMMEDIATE ''CREATE TABLE TREZORS ( "ID" INTEGER NOT NULL, "TIME_FROM" TIMESTAMP NOT NULL,');
DBMS_OUTPUT.PUT_LINE(' "TIME_TO" TIMESTAMP NOT NULL, STATUS INTEGER NOT NULL, DATAFILES INTEGER NOT NULL)'';');
DBMS_OUTPUT.PUT_LINE(' EXECUTE IMMEDIATE ''INSERT INTO TABVER(NAME,VERSION,PREV_VER) VALUES (''''TREZORS'''',1,0)'';');
DBMS_OUTPUT.PUT_LINE(' END IF;');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE(' EXEC D2CreateAndFillTrezorsTable;');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('DROP PROCEDURE D2CreateAndFillTrezorsTable;');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* make record in Trezors table, mark status of trezor as "read only" */');
DBMS_OUTPUT.PUT_LINE('INSERT INTO TREZORS(ID,TIME_FROM,TIME_TO,STATUS,DATAFILES) VALUES ('|| TrezorId);
DBMS_OUTPUT.PUT_LINE(',TO_DATE(''' || TO_CHAR(StartDateUTC,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'')');
DBMS_OUTPUT.PUT_LINE(',TO_DATE(''' || TO_CHAR(EndDateUTC ,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS''),' || '40,' || TrezorCountDatafiles || ');');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('commit;');
DBMS_OUTPUT.PUT_LINE(chr(0));
END CreateTargetTablespace;
PROCEDURE CopyArchivDefToTrezor IS
BEGIN
IF TrezorsInArchiveDatabase THEN
DBMS_OUTPUT.PUT_LINE('/* Copy ArchivDef to ArchivDefN*/');
DBMS_OUTPUT.PUT_LINE('INSERT INTO ARCHIV_DEF' || TrezorId || '(');
DBMS_OUTPUT.PUT_LINE('ID,NAME,DESCRIPT,VALUE_TYPE,TECH_UNIT,SOURCE_NAME,');
DBMS_OUTPUT.PUT_LINE('SOURCE_DESCRIPT,ARCHIV,DEEP_TIME,PERIOD,OFFSET,START_INT,');
DBMS_OUTPUT.PUT_LINE('WRITE_METHOD,FILTER_VALUE,HIGH_LIMIT,HIGH_FILTER,LOW_LIMIT,');
DBMS_OUTPUT.PUT_LINE('LOW_FILTER,FUNCTION,VALID_PERC,K_INT,WRITE_DATA,');
DBMS_OUTPUT.PUT_LINE('ACTIVE_OBJ,TREZOR,SOURCE_ROW,SOURCE_COL) SELECT ');
DBMS_OUTPUT.PUT_LINE('A.ID,A.NAME,A.DESCRIPT,A.VALUE_TYPE,A.TECH_UNIT,O.NAME,');
DBMS_OUTPUT.PUT_LINE('O.DESCRIPT,A.ARCHIV,A.DEEP_TIME,A.PERIOD,A.OFFSET,A.START_INT,');
DBMS_OUTPUT.PUT_LINE('A.WRITE_METHOD,A.FILTER_VALUE,A.HIGH_LIMIT,A.HIGH_FILTER,A.LOW_LIMIT,');
DBMS_OUTPUT.PUT_LINE('A.LOW_FILTER,A.FUNCTION,A.VALID_PERC,A.K_INT,A.WRITE_DATA,');
DBMS_OUTPUT.PUT_LINE('A.ACTIVE_OBJ,A.Save_Data,A.SOURCE_ROW,A.SOURCE_COL FROM ');
DBMS_OUTPUT.PUT_LINE('ARCHIV_DEF A LEFT JOIN OBJLIST O ON A.SOURCE_OBJ=O.ID;');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE(chr(0));
ELSE
DECLARE
CURSOR ASrc IS SELECT A.ID,A.NAME,A.DESCRIPT,A.VALUE_TYPE,A.TECH_UNIT,O.NAME AS SOURCE_NAME,
O.DESCRIPT AS SOURCE_DESCRIPT,A.ARCHIV,A.DEEP_TIME,A.PERIOD,A.OFFSET,A.START_INT,
A.WRITE_METHOD,A.FILTER_VALUE,A.HIGH_LIMIT,A.HIGH_FILTER,A.LOW_LIMIT,
A.LOW_FILTER,A.FUNCTION,A.VALID_PERC,A.K_INT,A.WRITE_DATA,
A.ACTIVE_OBJ,A.Save_Data,A.SOURCE_ROW,A.SOURCE_COL FROM
ARCHIV_DEF A LEFT JOIN OBJLIST O ON A.SOURCE_OBJ=O.ID;
ARec ASrc%ROWTYPE;
BEGIN
OPEN ASrc;
LOOP
FETCH ASrc into ARec;
EXIT WHEN ASrc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('INSERT INTO ARCHIV_DEF' || TrezorId || '(');
DBMS_OUTPUT.PUT_LINE('ID,NAME,DESCRIPT,VALUE_TYPE,TECH_UNIT,SOURCE_NAME,');
DBMS_OUTPUT.PUT_LINE('SOURCE_DESCRIPT,ARCHIV,DEEP_TIME,PERIOD,OFFSET,START_INT,');
DBMS_OUTPUT.PUT_LINE('WRITE_METHOD,FILTER_VALUE,HIGH_LIMIT,HIGH_FILTER,LOW_LIMIT,');
DBMS_OUTPUT.PUT_LINE('LOW_FILTER,FUNCTION,VALID_PERC,K_INT,WRITE_DATA,');
DBMS_OUTPUT.PUT_LINE('ACTIVE_OBJ,TREZOR,SOURCE_ROW,SOURCE_COL) VALUES(');
DBMS_OUTPUT.PUT_LINE(ARec.ID || ',''' || TRIM(ARec.NAME) || ''',''');
DBMS_OUTPUT.PUT_LINE(TRIM(ARec.DESCRIPT) || ''',');
DBMS_OUTPUT.PUT_LINE(ARec.VALUE_TYPE || ',''' || TRIM(ARec.TECH_UNIT) || ''',''' || TRIM(ARec.SOURCE_NAME) || ''',''');
DBMS_OUTPUT.PUT_LINE(TRIM(ARec.SOURCE_DESCRIPT) || ''',' || ARec.ARCHIV || ',' || ARec.DEEP_TIME || ',' );
DBMS_OUTPUT.PUT_LINE(ARec.PERIOD || ',' || ARec.OFFSET || ',' || ARec.START_INT || ',');
DBMS_OUTPUT.PUT_LINE(ARec.WRITE_METHOD || ',' || ARec.FILTER_VALUE || ',' || ARec.HIGH_LIMIT || ',');
DBMS_OUTPUT.PUT_LINE(ARec.HIGH_FILTER || ',' || ARec.LOW_LIMIT || ',');
DBMS_OUTPUT.PUT_LINE(ARec.LOW_FILTER || ',' || ARec."FUNCTION" || ',' || ARec.VALID_PERC || ',');
DBMS_OUTPUT.PUT_LINE(ARec.K_INT || ',' || ARec.WRITE_DATA || ',' || ARec.ACTIVE_OBJ || ',');
DBMS_OUTPUT.PUT_LINE(ARec.Save_Data || ',' || ARec.SOURCE_ROW || ',' || ARec.SOURCE_COL || ');');
DBMS_OUTPUT.PUT_LINE(chr(0));
END LOOP;
CLOSE ASrc;
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE('/* End of commands for target database */');
DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE('/* End of CreateTrezor.sql */');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE(chr(0));
END;
END IF;
END CopyArchivDefToTrezor;
PROCEDURE CopyDataToTrezor IS
tbl VARCHAR(20);
AD_ID ARCHIV_DEF.ID%Type;
AD_Name ARCHIV_DEF.NAME%Type;
Cnt_Row ARCHIV_DEF.ROWS_NR%Type;
Cnt_Col ARCHIV_DEF.COLS_NR%Type;
CURSOR CurObjList IS SELECT ID, NAME, ROWS_NR, COLS_NR, 'DT' || LTRIM(TO_CHAR(ID,'0000000')) ||
DECODE(ROWS_NR,0,'',DECODE(COLS_NR,0,'R','RC')) AS TblName FROM ARCHIV_DEF ORDER BY ID;
TblFound BOOLEAN;
TblTtypeTxt VARCHAR(4);
TblName VARCHAR(40);
UTCOffset NUMBER; /* UTC offset: data in Oracle archive are stored in UTC time + UTCOffset */
StartDateLOC DATE;
EndDateLOC DATE;
PROCEDURE MyImp(R IN NUMBER, C IN NUMBER) IS
BEGIN
IF ParallelLoading THEN
DBMS_OUTPUT.PUT(BackgroundStart);
END IF;
DBMS_OUTPUT.PUT('sqlldr userid=' || TrezorDBLogonInfo || ' ' || 'control=' || TblName || '_' || R || '_' || C || '.txt');
IF ParallelLoading THEN
DBMS_OUTPUT.PUT(BackgroundEnd);
END IF;
DBMS_OUTPUT.PUT_LINE(chr(0));
END MyImp;
PROCEDURE MyExp(R IN NUMBER, C IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('sqlplus -s ' || ArchivDBLogonInfo || ' @d2expdt '|| AD_ID || ' ' || R || ' ' || C || ' ' ||
TblName || ' ' || DataN || ' "' || StartDateLOC || '" "' || EndDateLOC || '" ' || UTCOffset || ' > ' || TblName || '_' || R || '_' || C || '.txt');
IF ParallelLoading THEN
MyImp(R,C);
END IF;
DBMS_OUTPUT.PUT_LINE(chr(0));
END MyExp;
PROCEDURE MyExpImp(R IN NUMBER, C IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('INSERT INTO ' || DataN || '(ID,"ROW",COL,CAS,VALUE,FLAGS,STATUS,LIMIT_STATUS,ARCHIV_STATUS)');
DBMS_OUTPUT.PUT_LINE(' SELECT ' || AD_ID || ',' || R || ',' || C || ',CAS-numtodsinterval('|| UTCOffset ||',''SECOND''),VALUE,FLAGS,');
DBMS_OUTPUT.PUT_LINE(' BITAND(STATUS,4095), BITAND(STATUS,61440)/4096, BITAND(DECODE(SIGN(STATUS),-1,4294967296+STATUS,STATUS), 4294901760)/65536');
DBMS_OUTPUT.PUT_LINE(' FROM ' || TblName || ' WHERE');
IF C<> 0 THEN
DBMS_OUTPUT.PUT(' COL=' || C || ' AND ');
END IF;
IF R <> 0 THEN
DBMS_OUTPUT.PUT(' "ROWX"=' || R || ' AND ');
END IF;
DBMS_OUTPUT.PUT_LINE(' CAS>= TO_DATE(''' || TO_CHAR(StartDateLOC,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') AND');
DBMS_OUTPUT.PUT_LINE(' CAS<= TO_DATE(''' || TO_CHAR(EndDateLOC ,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'');');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE(chr(0));
END MyExpImp;
BEGIN
SELECT OFFSET INTO UTCOffset FROM UTC_OFFSET;
StartDateLOC := StartDateUTC + UTCOffset/(3600*24);
EndDateLOC := EndDateUTC + UTCOffset/(3600*24);
DBMS_OUTPUT.PUT_LINE('/* UTC offset in archive is: ' || UTCOffset || ' [sec] */');
DBMS_OUTPUT.PUT_LINE('/* Start UTC time : ' || StartDateUTC || ' -> local time:' || StartDateLOC || ' */' );
DBMS_OUTPUT.PUT_LINE('/* End UTC time : ' || EndDateUTC || ' -> local time:' || EndDateLOC || ' */' );
DBMS_OUTPUT.PUT_LINE(chr(0));
OPEN CurObjList;
IF NOT TrezorsInArchiveDatabase THEN
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* Start of Exp.bat */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* Copy these commands to a separate file Exp.bat */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* and execute as a batch */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* They will produce a large volume of text files */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(chr(0));
END IF;
LOOP
FETCH CurObjList INTO AD_ID, AD_Name, Cnt_Row, Cnt_Col, TblName; /* for every archived object */
EXIT WHEN CurObjList%NOTFOUND;
IF TrezorsInArchiveDatabase THEN
DBMS_OUTPUT.PUT_LINE('/* Exporting ' || TblName || ' [' || TRIM(AD_NAME) || '] */' );
IF Cnt_Row=0 THEN /* DTxxxxxxx */
MyExpImp(0,0);
ELSIF Cnt_Col=0 THEN /* DTxxxxxxxR */
FOR i IN 1..Cnt_Row LOOP
MyExpImp(i,0);
END LOOP;
ELSE /* DTxxxxxxxRC */
FOR i IN 1..Cnt_Row LOOP
FOR j IN 1..Cnt_Col LOOP
MyExpImp(i,j);
END LOOP;
END LOOP;
END IF;
ELSE
/* Export from archive to .txt files */
DBMS_OUTPUT.PUT(CommentStr || 'Exporting ');
IF ParallelLoading THEN
DBMS_OUTPUT.PUT('and importing ');
END IF;
DBMS_OUTPUT.PUT_LINE(TblName || ' [' || TRIM(AD_NAME) || ']' );
IF Cnt_Row=0 THEN /* DTxxxxxxx */
MyExp(0,0);
ELSIF Cnt_Col=0 THEN /* DTxxxxxxxR */
FOR i IN 1..Cnt_Row LOOP
MyExp(i,0);
END LOOP;
ELSE /* DTxxxxxxxRC */
FOR i IN 1..Cnt_Row LOOP
FOR j IN 1..Cnt_Col LOOP
MyExp(i,j);
END LOOP;
END LOOP;
END IF;
END IF;
END LOOP;
CLOSE CurObjList;
IF NOT TrezorsInArchiveDatabase THEN
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* End of commands for Exp.bat */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* End of Exp.bat */');
DBMS_OUTPUT.PUT_LINE(chr(0));
IF ParallelLoading THEN
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* Start of Imp.bat */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* Copy these commands to a separate file imp.bat */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* and execute as a batch in the same directory */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* where exp.bat was executed. Data exported in */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* exp.bat will be now reloaded to a new trezor */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(chr(0));
/* Import from .txt files to DataN table */
OPEN CurObjList;
LOOP
FETCH CurObjList INTO AD_ID, AD_Name, Cnt_Row, Cnt_Col, TblName; /* for every archived object */
EXIT WHEN CurObjList%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(CommentStr || ' Importing ' || TblName || ' [' || TRIM(AD_NAME) || ']' );
IF Cnt_Row=0 THEN /* DTxxxxxxx */
MyImp(0,0);
ELSIF Cnt_Col=0 THEN /* DTxxxxxxxR */
FOR i IN 1..Cnt_Row LOOP
MyImp(i,0);
END LOOP;
ELSE /* DTxxxxxxxRC */
FOR i IN 1..Cnt_Row LOOP
FOR j IN 1..Cnt_Col LOOP
MyImp(i,j);
END LOOP;
END LOOP;
END IF;
END LOOP;
CLOSE CurObjList;
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* End of commands for imp.bat */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE(CommentStr || '/* End of imp.bat */');
END IF;
END CopyDataToTrezor;
PROCEDURE WriteInfoHeader IS
FUNCTION B2Txt( B in BOOLEAN) return VARCHAR2 IS
begin
IF B THEN
RETURN 'TRUE';
ELSE
RETURN 'FALSE';
END IF;
end;
BEGIN
DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */');
DBMS_OUTPUT.PUT_LINE('/* Arc2Trezor was run with these parameters ');
DBMS_OUTPUT.PUT_LINE(' TrezorId : ' || TrezorId);
DBMS_OUTPUT.PUT_LINE(' AppName : ' || AppName );
DBMS_OUTPUT.PUT_LINE(' TrezorPath : ' || TrezorPath);
DBMS_OUTPUT.PUT_LINE(' TrezorCountDatafiles : ' || TrezorCountDatafiles);
DBMS_OUTPUT.PUT_LINE(' TrezorStartDatafileSize : ' || TrezorStartDatafileSize);
DBMS_OUTPUT.PUT_LINE(' TrezorMaxDatafileSize : ' || TrezorMaxDatafileSize);
DBMS_OUTPUT.PUT_LINE(' TrezorDatafileSizeStep : ' || TrezorDatafileSizeStep);
DBMS_OUTPUT.PUT_LINE(' StartDateUTC : ' || StartDateUTC);
DBMS_OUTPUT.PUT_LINE(' EndDateUTC : ' || EndDateUTC);
DBMS_OUTPUT.PUT_LINE(' TrezorsInArchiveDatabase: ' || B2Txt(TrezorsInArchiveDatabase));
IF NOT TrezorsInArchiveDatabase THEN
DBMS_OUTPUT.PUT_LINE(' ArchivDBLogonInfo : ' || ArchivDBLogonInfo);
DBMS_OUTPUT.PUT_LINE(' TrezorDBLogonInfo : ' || TrezorDBLogonInfo);
DBMS_OUTPUT.PUT_LINE(' OSTypeIsWindows : ' || B2Txt(OSTypeIsWindows));
DBMS_OUTPUT.PUT_LINE(' ParallelLoading : ' || B2Txt(ParallelLoading));
END IF;
DBMS_OUTPUT.PUT_LINE('*/');
DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */');
END WriteInfoHeader;
PROCEDURE OfflineTrezor IS
BEGIN
DBMS_OUTPUT.PUT_LINE('/* put tablespace ' || TrezorTablespace || ' to read-only mode */');
DBMS_OUTPUT.PUT_LINE('ALTER TABLESPACE "' || TrezorTablespace || '" READ ONLY;');
DBMS_OUTPUT.PUT_LINE('/* put tablespace ' || TrezorTablespace || ' offline */');
DBMS_OUTPUT.PUT_LINE('ALTER TABLESPACE "' || TrezorTablespace || '" OFFLINE;');
END OfflineTrezor;
BEGIN
dbms_output.enable(buffer_size => null);
IF OSTypeIsWindows THEN
CommentStr := 'REM ';
BackgroundStart := 'START ';
BackgroundEnd := '';
ELSE
CommentStr := '# ';
BackgroundStart := '';
BackgroundEnd := ' &';
END IF;
WriteInfoHeader;
CreateTargetTablespace;
CopyArchivDefToTrezor;
CopyDataToTrezor;
OfflineTrezor;
END;
/