Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> ORA-19209 when performing XML validation against a registered XSD

ORA-19209 when performing XML validation against a registered XSD

From: RayH <harilar_at_cpwplc.com>
Date: 1 Sep 2006 07:19:27 -0700
Message-ID: <1157120366.957928.226610@m73g2000cwd.googlegroups.com> 





Hello. I want to use PL/SQL to generate some XML and then validate it
against a registered XSD.



This is failing when I use XMLFORMAT.CREATEFORMAT, specifying my XSD.
The error is ORA-19209: invalid or unsupported formatting argument.



Info:


db version = 10.1.0.4.0


user has xdbadmin and resource privileges granted



create tables and data:



create table TMP_RH_CLOB


(


  XML_STRING CLOB



);



create table TMP_RH_CRM_XML


(


  CONTACT        NUMBER(8),



  TRANSACTION    NUMBER(10),


  CSA            VARCHAR2(12),
  OPEN_DAT       DATE,
  CLOSED_DAT     DATE,
  OPEN_TIM       DATE,
  CLOSED_TIM     DATE,
  EMPLOYEE_TRANS VARCHAR2(12),
  STATUS         VARCHAR2(1),
  AGREEMENT_NO   VARCHAR2(8),
  DOCKET_NUM     NUMBER(10),
  PRODUCT_DESCR  VARCHAR2(40),
  PART           VARCHAR2(20),
  ORDER_QTY      NUMBER(12),
  RETAIL_AMT     NUMBER,
  SALE_AMT       NUMBER,
  ACCOUNTNO      VARCHAR2(40),
  SORTCODE       VARCHAR2(40),
  ACCOUNTNAME    VARCHAR2(40),
  BANK_NAME      VARCHAR2(60),
  FEATURE_TEXT   VARCHAR2(450),
  IMEI           VARCHAR2(25)




);



insert into TMP_RH_CRM_XML (CONTACT, TRANSACTION, CSA, OPEN_DAT,
CLOSED_DAT, OPEN_TIM, CLOSED_TIM, EMPLOYEE_TRANS, STATUS, AGREEMENT_NO,

DOCKET_NUM, PRODUCT_DESCR, PART, ORDER_QTY, RETAIL_AMT, SALE_AMT,

ACCOUNTNO, SORTCODE, ACCOUNTNAME, BANK_NAME, FEATURE_TEXT, IMEI)


values (99906376, 3674381, 'MENONV', to_date('19-10-2005',
'dd-mm-yyyy'), to_date('19-10-2005', 'dd-mm-yyyy'), to_date('01-01-1900
11:31:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-1900 16:42:16',


'dd-mm-yyyy hh24:mi:ss'), 'MENONV', 'C', null, 9366490, '6 Months 1/2
Price line rental', 'MOTV3', 1, 0, 0, '78945612', '938076', 'J MAX',
'FIRST TRUST', 'Baker Street', '240752407808987');
insert into TMP_RH_CRM_XML (CONTACT, TRANSACTION, CSA, OPEN_DAT,
CLOSED_DAT, OPEN_TIM, CLOSED_TIM, EMPLOYEE_TRANS, STATUS, AGREEMENT_NO,

DOCKET_NUM, PRODUCT_DESCR, PART, ORDER_QTY, RETAIL_AMT, SALE_AMT,

ACCOUNTNO, SORTCODE, ACCOUNTNAME, BANK_NAME, FEATURE_TEXT, IMEI)


values (99906376, 3674381, 'MENONV', to_date('19-10-2005',
'dd-mm-yyyy'), to_date('19-10-2005', 'dd-mm-yyyy'), to_date('01-01-1900
11:31:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-1900 16:42:16',


'dd-mm-yyyy hh24:mi:ss'), 'MENONV', 'C', null, 9366490, '6 Months 1/2
Price line rental', 'MOTV3', 1, 0, 0, '78945612', '938076', 'J MAX',
'FIRST TRUST', 'NO', '240752407808987');

insert into TMP_RH_CRM_XML (CONTACT, TRANSACTION, CSA, OPEN_DAT,
CLOSED_DAT, OPEN_TIM, CLOSED_TIM, EMPLOYEE_TRANS, STATUS, AGREEMENT_NO,

DOCKET_NUM, PRODUCT_DESCR, PART, ORDER_QTY, RETAIL_AMT, SALE_AMT,

ACCOUNTNO, SORTCODE, ACCOUNTNAME, BANK_NAME, FEATURE_TEXT, IMEI)


