Home » SQL & PL/SQL » SQL & PL/SQL » XML parsing function (Oracle 9.2.0.6)
XML parsing function [message #349082] Thu, 18 September 2008 17:07 Go to next message
dahaka
Messages: 1
Registered: September 2008
Junior Member
Hi experts,

I am having problem with a function, which parses XML data stored as LONG type in Oracle 9.2.0.6. The function is shown below:

 ( audit_key   IN   VARCHAR2 )
   RETURN XMLTYPE
AS
   cur          INTEGER;
   ignore       NUMBER;
   long_len     NUMBER;
   out_buf      VARCHAR2 (32767);
BEGIN
   cur := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (cur, 'select audit_xml from sys_audit where audit_key = :k', DBMS_SQL.native);
   DBMS_SQL.bind_variable (cur, ':k', audit_key);
   DBMS_SQL.define_column_long (cur, 1);
   ignore := DBMS_SQL.EXECUTE (cur);
   IF (DBMS_SQL.fetch_rows (cur) > 0)
   THEN
      DBMS_SQL.column_value_long (cur, 1, 32767, 39, out_buf, long_len);
   END IF;
   DBMS_SQL.close_cursor (cur);
   RETURN xmltype(out_buf);
END to_xmltype;



The XML data looks something like:

<AuditDetail AuditType="Shipment">
    <IDs>
        <ID DataType="class java.lang.String" Name="ShipmentNo" Value="100003350"/>
        <ID DataType="class java.lang.String" Name="ShipNode" Value="NTSHP01"/>
        <ID DataType="class java.lang.String"
            Name="SellerOrganizationCode" Value="NT"/>
    </IDs>
    <Attributes>
        <Attribute DataType="class java.lang.String" Name="Status"
            NewValue="1200.100" OldValue="1200"/>
        <Attribute DataType="class java.util.Timestamp"
            Name="StatusDate" NewValue="20080603080546" OldValue="20080603080449"/>
        <Attribute DataType="class java.util.Timestamp"
            Name="Modifyts" NewValue="20080603080546" OldValue="20080603080449"/>
    </Attributes>
</AuditDetail>



The above function is used in a view below:

(
SELECT
    a.audit_key,
    a.table_name,
    a.table_key,
    a.operation,
    a.reference_6,
    a.modifyts,
    extractValue(value(x),'/Attribute/@Name') attribute_name,
    extractValue(value(x),'/Attribute/@DataType') data_type,
    extractValue(value(x),'/Attribute/@OldValue') old_value,
    extractValue(value(x),'/Attribute/@NewValue') new_value
FROM
    sys_audit a,
    TABLE(XMLSequence(extract(to_xmltype(a.audit_key),'/AuditDetail/Attributes/Attribute'))) x
)



Now when I try to query this view, I am getting the following error:

Error: java.sql.SQLException: Io exception: End of TNS data channel, SQL State: null, Error Code: 17002


Could someone point out where I am doing wrong? Thank's in advance!
Re: XML parsing function [message #349085 is a reply to message #349082] Thu, 18 September 2008 17:14 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
GOOGLE is your friend but only when you use it.

http://forum.springframework.org/archive/index.php/t-25300.html

http://www-01.ibm.com/support/docview.wss?uid=swg21142469

Previous Topic: Any simple way to achieve this
Next Topic: Question on ORDER BY
Goto Forum:
  


Current Time: Tue Dec 06 15:58:53 CST 2016

Total time taken to generate the page: 0.11218 seconds