Registering SQL*Loader as a Concurrent Program

Registering SQL*Loader as a Concurrent Program

Registering SQL*Loader as a Concurrent Program

The following steps will describe the process to register a SQL*Loader program as a Concurrent Program in Oracle Apps.

Step 1]

Create the SQL*Loader Control and Data file and place them in Server(ex: $CUSTOM_TOP/bin). Create or check the interface table structures in the backend.

Control file: test.ctl

Data file: test.dat

CREATE TABLE testdept
(deptno NUMBER(2) NOT NULL,
 dname VARCHAR2(14),
 loc VARCHAR2(13));

Step 2]

Go to Application Developer > Concurrent > Executables. Define a Concurrent Program Executable. Choose the Execution Method as SQL*Loader and give the Execution File Name as the name of the SQL*Loader control file. Save your work.

Step 3]

Go to Application Developer > Concurrent > Program. Define the Concurrent Program. Attach the executable defined above.

Step 4]

Go to parameters of the concurrent program. Create a parameter to take the server path of the data file. You can also place the default value.

Step 5]

Attach the Concurrent program to a Responsibility through a Request Group.

Step 6]

Go to that Responsibility and Run the Concurrent Program. If successful check the output file that have all data uploading information.

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TESTDEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
—————————— ———- —– —- —- ———————
DEPTNO                               FIRST     *   ,  O(“) CHARACTER      
DNAME                                NEXT     *   ,  O(“) CHARACTER      
LOC                                     NEXT     *   ,  O(“) CHARACTER           

Table TESTDEPT:
  7 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:           0
Total logical records read:               7
Total logical records rejected:          0
Total logical records discarded:        0

Run began on Thu Aug 12 09:41:55 2010
Run ended on Thu Aug 12 09:41:56 2010

Elapsed time was:      00:00:00.11
CPU time was:           00:00:00.01

Step 7]

Check in the backend whether the tables got updated or not.

The Bad and Discard files will be created in /conc/out file of the server.

Add a Concurrent Program to a Request Group

Add a Concurrent Program to a Request Group

Background:
Add a concurrent program to a request group through API/Script

Script:

BEGIN
fnd_program.add_to_group(
        PROGRAM_SHORT_NAME  => 'XX_SHORTNAME',
        PROGRAM_APPLICATION => 'XX_PROG_APPL',
        REQUEST_GROUP       => 'All Reports',
        GROUP_APPLICATION   => 'SQLAP'
      );

commit;

END;
/

Explanation of the Parameters:

program_short_name  = Short name of the program. (e.g. FNDSCRMT)
program_application   = Application of the program. (e.g. 'FND')
request_group            = Name of request group
group_application       = Application of the request group