values (99906376, 3674381, 'MENONV', to_date('19-10-2005',
'dd-mm-yyyy'), to_date('19-10-2005', 'dd-mm-yyyy'), to_date('01-01-1900
11:31:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-1900 16:42:16',


'dd-mm-yyyy hh24:mi:ss'), 'MENONV', 'C', null, 9366490, '6 Months 1/2
Price line rental', 'MOTV3', 1, 0, 0, '78945612', '938076', 'J MAX',
'FIRST TRUST', 'Bulk Dealer', '240752407808987');
insert into TMP_RH_CRM_XML (CONTACT, TRANSACTION, CSA, OPEN_DAT,
CLOSED_DAT, OPEN_TIM, CLOSED_TIM, EMPLOYEE_TRANS, STATUS, AGREEMENT_NO,

DOCKET_NUM, PRODUCT_DESCR, PART, ORDER_QTY, RETAIL_AMT, SALE_AMT,

ACCOUNTNO, SORTCODE, ACCOUNTNAME, BANK_NAME, FEATURE_TEXT, IMEI)


values (99906376, 3674381, 'MENONV', to_date('19-10-2005',
'dd-mm-yyyy'), to_date('19-10-2005', 'dd-mm-yyyy'), to_date('01-01-1900
11:31:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-1900 16:42:16',


'dd-mm-yyyy hh24:mi:ss'), 'MENONV', 'C', null, 9366490, '6 Months 1/2
Price line rental', 'MOTV3', 1, 0, 0, '78945612', '938076', 'J MAX',
'FIRST TRUST', 'OBP LifeLine Sales process, inbound telephone
required', '240752407808987');


insert into TMP_RH_CRM_XML (CONTACT, TRANSACTION, CSA, OPEN_DAT,
CLOSED_DAT, OPEN_TIM, CLOSED_TIM, EMPLOYEE_TRANS, STATUS, AGREEMENT_NO,

DOCKET_NUM, PRODUCT_DESCR, PART, ORDER_QTY, RETAIL_AMT, SALE_AMT,

ACCOUNTNO, SORTCODE, ACCOUNTNAME, BANK_NAME, FEATURE_TEXT, IMEI)


values (99906376, 3674381, 'MENONV', to_date('19-10-2005',
'dd-mm-yyyy'), to_date('19-10-2005', 'dd-mm-yyyy'), to_date('01-01-1900
11:31:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-1900 16:42:16',


'dd-mm-yyyy hh24:mi:ss'), 'MENONV', 'C', null, 9366490, '6 Months 1/2
Price line rental', 'MOTV3', 1, 0, 0, '78945612', '938076', 'J MAX',
'FIRST TRUST', 'RETAIL', '240752407808987');

insert into TMP_RH_CRM_XML (CONTACT, TRANSACTION, CSA, OPEN_DAT,
CLOSED_DAT, OPEN_TIM, CLOSED_TIM, EMPLOYEE_TRANS, STATUS, AGREEMENT_NO,

DOCKET_NUM, PRODUCT_DESCR, PART, ORDER_QTY, RETAIL_AMT, SALE_AMT,

ACCOUNTNO, SORTCODE, ACCOUNTNAME, BANK_NAME, FEATURE_TEXT, IMEI)


values (99906376, 3674381, 'MENONV', to_date('19-10-2005',
'dd-mm-yyyy'), to_date('19-10-2005', 'dd-mm-yyyy'), to_date('01-01-1900
11:31:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-1900 16:42:16',
'dd-mm-yyyy hh24:mi:ss'), 'MENONV', 'C', null, 9366490, '6 Months 1/2


Price line rental', 'MOTV3', 1, 0, 0, '78945612', '938076', 'J MAX',
'FIRST TRUST', 'Premier', '240752407808987');
insert into TMP_RH_CRM_XML (CONTACT, TRANSACTION, CSA, OPEN_DAT,
CLOSED_DAT, OPEN_TIM, CLOSED_TIM, EMPLOYEE_TRANS, STATUS, AGREEMENT_NO,

DOCKET_NUM, PRODUCT_DESCR, PART, ORDER_QTY, RETAIL_AMT, SALE_AMT,

ACCOUNTNO, SORTCODE, ACCOUNTNAME, BANK_NAME, FEATURE_TEXT, IMEI)


values (99906376, 3674381, 'MENONV', to_date('19-10-2005',
'dd-mm-yyyy'), to_date('19-10-2005', 'dd-mm-yyyy'), to_date('01-01-1900
11:31:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-1900 16:42:16',
'dd-mm-yyyy hh24:mi:ss'), 'MENONV', 'C', null, 9366490, '6 Months 1/2


Price line rental', 'MOTV3', 1, 0, 0, '78945612', '938076', 'J MAX',
'FIRST TRUST', 'On Line Student Mobile', '240752407808987');



Then I created directories as follows:



DECLARE



  v_return  BOOLEAN;


BEGIN


  v_return := dbms_xdb.createFolder('/home/');
  v_return := dbms_xdb.createFolder('/home/DEV/');
  v_return := dbms_xdb.createFolder('/home/DEV/xsd/');
  v_return := dbms_xdb.createFolder('/home/DEV/messages/');


  COMMIT;



END;


I then registered against the db as follows:



DECLARE



    lvXsd     CONSTANT VARCHAR2(32000) := '<?xml version="1.0"
encoding="UTF-8" standalone="yes"?>


    <!--W3C Schema generated by XMLSpy v2006 rel. 3 sp1
(http://www.altova.com)-->


<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">

	<xs:element name="TransInfo">
		<xs:complexType>


			<xs:sequence>

				<xs:element name="PIE_ERROR" type="PIE_ERRORType" minOccurs="0"/>
				<xs:element name="Transaction" type="TransactionType"
maxOccurs="unbounded"/>


			</xs:sequence>

		</xs:complexType>
	</xs:element>
	<xs:complexType name="PIE_ERRORType">
		<xs:sequence>


			<xs:element ref="ERROR_CODE"/>

			<xs:element ref="DESCRIPTION"/>

		</xs:sequence>
	</xs:complexType>
	<xs:element name="ERROR_CODE">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="LEAD NUMBER 12345 IS INVALID"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="DESCRIPTION">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="0001"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:complexType name="TransactionType">
		<xs:sequence>


			<xs:element ref="TRANSACTION_NUMBER"/>

			<xs:element ref="TRANSACTION_STATUS"/>

                        <xs:element ref="CONTACT_ID"/>


			<xs:element ref="INVOICE_NUMBER"/>

			<xs:element ref="CSA"/>

			<xs:element ref="OPEN_DATETIME"/>

			<xs:element ref="CLOSED_DATETIME"/>

			<xs:element name="Orderline" type="OrderlineType"

maxOccurs="unbounded"/>
		</xs:sequence>
	</xs:complexType>
	<xs:element name="TRANSACTION_NUMBER">
		<xs:simpleType>


			<xs:restriction base="xs:int">

				<xs:enumeration value="3455229"/>
				<xs:enumeration value="3455230"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="TRANSACTION_STATUS">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="C"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="CONTACT_ID">
		<xs:simpleType>


			<xs:restriction base="xs:int">

				<xs:enumeration value="111"/>
				<xs:enumeration value="222"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="INVOICE_NUMBER">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="123456789"/>
				<xs:enumeration value="987654321"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="CHANNEL">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="Test 1"/>
				<xs:enumeration value="Test 2"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="CSA">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="SEARLEA"/>
				<xs:enumeration value="SILVAJ"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="OPEN_DATETIME">
		<xs:simpleType>


			<xs:restriction base="xs:dateTime">

				<xs:enumeration value="2001-10-26T20:32:52"/>
				<xs:enumeration value="2004-12-06T10:23:25"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="CLOSED_DATETIME">
		<xs:simpleType>


			<xs:restriction base="xs:dateTime">

				<xs:enumeration value="2001-10-26T21:32:52"/>
				<xs:enumeration value="2004-12-06T10:23:25"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:complexType name="OrderlineType">
		<xs:sequence>


			<xs:element ref="ORDER_QTY"/>

			<xs:element ref="RETAIL_AMT"/>

			<xs:element ref="SALE_AMT"/>

			<xs:element ref="PRODUCT_DESCR"/>

			<xs:element ref="PART"/>

			<xs:element ref="IMEI"/>

			<xs:element name="BankDetails" type="BankDetailsType"/>

		</xs:sequence>
	</xs:complexType>
	<xs:element name="ORDER_QTY">
		<xs:simpleType>


			<xs:restriction base="xs:int">

				<xs:enumeration value="1"/>
				<xs:enumeration value="3"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="RETAIL_AMT">
		<xs:simpleType>


			<xs:restriction base="xs:decimal">

				<xs:enumeration value="45.67"/>
				<xs:enumeration value="54.76"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="SALE_AMT">
		<xs:simpleType>


			<xs:restriction base="xs:decimal">

				<xs:enumeration value="45.67"/>
				<xs:enumeration value="54.76"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="PRODUCT_DESCR">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="Product test 2"/>
				<xs:enumeration value="Vodafone Nokia 6230 Pack"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="PART">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="6806"/>
				<xs:enumeration value="8606"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="IMEI">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="099000000000001"/>
				<xs:enumeration value="09900078900001"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:complexType name="BankDetailsType">
		<xs:sequence>


			<xs:element ref="BANK_NAME"/>

			<xs:element ref="ACCOUNT_NAME"/>

			<xs:element ref="ACCOUNT_NUMBER"/>

			<xs:element ref="SORT_CODE"/>

		</xs:sequence>
	</xs:complexType>
	<xs:element name="BANK_NAME">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="HSBC"/>
				<xs:enumeration value="LLOYDS"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="ACCOUNT_NAME">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="MR A SEARLE"/>
				<xs:enumeration value="MR J SILVA"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="ACCOUNT_NUMBER">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="87698789"/>
				<xs:enumeration value="89769082"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>
	<xs:element name="SORT_CODE">
		<xs:simpleType>


			<xs:restriction base="xs:string">

				<xs:enumeration value="100928"/>
				<xs:enumeration value="123346"/>


			</xs:restriction>

		</xs:simpleType>
	</xs:element>




</xs:schema>';



BEGIN



    dbms_xmlschema.registerSchema(schemaurl =>
'http://aix53:8080/home/DEV/xsd/test_1.xsd'

                                 ,schemaDoc => lvXsd
                                 );




END;


At this point, I noticed that test_1.xsd was not in
'http://aix53:8080/home/DEV/xsd', although registration seemed to
complete with no errors. Is this expected?



My XML generation/validation script is here:



SET SERVEROUTPUT ON SIZE 1000000



SET LONG 50000



SET HEAD OFF



SET PAGES 0



SET TIMING ON


DECLARE



    v_xml_clob                     XMLTYPE;
    v_empty_xml_clob               CLOB;

    v_pr_found                     BOOLEAN;
    v_valid_xml                    VARCHAR2(10);





BEGIN


    EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp_rh_clob';
    v_pr_found := FALSE;



        v_pr_found := TRUE;
        BEGIN
            SELECT SYS_XMLAGG(
                       XMLELEMENT("Transaction"
                                 ,XMLFOREST(rec_test.transaction
"transaction"
                                                     ,rec_test.status
"transaction_status"
                                                     ,rec_test.contact


"contact_id"



,rec_test.docket_num "invoice_number"

                                                     ,rec_test.csa


"csa"



,TO_CHAR(rec_test.open_dat, 'YYYY-MM-YY') || 'T' ||
TO_CHAR(rec_test.open_tim, 'HH24:MI:SS') "open_datetime"



,TO_CHAR(rec_test.closed_dat, 'YYYY-MM-YY') || 'T' ||
TO_CHAR(rec_test.closed_tim, 'HH24:MI:SS') "closed_datetime"



,XMLFOREST(rec_test.order_qty "order_qty"



,rec_test.retail_amt "retail_amt"



,rec_test.sale_amt "sale_amt"



,rec_test.product_descr "product_descr"



,rec_test.part "part"



,rec_test.imei "imei"



,XMLFOREST(rec_test.bank_name "bank_name"



 ,rec_test.accountname "account_name"



 ) "BankDetails"

                                                               )


"Orderline"


                                           )
                                 )
                                    ,XMLFORMAT.CREATEFORMAT('TransInfo'
                                                           ,'USE GIVEN


SCHEMA'


,'/home/harilalr/sample_schema_29082006.xsd'

--                                                           ,NULL
--                                                           ,NULL
--                                                           ,NULL
                                                           )
                             )--.GETCLOBVAL()
            INTO   v_xml_clob
            FROM   tmp_rh_crm_xml rec_test
            WHERE  ROWNUM < 4;

           v_xml_clob.schemavalidate();

            INSERT INTO tmp_rh_clob
                (xml_string
                )
                VALUES
                (TO_CLOB(v_xml_clob)
                );

            COMMIT;
        END;


    IF NOT(v_pr_found) THEN
        RAISE_APPLICATION_ERROR
            (-20002
            ,'No data found'
            );




    END IF;


EXCEPTION



    WHEN OTHERS THEN


        DBMS_OUTPUT.PUT_LINE('Unexpected error ['||SUBSTR(SQLERRM, 1,
200)||'] in test.sql');


        ROLLBACK;
        RAISE;





END;



/



Can anyone see anything that's obviously wrong with this? I imagine
it's the fact that the registration probably hasn't completed
successfully, but don't know this for sure. The error message ORA-19209
isn't particularly useful.



Any comments/suggestions on a solution very much appreciated.



Thanks,


Ray.
Received on Fri Sep 01 2006 - 09:19:27 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US