making standard or custom webadi integrator updatable

Run below script:

update bne_integrators_b

set source='C'

where integrator_code = <>

Dynamic Date in Footer in XML Publisher using XDO Function

Use below code to achieve it.

©<?xdoxslt:sysdate('yyyy')?> Company, Inc. All rights reserved.

Adding Hyperlink to XML Publisher RTF

Using Word's Hyperlink feature will not work, as it appends the server or local path. Instead use a form field with the URL referenced . Ex:

 
<?variable@incontext:link;TRACKING_URL?><fo:basic-link external-destination="url({$link})"><?WAYBILL?></fo:basic-link>

Shows value of waybill flyover as TRACKING_URL.

=============

http://www.adivaconsulting.com/blog-bi-publisher/item/49-using-drill-down-in-bip-report.html

<?param@begin:CURRENT_SERVER_URL?>

<?$CURRENT_SERVER_URL?>

============= 

=================
Refer: http://winrichman.blogspot.com/2010/03/hyperlink.html
=================

option1:

If the XML data includes an element that contains a hyperlink , then you can use that element to create dynamic hyperlinks at runtime. 
a. insert hyperlink from word menu 
b. In the Type the file or Web page name field of the Insert Hyperlink dialog box, enter the following syntax:
c. {ELEMENT_NAME_WHICH_HAS_URL_LINK}
where ELEMENT_NAME_WHICH_HAS_URL_LINK is the xml data element name

option2:

create a form field, you can add it

<fo:basic-link external-destination="http://www.google..com">
<fo:inline text-decoration="underline">google Link</fo:inline>
</fo:basic-link>

or

<fo:basic-link external-destination="{ELEMENT_NAME}">
<fo:inline text-decoration="underline">google Link</fo:inline>
</fo:basic-link>

or

<fo:basic-link>
<xsl:attribute name="external-destination"><xsl:value-of select="ELEMENT_URL"/>
</xsl:attribute>
<xsl:value-of select="LINK_NAME"/>
</fo:basic-link>

Dynamic Image Display in XML Publisher

OA Media Directory Reference 

