SQL LOADER Overview

SQL Loader Overview

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. It supports various load formats, selective loading, and multi-table loads.

Below are the examples of loading data into Tables using different methods

Load Fixed length Data Records
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

Load Positional Data Records
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

Skip First 2 Header Records while Loading
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 2
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
NNNNNNNNNNNNNNN
UUUUUUUUUUUUUUU
11111AAAAAAAAAA
22222BBBBBBBBBB

Loading Data into Multiple Tables
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)

Skip Certain Columns while Loading DataLOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

Erroneous data after loading with SQL Loader

Our Database is created with below settingsCHARACTER SET "UTF8"
NATIONAL CHARACTER SET "WE8ISO8859P1"
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA

Include below code in control file to avoid junk data load using SQL loaderLOAD DATA
CHARACTERSET WE8ISO8859P1
APPEND INTO TABLE XXAP_GENERIC_INVOICES
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(operating_unit
,vendor_number
)

Using SQL Functions for Loader Columns
LOAD DATA
APPEND INTO TABLE
XXAR_INV_AT
FIELDS TERMINATED BY ""
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
DESCRIPTION "NVL(:DESCRIPTION,'Migrated from Legacy')",
TRX_DATE "TO_CHAR(TO_DATE(:TRX_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,
GL_DATE "TO_CHAR(TO_DATE(:GL_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,
TAX_RATE "REPLACE(:TAX_RATE,'%','')",
STAGING_TABLE_ID "XXAR_INV_AT_S1.NEXTVAL",
CREATION_DATE SYSDATE,
LAST_UPDATE_DATE SYSDATE
SOURCE "TRIM(REPLACE(:SOURCE,(SUBSTR(:SOURCE,LENGTH(:SOURCE),1)),''))"
)

Value Sets

AOL - Creating a Custom Account Flex Field Parameter for Concurrent Program in Oracle

We have seen in seeded GL reports that take the accounting flex field as parameters, the DFF form opens on the SRS form when we click on the flex field parameter. The user enters the flex field into the form. The flex field is then transferred into the report and the output is generated. This is a very handy way of allowing the user to enter the Accounting KFF.

To create a custom accounting flex field parameter, Below are the steps

Step 1: Create a RDF(XXAA_ACCT_FLEXFIELD_REP.rdf) with two(P_ACCT_LO, P_ACCT_HI) parameters, below is the query.

SELECT

      GCC.CODE_COMBINATION_ID

      ,GCC.CONCATENATED_SEGMENTS

      ,GCC.SEGMENT1

      ,GCC.SEGMENT2

      ,GCC.SEGMENT3

      ,GCC.SEGMENT4

      ,GCC.SEGMENT5

FROM GL_CODE_COMBINATIONS_KFV GCC

WHERE 1=1

      AND gcc.segment1 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 1, 2), '00') AND NVL (SUBSTR (:P_ACCT_HI, 1, 2), '99')

      AND gcc.segment2 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 4, 3), '000') AND NVL (SUBSTR (:P_ACCT_HI, 4, 3), '999')

      AND gcc.segment3 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 8, 4), '0000') AND NVL (SUBSTR (:P_ACCT_HI, 8, 4), '9999')

      AND gcc.segment4 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 13, 4), '0000') AND NVL (SUBSTR (:P_ACCT_HI, 13, 4), '9999')

      AND gcc.segment5 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 18), '000') AND NVL (SUBSTR (:P_ACCT_HI, 18), '999')

ORDER BY 3,4,5,6,7

   

Compile the RDF and save

Step 2: Create a value set(XXAA_GL_SRS_LEDGER_FLEXFIELD) with validation type as Pair

Navigation : System Administrator  àApplication àValidation àSet

Enter the value set name and select the validation type as Pair and click on the edit information

Pair :

  Use the Validation Rules window to define special validation for a Special value set. You also use this window to define validation routines for a Pair value set.

You can use this region to define a value set that lets your users enter an entire key flexfield combination within a single report parameter. For example, you may want to pass concatenated Accounting Flexfield segments as a parameter to a report. With this type of value set, a user can enter the report parameter and then see the "normal" behavior of a key flexfield, such as the key flexfield window and segment validation associated with that key flexfield. You use Oracle Application Object Library flexfield routines for these special value sets

Enter the below code and save

-->Event : EDIT 

FND POPIDR APPL_SHORT_NAME="SQLGL" CODE="GL#" REQUIRED="N" DISPLAY="1" DISPLAY="2" DISPLAY="3" DISPLAY="4"DISPLAY="5" VALIDATE="NONE" SEG=":!VALUE" DESC=":!MEANING" NAVIGATE="!DIR"

--> Event : VALIDATE 

FND VALIDR APPL_SHORT_NAME="SQLGL" CODE="GL#" VALIDATE="NONE" REQUIRED="N" DISPLAY="1" DISPLAY="2" DISPLAY="3"DISPLAY="4" DISPLAY="5" DESC=":!MEANING" SEG=":!VALUE"

Step 3 : Create a Concurrent Program(XXAA Account Flex Field Report)

Note : Port the RDF(XXAA_ACCT_FLEXFIELD_REP.rdf) file into unix server

Log in to Oracle Apps with System Administrator responsibility.

Navigation: Concurrent àProgram àExecutable

Executable : Enter the executable name, short name and RDF name.

Concurrent Program : Enter the program name, short name and select the executable name and select the output type format as XML

Save the form and click on Parameters button.

Step 4 : Create below parameters

1)      Parameter : From Account

Select the value set as we created in step 2(XXAA_GL_SRS_LEDGER_FLEXFIELD)

Enter the token value : P_ACCT_LO

2)      Parameter : To Account

Select the value set as we created in step 2(XXAA_GL_SRS_LEDGER_FLEXFIELD)

Enter the token value : P_ACCT_HI

Save it

Step 5: Assign the concurrent program to the request set

Save and close the form.

Step 6 :  Create data definition, data temple and upload the rtf file

Date Definition :

Data Template :

Step 7 : Run the concurrent program from any Payables responsibility

Select the from account and to account values and submit the program

Check the report output

AOL - Dynamically enabling and disabling Parameters in Oracle Concurrent Program

There is a requirement that dynamically enabling and disabling Concurrent Program Parameters.

Let a concurrent program has two parameters – Display and Application Name. If the value for Display is ‘YES’, then Users should be enabled containing names of Applications in a system and if the value for Display is ‘NO’, then Application Name parameter gets disable.

Initially Application Name parameter is disable.

·   If user has selected YES from Display parameter then Application Name value set enabled containing application names.

·   If user has selected NO from display then Application Name disabled containing application names.

 To create value set:

Navigation: Application developer à Application à Validation à Set

Create an Independent type value set

Create YES and NO values for above XXAA_DISPLAY_PARAMETER valueset

Navigation: Application developer à Application à Validation àValues

Then create one Dummy Set of none type for enable and disable other parameter  

XXAA_NONE_DIPLAY_PARAM value set to enable User Value set

To create Concurrent Executable:

Navigation: Application developer à Concurrent à Executable

To create Concurrent Program:

Navigation: Application developer à Concurrent à Program

Create concurrent program:

Let Executable Name: XXAA_ENABLE_DISABLE_PARAMETER

Method: PL/SQL  Stored Procedure

Click on Parameters.

As first parameter attach XXAA_DISPLAY_PARAMETER value set which provide the options YES or NO. 

Here we are using Display Parameter Parameter to enable and disable other parameter of concurrent program.

Create a parameter of any name and assign a value set XXAA_NONE_DIPLAY_PARAM of NONE type to it.

Select default type as SQL Statement and in Default type write query as:

Select case when :$FLEX$.XXAA_DISPLAY_PARAMETER ='YES' then 'Y' else null end from dual

When YES means application is selected by display value set then XXAA_NONE_DIPLAY_PARAM value set returns ‘Y’ otherwise NULL.

All the table type value set which satisfies the where condition ‘Y’=:$FLEX$.validation are enable when this query runs and others will remain disable.

Now create value set which should be enable on particular condition:

Navigation: Application developer à Application à Validation à Set

Create Value set XXAA_APPLICATION_NAME of table type:

Write ‘Y’=:$FLEX$.XXAA_NONE_DIPLAY_PARAM in where condition so that when validation return Y this value set get enable otherwise remains disable.

Assign XXAA_APPLICATION_NAME value set to the parameters of concurrent Program and save it.

Attach the concurrent program to Request Group of the responsibility.

Navigation:

System Administrator à Security àResponsibility àRequest

Uncheck the Display check box so that it is hidden from user.

Run the concurrent program by

View à Requests à Submit a new Request àSingle Request.

Output :

Here user selects NO from Display value set then Application Name disabled.

