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).[View:/cfs-file/__key/communityserver-discussions-components-files/248/atm_5F00_ACCOUNT.sql:320:240]
I did not find any option to do the same for Oracle. Is this possible?
Regards,
Valeriy
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.
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.