DBMS_XSLPROCESSOR.READ2CLOB Example Script to Read a file data into CLOB ~ Oracle Apps Knowledge Sharing

Sunday, May 24, 2015

DBMS_XSLPROCESSOR.READ2CLOB Example Script to Read a file data into CLOB

In our current project, we have a requirement for reading a data stored in physical file to a CLOB column. We found a standard ORACLE API DBMS_XSLPROCESSOR.READ2CLOB

In this post, we have tried to explain the same API with an example. We hope it helps.

API Specification:

DBMS_XSLPROCESSOR.READ2CLOB(

   flocation     IN   VARCHAR2,

   fname         IN   VARCHAR2,

   csid          IN   NUMBER:=0)

RETURN CLOB;

Script:

SET SERVEROUTPUT ON:

DECLARE

   -- CLOB data

   lv_clob                 CLOB           DEFAULT NULL;

   -- Database directory for a physical location

   lv_utl_file_directory   VARCHAR2 (100) DEFAULT 'TEST_DB_DIRECTORY';

   --Input Filename

   lv_file_name            VARCHAR2 (100) DEFAULT 'shareoracleapps.txt';

BEGIN

   lv_clob :=

      DBMS_XSLPROCESSOR.read2clob (flocation      => lv_utl_file_directory,

                                   fname          => lv_file_name

                                  );

   DBMS_OUTPUT.put_line (lv_clob);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error :' || SQLERRM);

END;