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;
/