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;

Testing:

Step1: Create a DB Directory pointing the physical location of file

clip_image001
Step2:  Place the file to be copied to CLOB in the desired location
clip_image003
clip_image004
Step3: Run the sample script to verify the content of the file
clip_image005
Happy Sharing Smile Enjoy Learning Smile

0 Responses to “DBMS_XSLPROCESSOR.READ2CLOB Example Script to Read a file data into CLOB”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.