SQL* Loader concurrent program

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle Database.

You can load data into an Oracle database by using the sqlldr command in UNIX.

sqlldr username@server/password control =loader.ctl data=data_file.csv

loader.ctl is the control file.

Following is the example of control file that needs to be  placed at the $XX_TOP/bin directory

Options (skip=1)
LOAD DATA
INFILE ‘/u02/oracle/VIS/apps/apps_st/appl/per/12.0.0/bin/data_file.csv’
APPEND
INTO TABLE XX_EMP_STG
fields terminated by “,” optionally enclosed by ‘”‘
TRAILING NULLCOLS          
(
TITLE,
FIRST_NAME,
HIRE_DATE,
PERSON_TYPE,
BUSINESS_GROUP_ID,
LAST_NAME,

–Tracking Columns
Last_Updated_By “FND_GLOBAL.USER_ID”,
Last_Update_Date SYSDATE,
Created_By “FND_GLOBAL.USER_ID”,
Creation_Date SYSDATE,
Last_Update_Login “FND_GLOBAL.LOGIN_ID”
)

Open the MS-Excel spreadsheet and save it as a CSV (Comma Separated Values) file. This file can now be copied to the Oracle machine and loaded using the SQL*Loader utility.

Possible problems and workarounds:

The spreadsheet may contain cells with newline characters (ALT+ENTER). SQL*Loader expects the entire record to be on a single line. Run the following macro to remove newline characters (Tools -> Macro -> Visual Basic Editor):

' Removing tabs and carriage returns from worksheet cells
Sub CleanUp()
 Dim TheCell As Range
 On Error Resume Next

 For Each TheCell In ActiveSheet.UsedRange
   With TheCell
     If .HasFormula = False Then
       .Value = Application.WorksheetFunction.Clean(.Value)
     End If
   End With
 Next TheCell
End Sub

Executable

exe

 

Note that the extension .ctl is not entered in the executable form.

The control file emp_ctl.ctl file should exist in $XXCUST_TOP/bin directory (XXCUST_TOP corresponds to Custom Applications).

Concurrent Definition:

proc

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s