Since, pl/sql belongs to oracle family and also no need of Middle tier code (application server dependent i.e. Java or .NET etc), we use it in this example. Oracle provides another tool, SQL Loader, using which we can import data files with our custom control files with respect to SQL Loader.
$ sqlldr apps/apps@fz1rw102 control=mycontrolfile.ctl log=test.log
When you install SQL Loader in your system (windows/linux), you can find ’sqlldr’ executable in bin directory.
From the above command,
1. apps/apps@fz1rw102 - username/passwd@SID of oracle database.
2. mycontrolfile.ctl - control file we write will be passed.
3. test.log - log file to know the errors/warnings during the course.
Content of mycontrolfile.ctl is:
LOAD DATA
INFILE ‘data.dat’
APPEND
INTO TABLE UJJWAL_TEST
FIELDS TERMINATED BY “:” OPTIONALLY ENCLOSED BY ‘”‘
(PAYMENT_NUMBER,PAYMENT_METHOD,CUSTOMER_NAME,
CUSTOMER_NUMBER,
APP_GL_DATE DATE(20) “DD-MON-YYYY”,
PAYMENT_GL_DATE DATE(20) “DD-MON-YYYY”,
ENTERED_DEBIT,ENTERED_CREDIT,ACCTD_DEBIT,
ACCTD_CREDIT,
RECEIPT_DATE DATE(20) “DD-MON-YYYY”,
RECEIPT_CURRENCY,CODE_COMBINATION_ID,RECEIPT_SOURCE,
BATCH_NAME,CATEGORY,ACCOUNT,COMPANY,ACCOUNT_DESC)
In the above content, you can find file name ‘data.dat’ which is the CSV file having the data to be imported into some table named PAVAN_TEST with it’s fields mentioned over there.
For more details about sqlloader, please refer to here.
No comments:
Post a Comment