(XML Publisher Report Designer's Guide Release 12 Part No. B31410-01 )

Note: This method only applies to Oracle E-Business Suite installations.

1. Insert a dummy image in your template.

2. In Microsoft Word's Format Picture dialog box select the Web tab. Enter the following syntax in the Alternative text region to reference the OA_MEDIA directory:

url:{'${OA_MEDIA}/image name'}

For example, enter:

url:{'${OA_MEDIA}/ORACLE_LOGO.gif'}

Element Reference from XML File

1. Insert a dummy image in your template.

2. In Microsoft Word's Format Picture dialog box select the Web tab. Enter the following syntax in the Alternative text region to reference the image URL:

url:{IMAGE_LOCATION}

where IMAGE_LOCATION is an element from your XML file that holds the full

URL to the image.

You can also build a URL based on multiple elements at runtime. Just use the concat function to build the URL string. For example:

url:{concat(SERVER,'/',IMAGE_DIR,'/',IMAGE_FILE)}

where SERVER, IMAGE_DIR, and IMAGE_FILE are element names from your XML

file that hold the values to construct the URL.

This method can also be used with the OA_MEDIA reference as follows:

url:{concat('${OA_MEDIA}','/',IMAGE_FILE)}

Report Triggers

Sample Before Report Trigger

function BeforeReport return boolean is

begin

DECLARE

BEGIN 

  BEGIN 

  SRW.USER_EXIT('FND SRWINIT');

  EXCEPTION

     WHEN SRW.USER_EXIT_FAILURE THEN

SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger - SRWINIT USER EXIT');

     return (FALSE);

  END;

BEGIN  /*MOAC*/

:P_ORG_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();

END;

Sample After Report Trigger Calls Bursting

function AfterReport return boolean is

 nReturnRequestID NUMBER;

begin

srw.message(1,'Start of after report trigger: ');

BEGIN

  IF :CF_REQ_BY_EMAIL IS NOT NULL THEN

    nReturnRequestID       := FND_REQUEST.SUBMIT_REQUEST ( 'XDO' -- Application Short Name

    , 'XDOBURSTREP'                                              -- Program Short Name

    , 'XML Publisher Report Bursting Program'                    -- Program Name

    , NULL                                                       -- Start Date

    , FALSE                                                      -- Sub-request

    /* Program Parameters START */

    , 'Y'           -- Hidden Param

    , :P_CONC_REQUEST_ID -- Parameter 1: Data extract Request ID

    , 'N'           -- Debug Flag, defaulted to 'N'

    /* Program Parameters END */

    );

    srw.message(1,'REQUEST ID of the BURSTING PROGRAM is: ' || nReturnRequestID);

  END IF;

EXCEPTION

WHEN OTHERS THEN

    srw.message(1,'Unhandled error in after report trigger: ' || SQLERRM);

end;

BEGIN 

SRW.USER_EXIT('FND SRWEXIT');

EXCEPTION

   when srw.user_exit_failure then

      srw.message(1,'Failed in AFTER REPORT TRIGGER');

    return (FALSE);

END;  

return (TRUE);

End;

Sample After Parameter Form Trigger Using Laxical Parameters

function AfterPForm return boolean is

begin

srw.message(99999,'$Header: OEXOEACK.rdf 120.8.12000000.4 2008/07/28 04:03  zsingh noship                        $');

/*----------- Disable Trace Bug 3304392 ----------------*/

/*

declare

x varchar2(100);

begin

  if (:p_enable_trace = 'Y') Then

     srw.do_sql('ALTER SESSION SET SQL_TRACE=TRUE');

     select 'OEXOECK TRACE' into x from dual;

  end if;

end;

*/

/*----------- Disable Trace Bug 3304392 Ends----------------*/

BEGIN

  

  if :p_booked_status is NOT NULL then

     if :p_booked_status = 'Y' then

    :lp_booked_status := ' and h.booked_flag = ''Y'' ' ;

     else

:lp_booked_status := ' and h.booked_flag = ''N'' ' ; 

     end if;

  else

     :lp_booked_status := '';

  end if ;

/*-----------   Lexical parameter for the Order Date range    --------------------*/

    if (:p_order_date_low is NOT NULL ) AND (:p_order_date_high is NOT NULL )then

      :lp_order_date := ' and h.ordered_date between :p_order_date_low and (:p_order_date_high+1) ' ;

    elsif  (:p_order_date_low is NOT NULL ) then

      :lp_order_date := ' and h.ordered_date  >= :p_order_date_low ' ;

    elsif  (:p_order_date_high is NOT NULL ) then

      :lp_order_date := ' and h.ordered_date <= (:p_order_date_high+1) ' ;

    end if;

  /*-----------   Lexical parameter for the Schedule Date range    --------------------*/

    if (:p_schedule_date_low is NOT NULL ) AND (:p_schedule_date_high is NOT NULL )then

      :lp_schedule_date := ' and (l.schedule_ship_date between :p_schedule_date_low and (:p_schedule_date_high+1)) ' ;

    elsif  (:p_schedule_date_low is NOT NULL ) then

      :lp_schedule_date := ' and l.schedule_ship_date  >= :p_schedule_date_low ' ;

    elsif  (:p_schedule_date_high is NOT NULL ) then

      :lp_schedule_date := ' and l.schedule_ship_date <= (:p_schedule_date_high+1) ' ;

    end if;

  /*-----------   Lexical parameter for the Request Date range    --------------------*/

    if (:p_request_date_low is NOT NULL ) AND (:p_request_date_high is NOT NULL )then

      :lp_request_date := ' and (l.request_date between :p_request_date_low and (:p_request_date_high+1)) ' ;

    elsif  (:p_request_date_low is NOT NULL ) then

      :lp_request_date := ' and l.request_date  >= :p_request_date_low ' ;

    elsif  (:p_request_date_high is NOT NULL ) then

      :lp_request_date := ' and l.request_date <= (:p_request_date_high+1) ' ;

    end if;

  /*-----------   Lexical parameter for the Promise Date range    --------------------*/

    if (:p_promise_date_low is NOT NULL ) AND (:p_promise_date_high is NOT NULL )then

      :lp_promise_date := ' and (l.promise_date between :p_promise_date_low and (:p_promise_date_high+1)) ' ;

    elsif  (:p_promise_date_low is NOT NULL ) then

      :lp_promise_date := ' and l.promise_date  >= :p_promise_date_low ' ;

    elsif  (:p_promise_date_high is NOT NULL ) then

      :lp_promise_date := ' and l.promise_date  <= (:p_promise_date_high+1) ' ;

    end if;

/* Changed the ship_cust, bill_cust, del_cust to ship_party, bill_party, del_party for the bug# 2347438 */

/* Modified ship_party.customer_name to ship_party.party_name, this is done for all bill/del as well --bug 2425515 */

  /*-----------   Lexical parameter for the Customer Name  (Ship To) range    ---------*/

    

    if (:p_ship_to_customer_name_lo is NOT NULL ) AND (:p_ship_to_customer_name_hi is NOT NULL ) then

      :lp_ship_to_customer_name := ' and (ship_party.party_name between :p_ship_to_customer_name_lo and 

:p_ship_to_customer_name_hi) ';

    elsif (:p_ship_to_customer_name_lo is NOT NULL ) then

      :lp_ship_to_customer_name := ' and ship_party.party_name >= :p_ship_to_customer_name_lo ';

    elsif (:p_ship_to_customer_name_hi is NOT NULL ) then

      :lp_ship_to_customer_name := ' and ship_party.party_name <= :p_ship_to_customer_name_hi ';

    end if;

  /*-----------   Lexical parameter for the Customer Name  ( Bill To)range    ------------*/

    

    if (:p_bill_to_customer_name_lo is NOT NULL ) AND (:p_bill_to_customer_name_hi is NOT NULL ) then

      :lp_bill_to_customer_name := ' and (bill_party.party_name between :p_bill_to_customer_name_lo and 

