ASCII Bulk Build (create procedures) - Forum - OpenEdge Pro2 - Progress Community

ASCII Bulk Build (create procedures)

 Forum

ASCII Bulk Build (create procedures)

This question is answered

Hello,

I'm trying to use Pro2-AS-Src-Admin-Tool.sh script for create procedures for bulk load ASCII data into Oracle.

But I see that the sql-script was created for MSSQL (see the attachment).atm_ACCOUNT.sql

I did not find any option to do the same for Oracle. Is this possible?

Regards,
Valeriy

Verified Answer
  • For those who will be interested in this.

    After starts "ASCII Bulk-Export" through "Pro2 - App-Server Admin Tool" for make dump content from table, you should create SQL*Loader control file for load this dump file(s) into Oracle.

    That is a simple example of control file for Account table from ATM test (10 000 000 records):

    LOAD DATA
    INFILE 'account.txt' "str '^^^'"
    INFILE 'account-1.txt' "str '^^^'"
    APPEND
    INTO TABLE ACCOUNT
    FIELDS TERMINATED BY '\t' TRAILING NULLCOLS  
    (   PRROWID, 
    	ID, 
    	BALANCE, 
    	BRANCHID, 
    	FILL1, 
    	FILL2, 
    	FILL3, 
    	PRO2SRCPDB,
    	PRO2CREATED "TO_TIMESTAMP_TZ(:PRO2CREATED,'dd/mm/yyyy HH24:MI:SS.FF3TZH:TZM')", 
    	PRO2MODIFIED "TO_TIMESTAMP_TZ(:PRO2MODIFIED,'dd/mm/yyyy HH24:MI:SS.FF3TZH:TZM')", 
    	PROGRESS_RECID "ACCOUNT_SEQ.NEXTVAL"
    )

    The LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load. 

    The INFILE clause specifies the name of a datafile containing data that you want to load. You can specify multiple files to load. The STR attribute with INFILE clause specifies a new end-of-line character. For  "ASCII Bulk-Export"  from Pro2 it is '^^^' string.

    The APPEND clause is one of the options you can use when loading data into a table that is not empty. I use it here since I load two files at once.

    The TERMINATED BY  clause is one of the delimiters it is possible to specify for a field. In this example it is tab (\t).

    The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.  I'm using it here since I'm generating the PROGRESS_RECID field while it's not in the dump file. To generate a field value, I use the next value of the sequence ACCOUNT_SEQ.

    After preparing the control file, you simply run the following command to start the data load:

    sqlldr userid=pro2/qwerty@172.16.95.140/pro2sid control=account.ctl errors=100 bad=account.bad

    I hope this helps someone.

    P.S.

    If you know how to improve this, I will be happy with your comments.

All Replies
  • For those who will be interested in this.

    After starts "ASCII Bulk-Export" through "Pro2 - App-Server Admin Tool" for make dump content from table, you should create SQL*Loader control file for load this dump file(s) into Oracle.

    That is a simple example of control file for Account table from ATM test (10 000 000 records):

    LOAD DATA
    INFILE 'account.txt' "str '^^^'"
    INFILE 'account-1.txt' "str '^^^'"
    APPEND
    INTO TABLE ACCOUNT
    FIELDS TERMINATED BY '\t' TRAILING NULLCOLS  
    (   PRROWID, 
    	ID, 
    	BALANCE, 
    	BRANCHID, 
    	FILL1, 
    	FILL2, 
    	FILL3, 
    	PRO2SRCPDB,
    	PRO2CREATED "TO_TIMESTAMP_TZ(:PRO2CREATED,'dd/mm/yyyy HH24:MI:SS.FF3TZH:TZM')", 
    	PRO2MODIFIED "TO_TIMESTAMP_TZ(:PRO2MODIFIED,'dd/mm/yyyy HH24:MI:SS.FF3TZH:TZM')", 
    	PROGRESS_RECID "ACCOUNT_SEQ.NEXTVAL"
    )

    The LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load. 

    The INFILE clause specifies the name of a datafile containing data that you want to load. You can specify multiple files to load. The STR attribute with INFILE clause specifies a new end-of-line character. For  "ASCII Bulk-Export"  from Pro2 it is '^^^' string.

    The APPEND clause is one of the options you can use when loading data into a table that is not empty. I use it here since I load two files at once.

    The TERMINATED BY  clause is one of the delimiters it is possible to specify for a field. In this example it is tab (\t).

    The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.  I'm using it here since I'm generating the PROGRESS_RECID field while it's not in the dump file. To generate a field value, I use the next value of the sequence ACCOUNT_SEQ.

    After preparing the control file, you simply run the following command to start the data load:

    sqlldr userid=pro2/qwerty@172.16.95.140/pro2sid control=account.ctl errors=100 bad=account.bad

    I hope this helps someone.

    P.S.

    If you know how to improve this, I will be happy with your comments.