SQL * LOADER – Oracle Tool
It is one of the Oracles tool will be used to upload the data from flat files in to oracle database tables.
Flat file or Data file:It contains the data in specific format it will be either text file (.txt) or excel sheet. (.txt, .dat, .csv – Comma Separated Value)
Control File: It contains SQL * Loader program contains data file path, database table name and column mapping and soon, once we develop the control file we will execute this, that time data will be transfer from file to Oracle Database Table, during this data transfer, the following files will be created. The extension of the control files is .ctl.
Badfile: It contains the rejected records which are rejected by SQL*Loader, because of bad format or data type mismatch and so on. The extension of the bad file .bad.
Discard file: It contains the rejected records which are rejected by control file if we have specified any condition in the control file if record is not satisfying the condition the complete record will be inserted in the discard file. The extension of file is .dis.
Log file: It contains information about control file execution like, execution start time and end time, successful records count, bad records count, if there are any errors in the control file, those error messages will be available in segments, the extension of file .log.
The Syntax of the control file is as follows:
And save as .ctl file
For execution Command for .ctl file.
Control = <Control file Patch>
- Connect to Scott Schema and create temporary table
Select * from emp_details
- Prepare data file and save in the local machine.
- Develop the control file like as follows and save it with extension .ctl
- Go to Command Prompt select following path
- Execute the control file with SQLLDR Command
Control = C:\20\LOAD\EMP.CTL
- To find path to execute the SQLLDR command is HOST Command from SQL prompt which will shows the path where we can execute control file
- Select * from v$parameter.
INSERT – Table should be empty – New records will be inserted.
APPEND – New records will be add for existing records
TRUNCATE – It will delete existing records and inserts new records from the file.
.csv file (Comma Separated Valu)
If data in excel sheet we can save that file as .csv file.
If any string contains commas we will enclosed this will in double quotation in the control file we will specify the following statement syntax
Fields Terminated by ‘ , ‘ optionally enclosed by ‘ ” ’
If one of the field is missing for the database column we can generate sequences and also we can use the some functions like to_char, NVL, Decode, Instr, Rtrim, Trim and so on
Training Nullcols – Inserts NULL columns if no data is there, we can not use any DDL, DML or TCL command in control file.
Fixed format control file exampled
Sometimes data may found in fixed format – for example
Empno – 5, Ename – 10, Jdate – 11, Deptno – 2, Sal – 5, Comm – 3, Tax -3
If data is in the fixed format the control file will be developed in different format
In control file itself we can specify the data and at the time of insertion we specify the specify the condition and we can also specify the discard file pat in control file it self – for example
Along with the SQLLDR command we can also use the few commands like as bellow
LOAD – Will be used to load the first ‘N’ records
SQLLDER username/password@Hoststring Load=10
SKIP – TO skip the first ‘n’ records
SQLLDER username/password@Hoststring skip=10
ROWS – It will be used to issue the commit after insert ’N’records for every 10 records internally auto commit will happen
SQLLDER username/password@Hoststring rows=10
INIT.ORA file contains the default values for the commit point
Data Uplaoding in to two tables
Positions of the data in datafile
Empno 5, Ename 10, Job1, Deptno2, dname 7
DECODE Statement to be used to Code the ‘A” for Analyst ‘M’ for Manager and Multiple data files can also be called in single control file
Example control file
Register Control file as Concurrent Program
- Develop the control file and move the both data file and control file in the server
- CUS_TOP\11.5.0\BIN\ .CTL
- Create executable by seleting the execution method as SQL*Loader
- create concurrent Program attach executable, Parameters
- Attach the concurrent program to the Request Group
- Attach the Request group to the Responsibility
- Attach Responsibility to the user will submit the request form SRS window.
Create the table in APPS Schema
Basic Cost to the calculated field in the Control file i.e. ItemCost – ItemTax
Development of Control file
- Move the .ctl file in the Inventory top i.e. BIN Directory
- And register the control file with applications.
- Go to System Administrator and register with the applications and concurrent program and method should be SQL * Loader
- Data file pat can changed dynamically
- Infile ‘&1’
- We muse use the ‘1` degit
- We are allowed to submit only one parameter that should be data file path
SQL * Loader with Parameters
At the time of defining concurrent programe select parameter butiton define the parameter specify the ‘&1’ in the control file so that what ever the values we have passed that will be replaced in the control file.
1. How to issue the commit in the control file
a. By suing ROWS Command
2. What are the types loading we have
a. Direct Method Load
b. Conventional method load (Default)
3. How to upload the data in to multiple table at with time
a. By using WHEN command
4. Can we have the data in Fixed format and how to upload
a. Yes, we can have fixed format that time we will use POSITION keyword
5. What is the difference between Bad file and Discard file
a. Both file contains rejected records – Bad file contains records which are rejected by SQL*Loader and Discard file contains which are rejected by Control file.
6. What are the SQL functions we can use in the control file
a. TO_CAHR, TO_DATE, INSTER, SUBSTER, RTRIM, LTRIM, DECODE, NVL and so on.
7. can we use user defined functions
a. NO We can’t user defined functions
8. If control file rejected more than 10 records error the I would like to stop the process how to do it
a. We have a file called INIT.ORA file where we will set parent called maximum errors allowed and max auto commit other parameter also.
9. How to skip specific field data
a. By using FILLER command – In this position column will not accept the data and it inserts the NULL values.
SQL * Plus
- Develop the SQL Program (.sql)
- Move the file form local machine to server in to respective path
- CUST_TOP\11.5.0\SQL\ .sql
- Create executable execution method as “SQL*PLUS”
- Crate concurrent program and attach executable and parameters
- Attach concurrent program to the request group
- Attach request group to the responsibility
- Attach responsibility to the user.
- Submit request from SRS Window
SQL * Plus Program
Column User_id format 9999999999
Column User_name Format A25
Column Cration_Date Format A11
Column &3 Format A50
Prompt SQL Report with User Details
Select User_ID, User_Name, Creation_Date, &3 from fnd_user
Where User_id between &1 and &2
Save as the file .sql and create the parameters in Oracle apps systems administrator and column can be added dynamically
- We can define Maximum 100 parameter
- We are suppose to use &1, &2, &3 ……………
- We are not suppose to skip the sequence
- WE can use same parameter values in multiple places in the script
- SQL * PLUS concurrent program to execute DDL and Connect to remote database and executes the scripts and so on.
the access to the front end Submission of Concurrent Program from Back End because, sometimes we don’t have.
We need to use the Application Programe Interface details in 115devg.pdf 21 chapter
FND_PROGRAM.EXECUTABLE – Executable
FND_PROGRAM.REQUEST – Concurrent Program
FND_PROGRAM.PARAMETER – Attach Parameter
FND_PROGRAM.REQUEST_GROUP – Create Request Group
FND_PROGRAM.ADD_TO_GROUP – Add Concurrent Program R.G
Submission of CP from Back End
Report – Report Triggers
Forms – Form Trigger
FND_REQUEST.SUBMIT_REQUEST( ) – It will be used to submit the Request from Back end.
NOTE: If we are submitting concurrent program from the Form Trigger we are suppose to pass the 100 parameter. If parameter are not defined, still we are suppose to pass NUL values
Fnd_responsibility_vl – responsibility_key
Fnd_application_vl – application_name
Scheduling the Concurrent program
We can submit the Concurrent program future date or date by using the schedule button in SRS window
1. As soon as possible: This is default option whenever we submit the request it will submit the as soon as possible
2. Once: It will submit the rest only once for future date.
3. Periodically: WE can specify the from_date and to_date to submit program periodically no of. Days months, hours, minutes and so on.
4. Specific Days: If we want submit concurrent program in the specific days we write select this option
5. Save this Schedule: This check box will be used to save the schedule and apply same schedule to other concurrent programs by selecting the button called ‘Apply save schedule’
NOTE: After schedule the Concurrent program we can also cancel by selecting the cancel button.