Hello Experts,
I have an issue with populating a schema based XMLTYPE table with BIFILES pulled from a UNIX directory.  The below is my strategy.

First I created a regular table to hold BFILE attributes, then I created a controlling procedure that gets the directory name from sys.dba_directories, passes the name to a pl/sql wrapper that calls a java stored procedure that empties the table, then traverses the folder contents inserting the BFILE attributes.

When control is returned to the polling program, in inline cursor FOR LOOP traverses the table passing the file_name attribute to the INSERT statement.

The first table populates fine, it appears the FOR LOOP is breaking as only 173 0f 716 files are being inserted into the XMLTYPE table. For the life of me I cannot figure out why.

My code is below, and hopefully an expert will give me guidance and wisdom in my efforts to accomplish this task.
-- First table Create statement
  FILE_SIZE      NUMBER(15),
--Polling Procedure
CREATE OR REPLACE PROCEDURE fadsadmin.poll_4_fads_xml_data
   v_dirname   VARCHAR2 (255) := NULL;
   SELECT directory_path
   INTO v_dirname
   FROM sys.dba_directories
   WHERE directory_name = 'FADSNEWDOCSDIR';
   get_fads_dir_contents (v_dirname);
   -- Open in-line cursor to do the remove file immediately after the insert commit;
             FROM fads_dir_contents
             WHERE file_type = 'F')
      INSERT INTO meta_docs
      VALUES (
                xmltype (BFILENAME ('FADSNEWDOCSDIR', x.file_name),
                         NLS_CHARSET_ID ('AL32UTF8')
      -- cleanup the directory
      UTL_FILE.fremove ('FADSNEWDOCSDIR', x.file_name);
      fads_error.handleallfads (FALSE);
END poll_4_fads_xml_data;
--PL/SQL Wrapper Calling Java Stored Procedure
CREATE OR REPLACE PROCEDURE get_fads_dir_contents (p_directory IN varchar2)
   NAME 'FadsDirListing.getContentsList(java.lang.String)';
-Java Stored Procedure
   AS import*;
import java.sql.*;
public class FadsDirListing
    public static void getContentsList(String directory) throws SQLException
       File[] files = new File(directory).listFiles();
       for(int i = 0; (files != null) && (i < files.length); ++i)
           File f = files[i];
           String    filName = f.getName();
           long      filSize = f.length();
           Timestamp modDate = new Timestamp(f.lastModified());
           String    filType = f.isDirectory() ? "D" : "F";
           #sql { INSERT 
                  VALUES (:filName, :filSize, :filType, :modDate)
           #sql { COMMIT

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What returns the query

             FROM fads_dir_contents
             WHERE file_type = 'F'

Also catch the exeception in clear way:
set serveroutput on
     dbms_output.put_line('Errors  encountered.');

Also your java programa can not catch exception: try ...catch
dcfreyerAuthor Commented:
Thank you for your reply and interest.

Is your question what is returned by the query?  If so, the returns are the BFILE attributes gathered by the Java.  The file_type will either be "F" for file or "D" for directory.

Also as an experiment, I tried rewriting poll_4_fads_xml_data to use BULK COLLECT and FORALL.  However, I am beginning to believe this is not workable when working with XMLTYPE tables - unless an expert tells me otherwise

The Java program works flawlessly (it has never thrown an exception), and is so rudimentary that a try ..catch, I felt was not necessary.  However, I will add just for insurance - you never know what is going to happen.

The EXCEPTION handler did not catch errors at all.  The clause was changed as follows:
          WHEN OTHERS
        dbms_output.put_line('ORA Error Number ' || err_num );
        dbms_output.put_line('ORA Error message ' || err_msg);
        dbms_output.put_line('On inserting file: '||x.file_name);

err_num and err_msg are defined in DECLARE as  err_num NUMBER := SQLCODE;
   err_msg VARCHAR2(512) := SQLERRM;

Agaom thnk you for the assist
slightwv (䄆 Netminder) Commented:
Are the first 173 being loaded properly?  I haven't messed with BFILEs much and was curious about the BFILENAME call in the insert statement.  From the little I read in the docs, BFILENAME doesn't return LOB data so I'm not sure what is getting loaded.

I've done a lot with XMLTYPE and LOB data including loading, converting and general moving/updating.  Just a little weak on BFILEs.

All that said:
I would take the path schwertner was sending you down and add a little error checking.  What I've done with our apps is set up a nested PL/SQL block.

I first load the LOB into a straight CLOB.  I then enter the nested block with a custom exception handler.  If the CLOB fails to convert into an XMLTYPE, I drop out and insert it into an errors table then move on to the next one.  This way, it never errors out.

If you can confirm BFILENAME is actually loading valid data and provide me with a table definition for media_docs and fads_dir_contents and a row or 2 of sample data for  fads_dir_contents I'll attempt to come up with a small working testcase.

If I missed it I apologize but I would also like your specific database version with all 4 numbers ( example: )
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dcfreyerAuthor Commented:
I apologize for not answering your post until now.

Our database version is  Efforts to upgrade are met with push-back from the customer, as they are fearful of change. This alone could be the subject of a book.

The meta_docs table is a schema based XMLTYPE table.  The parent schema is composed of its own types,  4 includes and 1 import.  Unfortunately I cannot pass them to you, as they are classified. as is the database.  Which makes creating a test case outside our firewall "verboten".

I realize this constrains your willingness to analyze the issue and provide assistance, and I apologize.

The table definition for fads_dir_contents is contained in the code snippet in my original post.   I have since added an exception handler to the poll_4_,fad_xml_data main procedure, as the original handler (contained in my second post) did not throw an exception at all.  Neither does the new one.  All this is why I am pleading for Expert guidance.
slightwv (䄆 Netminder) Commented:
Having dealt with secure/proprietary systems pretty much my entire career I find that problems can normally be broken down into core components and test cases can be created that in no way leak sensitive information.

I can guess at a table def for meta_docs.  I haven't done much with attaching a schema directly to an xmltype table so a mockup might help a little.   If you are strong in creating an XML schema and can create a simple one along with a simple table def that relates to but is not close to production, it would help a lot.  Something as simple as a schema and table def for:

Back to the problem:  Loops just don't fail.  If the code doesn't throw an exception it might be a bug.  Check the alert log for any fatal errrors.  Look for trace files that might be related.

You also didn't mention if the first 172 files load properly.  I'm still not sure how BFILENAME accesses the LOB but I haven't created a test case on my end yet.
dcfreyerAuthor Commented:
Yes, the 172 BFILES loaded properly and were validated as per the XMLTYPE table.

Unfortunately, I cannot work on FADS until tomorrw as I was handed a Pri 1 application fault that must be resolved, tested, and patched to production by morning. Hopefully RedBull is not part of this exercise.

I was planning to work the exception handler tomorrow to determin why SQLERR is not picking up the failure.  I will post my code snippet and test results if you would like.

As per my understanding the BFILENAME function initializes the LOB column and returns the BFILE locator for the physical LOB binary file.  The rest is easy. I guess that is why a simple cursor for loop is causing me so much angst.

Anyway, I very much appreciate yoiur interest in helping me find the answer so I can fix.  It is professionals, like yourself, within this forum that make it a valuable "almost last resort" resource.  



slightwv (䄆 Netminder) Commented:
No problem.  I work on your schedule.  The reasons you provided are why I'm a member of this site.  The other reason: I end up learning more than I ever teach.  For example:  I'll need to play around a little with BFILENAME.  If I get a little time today, I'll try to set up my own test case.

Something I just thought of:  How large are the files you're dealing with?  In later versions of 10g, Oracle XML has a problem with larger files.  My production is and I have problems anytime I start messing with XML files in the 2 to 4 meg range.  Every once in a while, the database sort of gives up and refuses to work with them.

When you get time:  You might want to see if it ALWAYS fails on the same file.  This can narrow down the problem.

Just started testing 11g so I'm not sure if this feature has been fixed or not.
dcfreyerAuthor Commented:
A fully populated meta_doc record is just under 100K.  I changed the polling procedure a bit to get and record the file name before the insert and after a successful COMMIT.  The short answer is "NO", the INSERT does not fail at the same file.

I am befinning to wonder if SharedMemory, i.e., SQL Work Area is hiccuping when a memory level is reached.  This should not happen as BFILENAME releases the memory after each commit.  

I will again look to ensure that each XML Instance file has the same namespace declarations and in the same order.

With respect to namespaces, I may add the fully qualified namespace for the schema.
For example xsi:SchemaLocation="urn:us:gov:ic:doc Harmony-DOCUMENT.xsd" then test again, after again checking each instance document.

Again, thank you for your professionalism.


The XML Schema  and XML Instance document declarations section are as follows;
<xsd:schema xmlns:xsd="" xmlns="urn:us:gov:ic:doc" xmlns:sec="urn:us:gov:ic:sec" xmlns:sch="" xmlns:xdb=""
elementFormDefault="qualified" version="2.2.0"
  <xsd:documentation xml:lang="en">W3C XML XSD 2.2.0 Schema for the Harmony System. XSD 2.0 was developed to follow a logical flow enabling easier understanding, import, manipulation and display of Harmony data for external export customers (XSD 2.0 mirrors the logical flow of the Harmony records result page). XSD 2.0 is also a representation of common document elements and attributes that are shared by Harmony internal and external users. There are four parts of the XSD: 1. Harmony Schema - Functional Representation of Data 
     2. List of Values (LOV) Maintenance
     3. Harmony Meta-Data Definitions 
     4. Display and Maintenance Guide for External Export Customers
Oracle XML Database Annotations were added to control  mapping between XMLTYPE storage within the database and the Harmony XML Schemas. Oracle XML Database Annotations are set as attributes added to document, element, and attribute levels of XML Schemas for registration into the XML Database that declare SQL Object Names, data types, and various storage options. 
All of these annotations are in the Oracle XML Database name space (xmlns:xdb) as defined above.
Schematron schema validation techniques were also added to ensure that xsd:element=name "document" is the root element defined within instances, that there are no Privatized Remarks and; that there are no Sensitive Remarks.
			<Modified_By>D Freyer</Modified_By>
<sch:title> Includes Schematron validation for Root Element and Privatized
	<xsd:import namespace="urn:us:gov:ic:sec" schemaLocation="Harmony-IC-ISM-v2.xsd"/>
	<xsd:include schemaLocation="Harmony-DOCUMENT-LOV.xsd"/>
	<xsd:include schemaLocation="Harmony-DOCUMENT-BIO.xsd"/>
	<xsd:include schemaLocation="Harmony-DOCUMENT-GEO.xsd"/>
	<xsd:include schemaLocation="Harmony-DOCUMENT-NEE.xsd"/>
	<xsd:include schemaLocation="Harmony-DOCUMENT-MCM.xsd"/>
<document xmlns:doc="urn:us:gov:ic:doc" xmlns:xsd=""
xmlns:xdb="" xmlns:xsi="" xsi:noNamespaceSchemaLocation="Harmony-DOCUMENT.xsd">

Open in new window

slightwv (䄆 Netminder) Commented:
It could be a memory leak but those typically throw an error of some kind.  Since it doesn't fail on the exact same record but seems to fail about the same record count that would make sense.

I'm not sure how much more we can help here.  You might need to open an SR with Oracle.  They can have you set some session tracing to see exactly what is happening.

Do you have a test environment that you could try or higher patchset?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dcfreyerAuthor Commented:
I have been talkiing with the SA's here on creating another db instance that has been patched to, as it fixes many issues with XDB.  Two bugs especially are related to this issue - 4387531 and 5990114.  I am getting push-back however, as hardware resources are limited for this purpose.

I will close this Expert request, as I agree with your statement on further assistance.  I may create a follow-on issue though following additional Oracle troubleshooting,  If possible I will push for a one-off patch for to fix.  As a note, I am not a big fan of meta_link as many of its "professionals" follow a troubleshooting tree, and have no real long-time experience with the XML database.  Maybe I will get lucky and get a US based person.

You have given me much insight and guidance for which I am very appreciative.  You earned points my friend, I will try and give you 2/3's of 500.

Take care my friend, and thank you for your professionalism and consideration.

dcfreyerAuthor Commented:
It was a distinct pleasure working with you.  Your insight, knowledge and guidance did much to help me arrive at the next step in finding a solution to this issue.
dcfreyerAuthor Commented:
Changed my mind and gave you all the points - you earned them.
slightwv (䄆 Netminder) Commented:
No problem.  For many of us here, it's not about the points.  We just like to help and occasionally get challenged ourselves.

I agree with Oracle Support and hate the long drawn out process to get at the root cause of the problem (trust me, you have no idea how much I dislike it).  However in this case, they have the trace knowledge and ability to parse the resulting logs to zero in on a problem.

It couldn't hurt to open the SR and have it open in parallel.  This question has been open for 13 days.  Even Oracle could have probably let you know something in that amount of time.  Granted, they'll probably tell you they don't support anymore but they should be able to help you determine the specific bug you may be encountering.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.