1 Reply Latest reply on Mar 21, 2005 2:59 PM by 406672

    problem with ADO

    439202

      I have defined a view to get the data from an XMLTYPE table into relational form. I have a problem with opening this view with ADO and Delphi 6.

      If I define my view like this:

      CREATE OR REPLACE VIEW xml_test (versionsnr)
      AS SELECT extractValue(value(xml),'/STANDAT/HEADER/@versionsnr')
      FROM xmltable xml

      I will the an error ORA-03114 when I try to select from the view with ADO. I have no problems when doing the select in sql*plus.

      When I define the view like this:

      CREATE OR REPLACE VIEW xml_test2 (versionsnr)
      AS SELECT extractValue(value(header),'/HEADER/@versionsnr')
      FROM xmltable xml,
      table(xmlsequence(extract(value(xml),'/STANDAT/HEADER'))) header

      ,then it works everywhere, but it is significally slower with large amounts of data.

      Interestingly enough, if the view contains a minus operator like below, then it also works everywhere.

      CREATE OR REPLACE VIEW xml_test3 (versionsnr)
      AS
      (SELECT extractValue(value(xml),'/STANDAT/HEADER/@versionsnr')
      FROM xmltable xml
      MINUS
      SELECT extractValue(value(xml),'/STANDAT/HEADER/@versionsnr')
      FROM xmltablenew xml)

      Do you have any experience with this? Is it a bug in ADO?

        • 1. Re: problem with ADO
          406672
          Viktor,

          Have you looked in the Oracle server trace file dump location to see if there are any trace files that correspond with the time/date of your Ora-03114 error.

          Secondly, Have you tried to trace your session to see what is happening?