:p_bill_to_customer_name_hi) ';

    elsif (:p_bill_to_customer_name_lo is NOT NULL ) then

      :lp_bill_to_customer_name := ' and bill_party.party_name >= :p_bill_to_customer_name_lo ';

    elsif (:p_bill_to_customer_name_hi is NOT NULL ) then 

      :lp_bill_to_customer_name := ' and bill_party.party_name <= :p_bill_to_customer_name_hi ';

    end if;

  /*---------   Lexical parameter for the Customer Name  ( Deliver To)range    ------------*/

    

    if (:p_del_to_customer_name_lo is NOT NULL ) AND (:p_del_to_customer_name_hi is NOT NULL ) then

      :lp_del_to_customer_name := ' and (del_party.party_name between :p_del_to_customer_name_lo and 

:p_del_to_customer_name_hi) ';

    elsif (:p_del_to_customer_name_lo is NOT NULL ) then

      :lp_del_to_customer_name := ' and del_party.party_name >= :p_del_to_customer_name_lo ';

    elsif (:p_del_to_customer_name_hi is NOT NULL ) then 

      :lp_del_to_customer_name := ' and del_party.party_name <= :p_del_to_customer_name_hi ';

    end if;

  /*-----------   Lexical parameter for the Order Number range    ------------------*/

    

    if (:p_order_num_low is NOT NULL ) AND (:p_order_num_high is NOT NULL ) then

      if ( :p_order_num_low = :p_order_num_high) then

          :lp_order_num := ' and h.order_number = :p_order_num_low ';

      else

          :lp_order_num := ' and (h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high)) ';

      end if;

    elsif (:p_order_num_low is NOT NULL ) then

      :lp_order_num := ' and h.order_number >= to_number(:p_order_num_low) ';

    elsif (:p_order_num_high is NOT NULL ) then

      :lp_order_num := ' and h.order_number <= to_number(:p_order_num_high) ';

    end if;

  /*-----------   Lexical parameter for Salesrep    --------------------*/

  

  if :p_salesrep is  NOT NULL then

    :lp_salesrep := ' and sr.name = :p_salesrep ' ;

  else 

    :lp_salesrep := null;

  end if ;

  if :p_created_by is NOT NULL then

    :lp_created_by := ' and u.user_name = :p_created_by ' ;

  else

    :lp_created_by := null;

  end if ;

