Oracle Write a Blob to file

 CREATE OR REPLACE PROCEDURE APPS.write_blob_to_file (
   --p_entity_name   IN   VARCHAR2,
   p_pk1_value     IN   VARCHAR2
)
AS
   v_lob_loc       BLOB;
   v_buffer        RAW (32767);
   v_buffer_size   BINARY_INTEGER;
   v_amount        BINARY_INTEGER;
   v_offset        NUMBER (38)        := 1;
   v_chunksize     INTEGER;
   v_out_file      UTL_FILE.file_type;
   v_file_name     VARCHAR2 (1000);
   
   Cursor Cur IS
     SELECT  fd.media_id, flb.file_data v_lob_loc, flb.file_name v_file_name
     FROM     fnd_lobs flb, 
             fnd_attached_documents fad,
             fnd_documents fd
     WHERE 1=1
     AND fad.entity_name = 'RA_CUSTOMER_TRX'
     AND fad.pk1_value = p_pk1_value
     AND fad.document_id = fd.document_id
     AND fd.media_id = flb.file_id;
BEGIN
-- +-------------------------------------------------------------+
-- | SELECT THE LOB LOCATOR                                      |
-- +-------------------------------------------------------------+
  
DBMS_OUTPUT.put_line('File Location'||p_pk1_value);
-- +-------------------------------------------------------------+
 -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN                  |
 -- +-------------------------------------------------------------+
 FOR c1 in Cur
    LOOP
   v_chunksize := DBMS_LOB.getchunksize (c1.v_lob_loc);
   IF (v_chunksize < 32767)
   THEN
      v_buffer_size := v_chunksize;
   ELSE
      v_buffer_size := 32767;
   END IF;
   v_amount := v_buffer_size;
-- +-------------------------------------------------------------+
-- | OPENING THE LOB IS OPTIONAL                                 |
-- +-------------------------------------------------------------+
   DBMS_LOB.OPEN (c1.v_lob_loc, DBMS_LOB.lob_readonly);
-- +-------------------------------------------------------------+
-- | WRITE CONTENTS OF THE LOB TO A FILE                         |
-- +-------------------------------------------------------------+
   v_out_file :=
      UTL_FILE.fopen
         (LOCATION          => '/global/erpgrp/cfs02/EBSPROD_cmlogs/conc/upload',
          filename          => c1.v_file_name,
          open_mode         => 'wb',
          max_linesize      => 32767
         );
   WHILE v_amount >= v_buffer_size
   LOOP
      DBMS_LOB.READ (lob_loc      => c1.v_lob_loc,
                     amount       => v_amount,
                     offset       => v_offset,
                     buffer       => v_buffer
                    );
      v_offset := v_offset + v_amount;
      UTL_FILE.put_raw (FILE           => v_out_file,
                        buffer         => v_buffer,
                        autoflush      => TRUE
                       );
      UTL_FILE.fflush (FILE => v_out_file);
   END LOOP;
   UTL_FILE.fflush (FILE => v_out_file);
   UTL_FILE.fclose (v_out_file);
-- +-------------------------------------------------------------+
-- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
-- +-------------------------------------------------------------+
   DBMS_LOB.CLOSE (c1.v_lob_loc);
   END LOOP;
   
EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line('No file found with Given Entity Name and PK1_Value'||SQLERRM);   
   
END write_blob_to_file;
/

SQL*Loader Concurrent Program Oracle Apps R12

Oracle R12 Register SQL*Loader as a Concurrent Program

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.

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. 

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:

Added the Concurrent Program to a Custom Responsibility through a Request Group.

Step 6:

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

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 or you set in control file .

Note:

Bad file:  The bad file contains rows that were rejected because of errors.

Discard file:  The discard file contains rows that were discarded because they were filtered out  control file.