rsim/oracle-enhanced — Issue #107: Error selecting and/or accesing xmltype fields
Author: cHaOs667
Created: 2011-10-14 14:27:25+00:00
State: open

I need to process some statements which uses xmltype fields.

As Rails and the oracle enhanced adapter does not support the xmltyp datafields i've created the following statement:
```SELECT extractValue(value, '/*[local-name()="Envelope"][1]/*[local-name()="Header"][1]/*[local-name()="Procedure"][1]/text()') AS procedure,
count(extractValue(value, '/*[local-name()="Envelope"][1]/*[local-name()="Header"][1]/*[local-name()="Procedure"][1]/text()')) AS number_of
FROM "XML_EVENTS"
WHERE
extractValue(value, '/*[local-name()="Header"][1]/*[local-name()="Transmitter"][1]/text()') = 'TestCompany' AND
extractValue(value, '/*[local-name()="Envelope"][1]/*[local-name()="Body"][1]/*/*/*[local-name()="Reference"][1]/text()') IS NOT NULL AND
client_timestamp between
to_timestamp('09.10.2011', 'DD.MM.YYYY') AND
to_timestamp('14.10.2011', 'DD.MM.YYYY'))
GROUP BY extractValue(value, '/*[local-name()="Envelope"][1]/*[local-name()="Header"][1]/*[local-name()="Procedure"][1]/text()';```

And this statements works pretty fine within the oracle sql developer and ruby applications using the plain ruby-oci8 connector but it does not work withing rails 3 with the oracle enhanced adapter :(

I've tried to implement it in the following way:
```procedures = XmlEvent.
select("extractValue(value, '/*[local-name()=\"Envelope\"][1]/*[local-name()=\"Header\"][1]/*[local-name()=\"Procedure\"][1]/text()') AS procedure,
count(extractValue(value, '/*[local-name()=\"Envelope\"][1]/*[local-name()=\"Header\"][1]/*[local-name()=\"Procedure\"][1]/text()')) AS number_of").
where("extractValue(value, '/*[local-name()=\"Header\"][1]/*[local-name()=\"Transmitter\"][1]/text()') = ? AND
extractValue(value, '/*[local-name()=\"Envelope\"][1]/*[local-name()=\"Body\"][1]/*/*/*[local-name()=\"Reference\"][1]/text()') IS NOT NULL AND
client_timestamp between to_timestamp(?, ) AND to_timestamp(?, )", company_name, (Time.now - (5*60*60*24)).strftime("%d.%m.%Y"), Time.now.strftime("%d.%m.%Y")).
group("extractValue(value, '/*[local-name()=\"Envelope\"][1]/*[local-name()=\"Header\"][1]/*[local-name()=\"Header\"][1]/*[local-name()=\"MessageTP\"][1]/*[local-name()=\"Procedure\"][1]/text()')")```

Every time i fire up this statement i just get an empty object inherited of ActiveRecord::Relation back.
Even when i'm using XmlEvent.connection.execute on the statement above to retreive an cursor i did not get any values back, only when i'm using a seperate plain ruby oci8 connection which bypasses active record and the oracle enhanced adapter.

My database is oracle 11g, i use ruby 1.8.7, rails 3.0.9, oracle enhanced adapter 1.4.0 (got the same issue with 1.3.x) and ruby-oci8 2.0.6 aswell oracle instant client 10.2 (happend on a test machine with oracle instant client 11 aswell).

I hope this is enough information to get this one cleared up.