V
Valeriy Bashkatov
Guest
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_TZPRO2MODIFIED,'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.
Continue reading...
PRO2CREATED,'dd/mm/yyyy HH24:MI:SS.FF3TZH:TZM')", PRO2MODIFIED "TO_TIMESTAMP_TZPRO2MODIFIED,'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.Continue reading...