DBA Tips Archive for Oracle

  


Load Text Files to CLOB / Multi-Byte Characterset DB - (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 note provides a solution for anyone trying to load text data (often XML) into a multi-byte character set database using the DBMS_LOB.LoadFromFile() PL/SQL API. I first ran into this when reading Steve Muench's book "Building Oracle XML Applications". (A book I highly recommend) Many of the example in his book use the DBMS_LOB.LoadFromFile() PL/SQL procedure for loading XML files into a CLOB column. However, in my case, the database I was using was UTF8 - a multi-byte UNICODE character set. After attempting to unload or view the data, all I got back was corrupt data - each character being an upside-down question mark (¿). I later found out that the DBMS_LOB.LoadFromFile() API did not support multi-byte data. My problems went away when I tried the same upload on another database with a single-byte WEISO5589P1 character set.

To solve this, Oracle introduced a new procedure in Oracle 9.2 called DBMS_LOB.LoadCLOBFromFile() to properly convert character sets (UCS-2) to a multi-byte character set.

Below, you will find a PL/SQL procedure, Load_CLOB_From_XML_File, that uses both PL/SQL procedures for loading an XML document into a UTF8 database. I then query the data to show the effects of using both methods to load CLOB data.



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 example procedure:

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', 'DatabaseInventory.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, WE WILL USE THE 
    -- DBMS_LOB.LoadFromFile() API WHICH *DOES NOT* SUPPORT MULTI-BYTE
    -- CHARACTER SET DATA.
    -- -----------------------------------------------------------------------

    INSERT INTO test_clob(id, file_name, xml_file, timestamp) 
        VALUES(1001, 'DatabaseInventory.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.LoadFromFile(
          DEST_LOB => dest_clob
        , SRC_LOB  => src_clob
        , AMOUNT   => DBMS_LOB.GETLENGTH(src_clob)
    );

    DBMS_LOB.CLOSE(src_clob);

    COMMIT;

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


    -- -----------------------------------------------------------------------
    -- THE FOLLOWING BLOCK OF CODE WILL ATTEMPT TO INSERT / WRITE THE CONTENTS
    -- OF AN XML FILE TO A CLOB COLUMN. IN THIS CASE, WE WILL USE THE NEW 
    -- DBMS_LOB.LoadCLOBFromFile() API WHICH *DOES* SUPPORT MULTI-BYTE
    -- CHARACTER SET DATA.
    -- -----------------------------------------------------------------------

    INSERT INTO test_clob(id, file_name, xml_file, timestamp) 
        VALUES(1002, 'DatabaseInventory.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=1002).');

END;
/

SQL> @load_clob_from_xml_file.sql Procedure created.



Test

SQL> set serveroutput on
SQL> exec Load_CLOB_From_XML_File
Loaded XML File using DBMS_LOB.LoadFromFile: (ID=1001).
Loaded XML File using DBMS_LOB.LoadCLOBFromFile: (ID=1002).

PL/SQL procedure successfully completed.


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

        ID     LENGTH
---------- ----------
      1001       1028
      1002       2056


SQL> set long 8000
SQL> set pagesize 9000

SQL> SELECT id, xml_file FROM test_clob ORDER BY id;

        ID
----------
XML_FILE
--------------------------------------------------------------------------------
      1001
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿

      1002
<?xml version="1.0"?>

<!DOCTYPE DatabaseInventory>

<DatabaseInventory>

  <DatabaseName>
    <GlobalDatabaseName>production.iDevelopment.info</GlobalDatabaseName>
    <OracleSID>production</OracleSID>
    <DatabaseDomain>iDevelopment.info</DatabaseDomain>
    <Administrator EmailAlias="jhunter" Extension="6007">Jeffrey Hunter</Adminis
trator>
    <DatabaseAttributes Type="Production" Version="9i"/>
    <Comments>
      The following database should be considered the most stable for
      up-to-date data. The backup strategy includes running the database
      in Archive Log Mode and performing nightly backups. All new accounts
      need to be approved by the DBA Group before being created.
    </Comments>
  </DatabaseName>

  <DatabaseName>
    <GlobalDatabaseName>development.iDevelopment.info</GlobalDatabaseName>
    <OracleSID>development</OracleSID>
    <DatabaseDomain>iDevelopment.info</DatabaseDomain>
    <Administrator EmailAlias="jhunter" Extension="6007">Jeffrey Hunter</Adminis
trator>
    <Administrator EmailAlias="mhunter" Extension="6008">Melody Hunter</Administ
rator>
    <DatabaseAttributes Type="Development" Version="9i"/>
    <Comments>
      The following database should contain all hosted applications. Production
      data will be exported on a weekly basis to ensure all development environm
ents
      have stable and current data.
    </Comments>
  </DatabaseName>

  <DatabaseName>
    <GlobalDatabaseName>testing.iDevelopment.info</GlobalDatabaseName>
    <OracleSID>testing</OracleSID>
    <DatabaseDomain>iDevelopment.info</DatabaseDomain>
    <Administrator EmailAlias="jhunter" Extension="6007">Jeffrey Hunter</Adminis
trator>
    <Administrator EmailAlias="mhunter" Extension="6008">Melody Hunter</Administ
rator>
    <Administrator EmailAlias="ahunter">Alex Hunter</Administrator>
    <DatabaseAttributes Type="Testing" Version="9i"/>
    <Comments>
      The following database will host more than half of the testing
      for our hosting environment.
    </Comments>
  </DatabaseName>

</DatabaseInventory>

  


Copyright (c) 1998-2017 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:53:17 EST
Page Count: 66705