Solved

Inserting BFILES into XMLTYPE using INLINE CURSOR FOR LOOP Fails

Posted on 2009-07-07
15
1,283 Views
Last Modified: 2013-12-07
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

CREATE TABLE FADSADMIN.FADS_DIR_CONTENTS

(

  FILE_NAME      VARCHAR2(255 BYTE),

  FILE_SIZE      NUMBER(15),

  FILE_TYPE      VARCHAR2(1 BYTE),

  LAST_MODIFIED  DATE

)

TABLESPACE FADS;
 

CREATE UNIQUE INDEX FADSADMIN.FILENAMEPK ON FADSADMIN.FADS_DIR_CONTENTS

(FILE_NAME);
 

ALTER TABLE FADSADMIN.FADS_DIR_CONTENTS ADD (

  CONSTRAINT FILENAMEPK

 PRIMARY KEY

 (FILE_NAME));

-----------------------------------------------------------------------------------------------

--Polling Procedure

CREATE OR REPLACE PROCEDURE fadsadmin.poll_4_fads_xml_data

IS

   v_dirname   VARCHAR2 (255) := NULL;
 

BEGIN

   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;
 

   FOR x IN (SELECT *

             FROM fads_dir_contents

             WHERE file_type = 'F')

   LOOP

      INSERT INTO meta_docs

      VALUES (

                xmltype (BFILENAME ('FADSNEWDOCSDIR', x.file_name),

                         NLS_CHARSET_ID ('AL32UTF8')

                )

             );
 

      COMMIT;
 

      -- cleanup the directory

      UTL_FILE.fremove ('FADSNEWDOCSDIR', x.file_name);

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      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)

AS

   LANGUAGE JAVA

   NAME 'FadsDirListing.getContentsList(java.lang.String)';

---------------------------------------------------------------------------------------

-Java Stored Procedure
 

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED fadsadmin."FADSDIRLISTING"

   AS import java.io.*;

import java.sql.*;

 

public class FadsDirListing

{

    public static void getContentsList(String directory) throws SQLException

    {

       #sql {DELETE FROM FADS_DIR_CONTENTS

            };

           

       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 

                    INTO FADS_DIR_CONTENTS (FILE_NAME, FILE_SIZE, FILE_TYPE, LAST_MODIFIED)

                  VALUES (:filName, :filSize, :filType, :modDate)

                };

           #sql { COMMIT

                };

       }

   }

}

Open in new window

0
Comment
Question by:dcfreyer
  • 7
  • 5
15 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
What returns the query

SELECT *
             FROM fads_dir_contents
             WHERE file_type = 'F'

Also catch the exeception in clear way:
set serveroutput on
.....
EXCEPTION
   WHEN OTHERS
   THEN
     dbms_output.put_line('Errors  encountered.');
     dbms_output.put_line(substr(sqlerrm,1,254));
END;

Also your java programa can not catch exception: try ...catch
0
 
LVL 1

Author Comment

by:dcfreyer
Comment Utility
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:
EXCEPTION
          WHEN OTHERS
    THEN
        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
 
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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: 10.2.0.4 )
0
 
LVL 1

Author Comment

by:dcfreyer
Comment Utility
I apologize for not answering your post until now.

Our database version is 10.2.0.3.  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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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:
<foo>
<bar>Hello</bar>
<bar>World</bar>
</foo>

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.
0
 
LVL 1

Author Comment

by:dcfreyer
Comment Utility
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.  

v/r

David

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 10.2.0.3 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.
0
 
LVL 1

Author Comment

by:dcfreyer
Comment Utility
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.

David

The XML Schema  and XML Instance document declarations section are as follows;
-- SCHEMA DECLARATIONS

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:us:gov:ic:doc" xmlns:sec="urn:us:gov:ic:sec" xmlns:sch="http://www.ascc.net/xml/schematron" xmlns:xdb="http://xmlns.oracle.com/xdb"

targetNamespace="urn:us:gov:ic:doc"

elementFormDefault="qualified" version="2.2.0"

xdb:storeVarrayAsTable="true"

xdb:schemaURL="Harmony-DOCUMENT.xsd">

	

<xsd:annotation>

  <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.

     </xsd:documentation>

	</xsd:annotation>

	<xsd:annotation>

		<xsd:appinfo>

			<BaseVersion>2.2</BaseVersion>

			<FadsVersion>1.0</FadsVersion>

			<Modified_Date>20090513</Modified_Date>

			<Modified_By>D Freyer</Modified_By>

<sch:title> Includes Schematron validation for Root Element and Privatized

			</sch:title>

		</xsd:appinfo>

	</xsd:annotation>

	<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"/>

	<!--
 

-------------------------------------------------------------------------

--XML INSTANCE DOCUMENT DECLARATIONS
 

<document xmlns:doc="urn:us:gov:ic:doc" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sec="urn:us:gov:ic:sec"

xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Harmony-DOCUMENT.xsd">

	

Open in new window

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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 10.2.0.4 or higher patchset?
0
 
LVL 1

Author Comment

by:dcfreyer
Comment Utility
I have been talkiing with the SA's here on creating another db instance that has been patched to 10.2.0.5, 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 10.2.0.3 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.

David
0
 
LVL 1

Author Closing Comment

by:dcfreyer
Comment Utility
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.
0
 
LVL 1

Author Comment

by:dcfreyer
Comment Utility
Changed my mind and gave you all the points - you earned them.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 10.2.0.3 anymore but they should be able to help you determine the specific bug you may be encountering.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Salary Amount Format 13 56
Adding a blank row when using union all 4 49
UNIX SCP 5 45
Oracle DATE Column Space 11 41
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now