DBA Tips Archive for Oracle

  


Load Text Files to CLOB then Write Back Out to Disk - (PL/SQL)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Example
  3. Test



Overview

The following example is part of the Oracle LOB Examples Collection.

This example provides two PL/SQL procedures that demonstrate how to populate a CLOB column with a text file (an XML file) then write it back out to the file system as a different file name.

Load_CLOB_From_XML_File

This PL/SQL procedure loads an XML file on disk to a CLOB column using a BFILE reference variable. Notice that I use the new PL/SQL procedure DBMS_LOB.LoadCLOBFromFile(), introduced in Oracle 9.2, that handles uploading to a multi-byte UNICODE database.
Write_CLOB_To_XML_File
This PL/SQL procedure writes the contents of the CLOB column in the database piecewise back to the file system.



Example

Let's first take a look at an example XML file:

After downloading the above XML file, create all Oracle database objects:

DROP TABLE test_clob CASCADE CONSTRAINTS
/

Table dropped.


CREATE TABLE test_clob (
      id           NUMBER(15)
    , file_name    VARCHAR2(1000)
    , xml_file     CLOB
    , timestamp    DATE
)
/

Table created.


CREATE OR REPLACE DIRECTORY
    EXAMPLE_LOB_DIR
    AS
    '/u01/app/oracle/lobs'
/

Directory created.


Now, let's define our two example procedures:

load_clob_from_xml_file.sql
CREATE OR REPLACE PROCEDURE Load_CLOB_From_XML_File
IS

    dest_clob   CLOB;
    src_clob    BFILE  := BFILENAME('EXAMPLE_LOB_DIR', 'DatabaseInventoryBig.xml');
    dst_offset  number := 1 ;
    src_offset  number := 1 ;
    lang_ctx    number := DBMS_LOB.DEFAULT_LANG_CTX;
    warning     number;

BEGIN

    DBMS_OUTPUT.ENABLE(100000);

    -- -----------------------------------------------------------------------
    -- THE FOLLOWING BLOCK OF CODE WILL ATTEMPT TO INSERT / WRITE THE CONTENTS
    -- OF AN XML FILE TO A CLOB COLUMN. IN THIS CASE, I WILL USE THE NEW 
    -- DBMS_LOB.LoadCLOBFromFile() API WHICH *DOES* SUPPORT MULTI-BYTE
    -- CHARACTER SET DATA. IF YOU ARE NOT USING ORACLE 9iR2 AND/OR DO NOT NEED
    -- TO SUPPORT LOADING TO A MULTI-BYTE CHARACTER SET DATABASE, USE THE
    -- FOLLOWING FOR LOADING FROM A FILE:
    -- 
    --     DBMS_LOB.LoadFromFile(
    --         DEST_LOB => dest_clob
    --       , SRC_LOB  => src_clob
    --       , AMOUNT   => DBMS_LOB.GETLENGTH(src_clob)
    --     );
    --
    -- -----------------------------------------------------------------------

    INSERT INTO test_clob(id, file_name, xml_file, timestamp) 
        VALUES(1001, 'DatabaseInventoryBig.xml', empty_clob(), sysdate)
        RETURNING xml_file INTO dest_clob;


    -- -------------------------------------
    -- OPENING THE SOURCE BFILE IS MANDATORY
    -- -------------------------------------
    DBMS_LOB.OPEN(src_clob, DBMS_LOB.LOB_READONLY);

    DBMS_LOB.LoadCLOBFromFile(
          DEST_LOB     => dest_clob
        , SRC_BFILE    => src_clob
        , AMOUNT       => DBMS_LOB.GETLENGTH(src_clob)
        , DEST_OFFSET  => dst_offset
        , SRC_OFFSET   => src_offset
        , BFILE_CSID   => DBMS_LOB.DEFAULT_CSID
        , LANG_CONTEXT => lang_ctx
        , WARNING      => warning
    );

    DBMS_LOB.CLOSE(src_clob);

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Loaded XML File using DBMS_LOB.LoadCLOBFromFile: (ID=1001).');

END;
/

SQL> @load_clob_from_xml_file.sql Procedure created.


write_clob_to_xml_file.sql
CREATE OR REPLACE PROCEDURE Write_CLOB_To_XML_File
IS

  clob_loc          CLOB;
  buffer            VARCHAR2(32767);
  buffer_size       CONSTANT BINARY_INTEGER := 32767;
  amount            BINARY_INTEGER;
  offset            NUMBER(38);

  file_handle       UTL_FILE.FILE_TYPE;
  directory_name    CONSTANT VARCHAR2(80) := 'EXAMPLE_LOB_DIR';
  new_xml_filename  CONSTANT VARCHAR2(80) := 'DatabaseInventoryBig_2.xml';

BEGIN

    DBMS_OUTPUT.ENABLE(100000);

    -- ----------------
    -- GET CLOB LOCATOR
    -- ----------------
    SELECT xml_file INTO clob_loc
    FROM   test_clob
    WHERE  id = 1001;


    -- --------------------------------
    -- OPEN NEW XML FILE IN WRITE MODE
    -- --------------------------------
    file_handle := UTL_FILE.FOPEN(
        location     => directory_name,
        filename     => new_xml_filename,
        open_mode    => 'w',
        max_linesize => buffer_size);

    amount := buffer_size;
    offset := 1;

    -- ----------------------------------------------
    -- READ FROM CLOB XML / WRITE OUT NEW XML TO DISK
    -- ----------------------------------------------
    WHILE amount >= buffer_size
    LOOP

        DBMS_LOB.READ(
            lob_loc    => clob_loc,
            amount     => amount,
            offset     => offset,
            buffer     => buffer);

        offset := offset + amount;

        UTL_FILE.PUT(
            file      => file_handle,
            buffer    => buffer);

        UTL_FILE.FFLUSH(file => file_handle);

    END LOOP;

    UTL_FILE.FCLOSE(file => file_handle);

END;
/

SQL> @write_clob_to_xml_file.sql Procedure created.



Test

SQL> set serveroutput on


SQL> exec Load_CLOB_From_XML_File
Loaded XML File using DBMS_LOB.LoadCLOBFromFile: (ID=1001).

PL/SQL procedure successfully completed.



SQL> exec Write_CLOB_To_XML_File

PL/SQL procedure successfully completed.



SQL> SELECT id, DBMS_LOB.GETLENGTH(xml_file) Length FROM test_clob;

        ID     LENGTH
---------- ----------
      1001      41113


SQL> host ls -l DatabaseInventory*
-rw-r--r--   1 oracle   dba        41113 Sep 20 15:02 DatabaseInventoryBig.xml
-rw-r--r--   1 oracle   dba        41113 Sep 20 15:48 DatabaseInventoryBig_2.xml

  


Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 23-Jan-2006 08:52:23 EST
Page Count: 67599