Here user selects YES from options value set then application value set get enabled.

API to Upload Oracle Value set Values

DECLARE

   v_enabled_flag        VARCHAR2 (2)                  := 'Y';

   v_summary_flag        VARCHAR2 (2)                  := 'Y';

   v_start_date_active   DATE                          := SYSDATE;

   v_error_msg           VARCHAR2 (1000)               := NULL;

   v_who_type            fnd_flex_loader_apis.who_type ;

   v_request_id          NUMBER;

   v_rec_success         NUMBER;

   v_rec_error           NUMBER;

   v_rec_cnt             NUMBER                        := 0;

   v_user_id             NUMBER                        := fnd_global.user_id;

   v_login_id            NUMBER                        := fnd_global.login_id;

   v_req_id              NUMBER                        := fnd_global.conc_request_id;

   v_value_set_name      VARCHAR2 (50)                 := 'Your Value set';

   v_value_set_value     VARCHAR2 (50)                 := 'your value';

BEGIN

   v_who_type.created_by := v_user_id;

   v_who_type.creation_date := SYSDATE;

   v_who_type.last_updated_by := v_user_id;

   v_who_type.last_update_date := SYSDATE;

   v_who_type.last_update_login := v_login_id;

   BEGIN

      fnd_flex_values_pkg.load_row

                                 (x_flex_value_set_name             => v_value_set_name,

                                  x_parent_flex_value_low           => NULL,

                                  x_flex_value                      => v_value_set_value,

                                  x_who                             => v_who_type,

                                  x_enabled_flag                    => v_enabled_flag,

                                  x_summary_flag                    => v_summary_flag,

                                  x_start_date_active               => v_start_date_active,

                                  x_end_date_active                 => NULL,

                                  x_parent_flex_value_high          => NULL,

                                  x_structured_hierarchy_level      => NULL,

                                  x_hierarchy_level                 => NULL,

                                  x_compiled_value_attributes       => NULL,

                                  x_value_category                  => NULL,

                                  x_attribute1                      => NULL,

                                  x_attribute2                      => NULL,

                                  x_attribute3                      => NULL,

                                  x_attribute4                      => NULL,

                                  x_attribute5                      => NULL,

                                  x_attribute6                      => NULL,

                                  x_attribute7                      => NULL,

                                  x_attribute8                      => NULL,

                                  x_attribute9                      => NULL,

                                  x_attribute10                     => NULL,

                                  x_attribute11                     => NULL,

                                  x_attribute12                     => NULL,

                                  x_attribute13                     => NULL,

                                  x_attribute14                     => NULL,

                                  x_attribute15                     => NULL,

                                  x_attribute16                     => NULL,

                                  x_attribute17                     => NULL,

                                  x_attribute18                     => NULL,

                                  x_attribute19                     => NULL,

                                  x_attribute20                     => NULL,

                                  x_attribute21                     => NULL,

                                  x_attribute22                     => NULL,

                                  x_attribute23                     => NULL,

                                  x_attribute24                     => NULL,

                                  x_attribute25                     => NULL,

                                  x_attribute26                     => NULL,

                                  x_attribute27                     => NULL,

                                  x_attribute28                     => NULL,

                                  x_attribute29                     => NULL,

                                  x_attribute30                     => NULL,

                                  x_attribute31                     => NULL,

                                  x_attribute32                     => NULL,

                                  x_attribute33                     => NULL,

                                  x_attribute34                     => NULL,

                                  x_attribute35                     => NULL,

                                  x_attribute36                     => NULL,

                                  x_attribute37                     => NULL,

                                  x_attribute38                     => NULL,

                                  x_attribute39                     => NULL,

                                  x_attribute40                     => NULL,

                                  x_attribute41                     => NULL,

                                  x_attribute42                     => NULL,

                                  x_attribute43                     => NULL,

                                  x_attribute44                     => NULL,

                                  x_attribute45                     => NULL,

                                  x_attribute46                     => NULL,

                                  x_attribute47                     => NULL,

                                  x_attribute48                     => NULL,

                                  x_attribute49                     => NULL,

                                  x_attribute50                     => NULL,

                                  x_attribute_sort_order            => NULL,

                                  x_flex_value_meaning              => v_value_set_value,

                                  x_description                     => v_value_set_value

                                 );

      COMMIT;

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Error is ' || SUBSTR (SQLERRM, 1, 1000));

   END;

END;