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?
Russian Progress User Group
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):
INFILE 'account.txt' "str '^^^'"
INFILE 'account-1.txt' "str '^^^'"
INTO TABLE ACCOUNT
FIELDS TERMINATED BY '\t' TRAILING NULLCOLS
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')",
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 firstname.lastname@example.org/pro2sid control=account.ctl errors=100 bad=account.bad
I hope this helps someone.
If you know how to improve this, I will be happy with your comments.