Re: Converting XML Format LCR's

Here is a sample I used recently.

I was converting Streams LCR to XML, and doing a DB web service call-out to 
initiate a BPEL process based on that change.

There is an XSD for the document as well that I have attached.

This is the code:

CREATE OR REPLACE PROCEDURE call_bpel_ws (in_any  IN  ANYDATA) AS
  --PRAGMA AUTONOMOUS_TRANSACTION;
  lcr          SYS.LCR$_ROW_RECORD;
  rc           PLS_INTEGER;
  command      VARCHAR2(30);
  oim_xml      clob;
  http_req  utl_http.req;
  http_resp utl_http.resp;
  lcr_request_env varchar2(32767);
  lcr_response_env varchar2(32767); 
  lcr_xml XMLType;

BEGIN
rc := in_any.GETOBJECT(lcr);  
command := lcr.GET_COMMAND_TYPE();

lcr_xml:=DBMS_STREAMS.CONVERT_LCR_TO_XML(in_any);

lcr_request_env:='<soap:Envelope 
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/";><soap:Header/><soap:Body>'||lcr_xml.getClobVal()||'</soap:Body></soap:Envelope>';

http_req := 
utl_http.begin_request('http://xpone:8888/orabpel/default/LCR_TO_XML_FILE/1.0', 
'POST', utl_http.HTTP_VERSION_1_1);
utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
utl_http.set_header(http_req, 'Content-Length', length(lcr_request_env));
utl_http.set_header(http_req, 'SOAPAction', 'initiate');
utl_http.write_text(http_req, lcr_request_env);

http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, lcr_response_env);
utl_http.end_response(http_resp);

audit_handler(lcr_xml, lcr_response_env);

EXCEPTION
   WHEN OTHERS
   THEN
      exception_log;
END; 
/


create or replace procedure audit_handler (lcr_xml IN XMLType, lcr_response_env 
IN varchar2) as
PRAGMA AUTONOMOUS_TRANSACTION;

begin

--XML Request Text Going to BPEL
insert into streams_monitor(txt_msg) values ('XML LCR'||lcr_xml.getClobVal());

--BPEL Response
insert into streams_monitor (txt_msg) values (lcr_response_env);

     

COMMIT;
end;
/


--- On Fri, 12/19/08, Michael Moore <michaeljmoore@xxxxxxxxx> wrote:

