Help - Search - Members - Calendar
Full Version: insert xml file into db table
Oracle DBA Forums > Oracle > Oracle Forum
yairk30
Hey all !
I have a procedure that supose to load data from an xml file into a db (9i on windows xp). it goes like this:

CREATE OR REPLACE
procedure xml_read_file (in_filename in varchar2)
is
my_dir varchar2(10) := 'XML_DIR';

l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(1000);
v_emp_id number(10);
v_emp_name varchar2(50);
v_birthday date;
src_csid NUMBER := NLS_CHARSET_ID('UTF8');
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
lang_context INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;

BEGIN

l_bfile := BFileName(my_dir, in_filename);
dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);

dbms_lob.loadclobfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile), dest_offset,src_offset, src_csid, lang_context, warning);

dbms_lob.close(l_bfile);

-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_DATE_FORMAT','''DD/MM/RR HH24:MI:SS''');


-- Create a parser.
l_parser := dbms_xmlparser.newParser;

-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);


-- Free resources associated with the CLOB and Parser now they are no longer needed.
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);

-- Get a list of all the nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/employees');

-- Loop through the list and create a new record in a tble collection
-- for each record.
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP

l_n := dbms_xmldom.item(l_nl, cur_emp);

-- Use XPATH syntax to assign values to he elements of the collection.
dbms_xslprocessor.valueOf(l_n,'emp_id/text()',v_emp_id);
dbms_xslprocessor.valueOf(l_n,'emp_name/text()',v_emp_name);
dbms_xslprocessor.valueOf(l_n,'birthday/text()',v_birthday);


insert into emp(emp_id , emp_name , birthday)
values(v_emp_id , v_emp_name , v_birthday)

END LOOP;



-- Free any resources associated with the document now it
-- is no longer needed.
dbms_xmldom.freeDocument(l_doc);

--remove file to another directory

commit;
EXCEPTION
WHEN OTHERS THEN
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
ROLLBACK;
END;
/

Its goes ok but doesnt preform the inserting stuff.

While monitoring in , using dbms's , i've noticed that it stops at the

dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
line.
In other words, i can't open the file.
i have to say that similar procedure with another xml file , table and server used to work fine.

Any ideas????

Thanks , Yair
andrew kerber
Did you check the protections on the file?
Do you have the file name right?
HAL9000
Here is a working example:

http://www.idevelopment.info/data/Oracle/D...s/LOBS_40.shtml

CODE
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;
/
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.