/*------------- Lexical Parameter for the Open Orders Only

----------------------- */

  

    if :p_open_orders = 'Y' then

       :lp_open_orders := 

                    'and h.open_flag = ''Y''';

    else

    :lp_open_orders := null;

    end if;

/*_______________Lexical Parameter for Order Type ___________________________*/

   if :p_order_type is not null then

      :lp_order_type := ' and h.order_type_id = :p_order_type ';

   else

      :lp_order_type := null;

   end if;

/* --------------Lexical Parameter for order Category----------------------*/

if :p_order_num_low=:p_order_num_high then null; 

else

 if :p_order_category is not null then

   if :p_order_category = 'SALES' then

   :lp_order_category := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';

   elsif :p_order_category = 'CREDIT' then

   :lp_order_category := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';

   elsif :p_order_category = 'ALL' then

   :lp_order_category := null;

   end if;

 else

  :lp_order_category := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';

 end if;

end if;

/*----------------Lexical Parameter for Line Category ---------------------*/

if :p_line_category is not null then

   if :p_line_category = 'SALES' then

    :lp_line_category := 'and l.line_category_code = ''ORDER'' ';

   elsif :p_line_category = 'CREDIT' then

    :lp_line_category := 'and l.line_category_code = ''RETURN'' ';

   elsif :p_line_category = 'ALL' then

    :lp_line_category := null;

   end if;

 else

  :lp_line_category := 'and l.line_category_code = ''ORDER'' ';

 end if;

/*---------------Set User Language -------------------*/

select userenv('LANG')

into :p_user_lang

from dual;

END ;

  return (TRUE);

end;

sample formula column code

Code Snippets for Formula Column in Reports

FUNCTION CF_Display_Bundled_CompFormula

   RETURN CHAR

IS

   l_found   VARCHAR (10);

BEGIN

   srw.MESSAGE (100, 'XX: ENTER CF_Display_Bundled_CompFormula');

   SELECT DISTINCT 'Exists'

     INTO l_found

     FROM apps.oe_order_headers_all h,

          apps.oe_order_lines_all l,

          apps.mtl_system_items msi,

          apps.mtl_item_categories sp,

          apps.mtl_category_sets cs,

          apps.mtl_categories cat

    WHERE     h.order_number = :ORDER_NUMBER

          AND h.header_id = l.header_id

          AND cat.segment1 IN ('PACK', 'BUNDLE')

          AND l.inventory_item_id = msi.inventory_item_id

          AND msi.organization_id = 124 --:c_master_org1

          AND msi.Inventory_item_id = sp.Inventory_Item_ID

          --AND msi.Inventory_item_id =  :INVENTORY_ITEM_ID

          AND msi.Organization_id = sp.Organization_id

          AND SP.category_set_id = cs.category_set_id

          AND cs.category_set_name = 'XX PROCESSING'

          AND sp.category_id = cat.category_id

          AND l.line_number = :l_number;

   --

   IF l_found = 'Exists'

   THEN

      IF    :CF_ITEM_CATEGORY NOT IN ('PACK', 'BUNDLE')

         OR :CF_ITEM_CATEGORY IS NULL

      THEN

         RETURN ('NO');

      ELSE

         IF :CF_BUNDLE_TOP_LEVEL = 'YES'

         THEN

            RETURN ('YES');

         ELSE

            RETURN ('NO');

         END IF;

      END IF;

   ELSE

      RETURN ('YES');

   END IF;

--

EXCEPTION

   WHEN OTHERS

   THEN

      srw.MESSAGE (

         5000,

         'DEBUG:  exception CF_Display_Bundled_CompFormula.' || SQLERRM);

      RETURN ('YES');

END;

Oracle / PLSQL: TO_CHAR Function Number Formatting

Number Formatting

      Character       Example       Explanation            9               9999          Each character represents a place holder for digits.      0               999.00        Used for leading or trailing zeros.           $               $9999         prefixes the number with a dollar sign.           S               S9999         Useful for positive and negative numbers in query results.           PR              9999PR        Displays negative numbers in angle brackets.           D or .          99D99, 99.99  Places a decimal point in the place where the D or. is.           ,               9,999         Places a comma in the place where the , is.           RN or rn        RN            Displays roman numerals, in upper-or lowercase depending on the case of the format mask.           DATE            date          Assumes the number in a Julian date, and displays the resulting value in MM/DD/YY format.      

TO_CHAR(-12345.67, '99,999.99PR'): negative value in angle brackets

SQL>  SQL>  SQL>  SQL> SELECT TO_CHAR(-12345.67, '99,999.99PR') FROM dual;    TO_CHAR(-12  -----------  <12,345.67>    SQL>  

TO_CHAR(12345.67, '99999V99'): shift specified number of digits

SQL>  SQL>  SQL> SELECT TO_CHAR(12345.67, '99999V99') FROM dual;    TO_CHAR(  --------   1234567    SQL>  SQL>

TO_CHAR(12345.67, 'U99,999.99')

SQL>  SQL>  SQL> SELECT TO_CHAR(12345.67, 'U99,999.99') FROM dual;    TO_CHAR(12345.67,'U9  --------------------            $12,345.67    SQL>

Refer: http://www.java2s.com/Code/Oracle/Data-Type/TOCHAR1234567U9999999.htm

Oracle Pricing: Concept of Price List

concept of pricing

---------------

1. Items/ item categories/ services

2. Qualifiers

 a. determine eligibility rules

 b. determine who receives the price benefit

3. Modifiers

 a. Pricing actions that can be used to adjust the list price

The pricing engine determines the selling price for products and services. 

It provides the final selling price after retrieving the initial price from the price list or customer agreement. 

If this order qualifies for any increases or decreases from the initial price, 

adjustments called modifiers are applied to the price.

-------------------------------------------When the sales order is entered, the pricing engine is called and reads the relevant price list or pricing agreement. 

The appropriate modifiers are then applied to the base price and the final selling price is sent to the sales order line or the calling application. 

The Adjustments window displays the original price, applied modifiers, and the final price.

---------------------------------------------

Overview of the Pricing Engine

The pricing engine, which consists of a search engine and a calculation engine, works through open APIs to provide the pricing results to the calling application. 

From the pricing request, the pricing engine evaluates the appropriate modifiers and price lists, resolves incompatibility issues, retrieves the list price, and calculates the unit selling 

price and adjustments. 

The search engine receives pricing information from entities like price lists, modifiers, qualifiers, formulas, products, and pricing attributes.

--------------------------------------------------------

Overview of Pricing Security

In Oracle Applications, the basic level of security called functional security is implemented to control users' access to different windows, controls, and reports in the application. 

Additionally, Basic Pricing has its own security setup called pricing security that works along with functional security to provide a secure environment where the user can view, update, or 

add pricing entities within a framework of authorizations and permissions. Pricing entities are price lists, modifiers, and agreements. You can set up pricing security if you have the 

Oracle Pricing Administrator responsibility. 

Using pricing security, you can assign pricing entities to operating units, assign privileges, and set security rules for pricing entities.

----------------------------------------------------

Overview of Price Lists

A price list is useful for maintaining the prices and other pricing details of products and services. 

-> It serves as a repository of items with their related pricing details.

-> You can retrieve a price list and add/edit/delete related items and item categories. 

-> You can also use the price list to define attributes for the products that determine the pricing action.

-> An item should have a price when it is entered on a sales order line. 

-> Once the pricing engine populates the unit Price field by deriving the price from the appropriate price list, the order can be booked.

-> The header region of the price list consists of fields to store the price list 

name, 

the effective dates of the price list, 

currency, 

multi-currency conversion, 

pricing controls, 

rounding factor, 

and shipping defaults like freight terms and freight carriers.

-> You can enter one or more pricing lines to the price list. 

-> However you may add only one secondary price list. 

-> Pricing attributes can also be used to determine the price in the price list.

-> You can create as many price lists as needed by entering the information or copying existing price lists. 

-> The price lists can be modified to include new items or groups of items. 

-> The list price of the price list lines can be increased or decreased by either an amount or a percentage.

----------------------------------------------------

Overview of Formulas

-> You can define prices as constant values or create formulas to provide pricing flexibility and the ability to carry out complex computations. 

-> Some of the formulas that are created are based on the following formula component types:

Factor List: You can apply different factor values for varying item attributes

Numeric Constant: Numeric value

Pricing Context: Grouping of pricing attributes, of which a maximum of 100 can be incorporated in the formula

Product Context: Which contains only one attribute, item

-> You can create formulas in the Pricing Formulas window. 

-> Basic Pricing provides seeded formulas, such as the Freight and Special Charges Formulas, which includes the Cost to Charge Conversion formula and Cost to Charge Markup formula. 

-> These formulas convert freight costs to freight charges.

------------------------------------------------------

Overview of Qualifiers

-> A qualifier defines eligibility rules for modifiers and price lists. 

-> A qualifier can be a customer name, a customer class, an order type, or an order amount that can span orders. 

-> Though a qualifier may be set up independently, it comes into effect only when linked to a modifier or a price list. 

-> Thus, the modifier that is set up and linked to a qualifier determines who the price adjustment will affect as well as the amount that will change. 

-> You can create qualifier groups to apply individual qualifiers as a set. When you group qualifiers, they become a set, which allows for faster searching and setup.

------------------------------------------------------

Concurrent Program output through email

Oracle Applications : Concurrent Program output through email

11/21/2016 10:44:00 PM

Sekhar Byna

Refer: http://ebiztechnics.blogspot.in/2016/11/oracle-applications-concurrent-program-output-trhough-email.html

How to send output of concurrent program to email?

 Set the profile options "FND:SMTP Host" and "FND:SMTP Port" by providing the right values 
---------------------------------------------------------------
a. Log into System Administrator responsibility.
b. Navigate to Profile - System.
c. Query up the %SMTP% profiles.
d. Set the following profile values to the defined host and port:

FND:SMTP Host
FND:SMTP Port

Example
FND:SMTP Host = mail.ebiztechnics.com
FND:SMTP Port = 25

e. Resubmit request and monitor email delivery. 

Add Concurrent Program to Request Group Using api fnd_program.add_to_group

Use the below PLSQL for  Adding Concurrent Program to Request Group Using api fnd_program.add_to_group

BEGIN

BEGIN

BEGIN

apps.fnd_program.remove_from_group

  (PROGRAM_SHORT_NAME  => 'XXCUST_CP',

PROGRAM_APPLICATION => 'XXCUST',

REQUEST_GROUP       => 'XXCUST Request Group',

GROUP_APPLICATION   => 'XXCUST'

 ); 

commit;

EXCEPTION WHEN OTHERS

THEN

NULL;

END;

fnd_program.add_to_group(

PROGRAM_SHORT_NAME  =>  'XXCUST_CP',

PROGRAM_APPLICATION =>  'XXCUST',

REQUEST_GROUP       =>'XXCUST Request Group',

GROUP_APPLICATION   =>  'XXCUST'

 );     

commit; 

END;

END;

/

What is SQL Loader with Examples

Reference: http://erporacleapps.blogspot.in/2010/04/what-is-sql-loader-with-examples.html

What is SQL Loader with Examples

What is SQL Loader

SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called a

 log file, bad file or reject file, discard file.

• Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
• Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.
• Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

What is Structure of the data file:

The data file can be in fixed record format or variable record format.

Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

7369 SMITH      CLERK        7902  12/17/1980         800                  

7499 ALLEN      SALESMAN  7698  2/20/1981           1600    

7521 WARD      SALESMAN  7698  2/22/1981           1250    

7566 JONES      MANAGER   7839  4/2/1981             2975             

7654 MARTIN    SALESMAN  7698  9/28/1981           1250    

7698 BLAKE      MANAGER   7839  5/1/1981             2850             

7782 CLARK      MANAGER   7839  6/9/1981             2450             

7788 SCOTT      ANALYST    7566  12/9/1982           3000             

7839 KING        PRESIDENT          11/17/1981         5000             

7844 TURNER    SALESMAN  7698  9/8/1981            1500           

7876 ADAMS     CLERK         7788  1/12/1983          1100             

7900 JAMES      CLERK         7698  12/3/1981          950              

7902 FORD        ANALYST     7566  12/3/1981          3000            

7934 MILLER     CLERK         7782  1/23/1982          1300            

Variable Record Format would like below where the data fields are separated by a delimiter.

Note: The Delimiter can be anything you like. In this case it is "|"

1196700|9|0|692.64

1378901|2|3900|488.62

1418700|2|2320|467.92

1418702|14|8740|4056.36

1499100|1|0|3.68

1632800|3|0|1866.66

1632900|1|70|12.64

1637600|50|0|755.5

Structure of a Control file:

Sample CTL file for loading a Variable record data file:

OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading

LOAD DATA

INFILE '$FILE'             -- Specify the data file path and name

APPEND                       -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE

INTO TABLE "APPS"."BUDGET"   -- the table to be loaded into

FIELDS TERMINATED BY '|'           -- Specify the delimiter if variable format datafile

 OPTIONALLY ENCLOSED BY '"'   --the values of the data fields may be enclosed in "

TRAILING NULLCOLS     -- columns that are not present in the record treated as null

  (ITEM_NUMBER    "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns

  QTY                 DECIMAL EXTERNAL,

  REVENUE             DECIMAL EXTERNAL,

  EXT_COST            DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))"  ,

  MONTH           "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,

DIVISION_CODE    CONSTANT "AUD"  -- Can specify constant value instead of

                                                                          Getting value from datafile

   )

OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n -- Number of logical records to skip (Default 0)

LOAD = n -- Number of logical records to load (Default all)

ERRORS = n -- Number of errors to allow (Default 50)

ROWS = n   -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)

BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- Suppress messages during run

                (header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} --Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} -- Perform parallel load (Default FALSE)

LOAD DATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location of the datafile or datafiles.

INFILE * specifies that the data is found in the control file and not in an external file. INFILE '$FILE', can be used to send the filepath and filename as a parameter when registered as a concurrent program.

INFILE   '/home/vision/kap/import2.csv' specifies the filepath and the filename.

Example where datafile is an external file:

LOAD DATA

INFILE   '/home/vision/kap/import2.csv'

INTO TABLE kap_emp

FIELDS TERMINATED BY ","

( emp_num, emp_name, department_num, department_name )

Example where datafile is in the Control file:

LOAD DATA

INFILE *

INTO TABLE kap_emp

FIELDS TERMINATED BY ","              

( emp_num, emp_name, department_num, department_name )

BEGINDATA

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

Example where file name and path is sent as a parameter when registered as a concurrent program

LOAD DATA

INFILE '$FILE'

INTO TABLE kap_emp

FIELDS TERMINATED BY ","              

( emp_num, emp_name, department_num, department_name )

TYPE OF LOADING:

INSERT   -- If the table you are loading is empty, INSERT can be used.

APPEND  -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.

REPLACE -- All rows in the table are deleted and the new data is loaded

TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.

INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE "APPS"."BUDGET", APPS refers to the Schema and BUDGET is the Table name.

FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.

TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

How to Loading a fixed format data file:

LOAD DATA

INFILE 'sample.dat'

INTO TABLE emp

(      empno         POSITION(01:04)   INTEGER EXTERNAL,

       ename          POSITION(06:15)   CHAR,

       job            POSITION(17:25)   CHAR,

       mgr            POSITION(27:30)   INTEGER EXTERNAL,

       sal            POSITION(32:39)   DECIMAL EXTERNAL,

       comm           POSITION(41:48)   DECIMAL EXTERNAL,

       deptno         POSITION(50:51)   INTEGER EXTERNAL)

What are Steps to Run the SQL* LOADER from UNIX:

At the prompt, invoke SQL*Loader as follows:

      sqlldr USERID=scott/tiger CONTROL= LOG=

      name>

SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

How to Register as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.

Define the Executable. Give the Execution Method as SQL*LOADER.

Define the Program. Add the Parameter for FILENAME.

 How to Skip columns:

You can skip columns using the 'FILLER' option.

Load Data
--
--
--
TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)

here the column name will be skipped.