> From: Michael Moore <michaeljmoore@xxxxxxxxx>
> Subject: Re: Converting XML Format LCR's
> To: sblackbu@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Date: Friday, December 19, 2008, 1:43 PM
> Is your XML document associated with a Schema. The schema
> has to be stored
> in the dictionary and the xml document needs to be
> validated against that
> schema. I am not 100% sure that this is your problem, but
> it sure looks like
> that is what the error message is trying to tell you.
> 
> Mike
> 
> On Fri, Dec 19, 2008 at 7:34 AM, Stuart Blackburn
> <sblackbu@xxxxxxxxx>wrote:
> 
> > Has anyone ever had success using the
> SYS.DBMS_STREAMS.convert_xml_to_lcr()
> > function?  I get an "ORA-19030 - Method invalid
> for non-schema based XML
> > Documents" error every time.  Even during testing
> when I run a
> > SYS.DBMS_STREAMS.CONVERT_LCR_TO_XML() function,
> enqueue the resulting
> > message and then immediately after I dequeue that same
> message and run a
> > SYS.DBMS_STREAMS.convert_xml_to_lcr() I still get the
> error.  I know there's
> > nothing wrong with the xml format because I
> haven't modified it in this
> > process.
> >
> > Thanks.
> > Stuart
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >


      
<schema xmlns="http://www.w3.org/2001/XMLSchema"; 
        targetNamespace="http://xmlns.oracle.com/streams/schemas/lcr"; 
        xmlns:lcr="http://xmlns.oracle.com/streams/schemas/lcr";
        xmlns:xdb="http://xmlns.oracle.com/xdb";
          version="1.0"
        elementFormDefault="qualified">
 
  <simpleType name = "short_name">
    <restriction base = "string">
      <maxLength value="30"/>
    </restriction>
  </simpleType>
 
  <simpleType name = "long_name">
    <restriction base = "string">
      <maxLength value="4000"/>
    </restriction>
  </simpleType>
 
  <simpleType name = "db_name">
    <restriction base = "string">
      <maxLength value="128"/>
    </restriction>
  </simpleType>
 
  <!-- Default session parameter is used if format is not specified -->
  <complexType name="datetime_format">
    <sequence>
      <element name = "value" type = "string" nillable="true"/>
      <element name = "format" type = "string" minOccurs="0" nillable="true"/>
    </sequence>
  </complexType>
 
  <complexType name="anydata">
    <choice>
      <element name="varchar2" type = "string" xdb:SQLType="CLOB" 
                                                        nillable="true"/>
 
      <!-- Represent char as varchar2. xdb:CHAR blank pads upto 2000 bytes! -->
      <element name="char" type = "string" xdb:SQLType="CLOB"
                                                        nillable="true"/>
      <element name="nchar" type = "string" xdb:SQLType="NCLOB"
                                                        nillable="true"/>
 
      <element name="nvarchar2" type = "string" xdb:SQLType="NCLOB"
                                                        nillable="true"/>
      <element name="number" type = "double" xdb:SQLType="NUMBER"
                                                        nillable="true"/>
      <element name="raw" type = "hexBinary" xdb:SQLType="BLOB" 
                                                        nillable="true"/>
      <element name="date" type = "lcr:datetime_format"/>
      <element name="timestamp" type = "lcr:datetime_format"/>
      <element name="timestamp_tz" type = "lcr:datetime_format"/>
      <element name="timestamp_ltz" type = "lcr:datetime_format"/>
 
      <!-- Interval YM should be as per format allowed by SQL -->
      <element name="interval_ym" type = "string" nillable="true"/>
 
      <!-- Interval DS should be as per format allowed by SQL -->
      <element name="interval_ds" type = "string" nillable="true"/>
 
      <element name="urowid" type = "string" xdb:SQLType="VARCHAR2"
                                                        nillable="true"/>
    </choice>
  </complexType>
 
  <complexType name="column_value">
    <sequence>
      <element name = "column_name" type = "lcr:long_name" nillable="false"/>
      <element name = "data" type = "lcr:anydata" nillable="false"/>
      <element name = "lob_information" type = "string" minOccurs="0"
                                                           nillable="true"/>
      <element name = "lob_offset" type = "nonNegativeInteger" minOccurs="0"
                                                           nillable="true"/>
      <element name = "lob_operation_size" type = "nonNegativeInteger" 
                                             minOccurs="0" nillable="true"/>
      <element name = "long_information" type = "string" minOccurs="0"
                                                           nillable="true"/>
    </sequence>
  </complexType>
 
  <complexType name="extra_attribute">
    <sequence>
      <element name = "attribute_name" type = "lcr:short_name"/>
      <element name = "attribute_value" type = "lcr:anydata"/>
    </sequence>
  </complexType>
 
  <element name = "ROW_LCR" xdb:defaultTable="">
    <complexType>
      <sequence>
        <element name = "source_database_name" type = "lcr:db_name" 
                                                            nillable="false"/>
        <element name = "command_type" type = "string" nillable="false"/>
        <element name = "object_owner" type = "lcr:short_name" 
                                                            nillable="false"/>
        <element name = "object_name" type = "lcr:short_name"
                                                            nillable="false"/>
        <element name = "tag" type = "hexBinary" xdb:SQLType="RAW" 
                                               minOccurs="0" nillable="true"/>
        <element name = "transaction_id" type = "string" minOccurs="0" 
                                                             nillable="true"/>
        <element name = "scn" type = "double" xdb:SQLType="NUMBER" 
                                               minOccurs="0" nillable="true"/>
        <element name = "old_values" minOccurs = "0">
          <complexType>
            <sequence>
              <element name = "old_value" type="lcr:column_value" 
                                                    maxOccurs = "unbounded"/>
            </sequence>
          </complexType>
        </element>
        <element name = "new_values" minOccurs = "0">
          <complexType>
            <sequence>
              <element name = "new_value" type="lcr:column_value" 
                                                    maxOccurs = "unbounded"/>
            </sequence>
          </complexType>
        </element>
        <element name = "extra_attribute_values" minOccurs = "0">
          <complexType>
            <sequence>
              <element name = "extra_attribute_value"
                       type="lcr:extra_attribute"
                       maxOccurs = "unbounded"/>
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
 
  <element name = "DDL_LCR" xdb:defaultTable="">
    <complexType>
      <sequence>
        <element name = "source_database_name" type = "lcr:db_name" 
                                                        nillable="false"/>
        <element name = "command_type" type = "string" nillable="false"/>
        <element name = "current_schema" type = "lcr:short_name"
                                                        nillable="false"/>
        <element name = "ddl_text" type = "string" xdb:SQLType="CLOB"
                                                        nillable="false"/>
        <element name = "object_type" type = "string"
                                        minOccurs = "0" nillable="true"/>
        <element name = "object_owner" type = "lcr:short_name"
                                        minOccurs = "0" nillable="true"/>
        <element name = "object_name" type = "lcr:short_name"
                                        minOccurs = "0" nillable="true"/>
        <element name = "logon_user" type = "lcr:short_name"
                                        minOccurs = "0" nillable="true"/>
        <element name = "base_table_owner" type = "lcr:short_name"
                                        minOccurs = "0" nillable="true"/>
        <element name = "base_table_name" type = "lcr:short_name"
                                        minOccurs = "0" nillable="true"/>
        <element name = "tag" type = "hexBinary" xdb:SQLType="RAW"
                                        minOccurs = "0" nillable="true"/>
        <element name = "transaction_id" type = "string"
                                        minOccurs = "0" nillable="true"/>
        <element name = "scn" type = "double" xdb:SQLType="NUMBER"
                                        minOccurs = "0" nillable="true"/>
        <element name = "extra_attribute_values" minOccurs = "0">
          <complexType>
            <sequence>
              <element name = "extra_attribute_value"
                       type="lcr:extra_attribute"
                       maxOccurs = "unbounded"/>
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

Other related posts: