Oracle XMLType data

Rails
1

Howdy folks,

Has anyone had any luck getting Oracle’s XMLType to play nicely with
Rails? According to the docs, it’s just a wrapper around CLOB.
However the ruby-oci8 driver throws an exception about user defined
types if you try to query a table with an XMLType column.

Any pointers would be appreciated.

Thanks,

-wilig

2

I haven’t played with it but it would be cool to get it back as a REXML
document.

Tony

3

Tony C. wrote:

I haven’t played with it but it would be cool to get it back as a REXML
document.

Yes, that would be cool. :slight_smile:

types if you try to query a table with an XMLType column.

I suspect it’s because the XMLType datatype was added in Oracle 9i. I’m
not
sure if Kubo would be willing to support this or if we need to wait for
ruby-oci9. :slight_smile:

I recommend posting a feature request on the ruby-oci8 project page.

Regards,

Dan

4

Kubo,

Thanks so much for your guidance. Works great. I also found the if
you TRIM the column during the select the clob value will be returned
as a string. I’m not sure if this will fail if the string is over
4096 characters though. Looking thru the 9i docs they claim to have
removed this limit.

Well either way, I now have a working solution. I will try my hand at
writing a plugin for Rails that allows you to retrieve the value as a
REXML document.

I’m thinking of something along these lines

class Product < ActiveRecord::Base
as_xml :description
end

Need it for a project I’m working on, and it will be a good way to get
to know ActiveRecord.

-wilig

5

Hi,

XMLTYPE can be retrieved as CLOB as following:

require ‘oci8’

def select_as_csv(conn, sqltext)
conn.exec(sqltext) do |row|
row.collect! do |col|
case col
when OCI8::LOB
col.read()
else
col
end
end
puts(row.join(‘,’))
end
end

conn = OCI8.new(‘ruby’, ‘oci8’)

#conn.exec(“CREATE TABLE po_tab OF XMLTYPE”)
#conn.exec(“CREATE TABLE po_xml_tab(poid NUMBER(10), poDoc XMLTYPE)”)

xml1 = ‘JohnSmith’
xml2 = ‘bar’

conn.exec(‘insert into po_tab values(:1)’, xml1)
conn.exec(‘insert into po_tab values(:1)’, xml2)
select_as_csv(conn, ‘select XMLTYPE.getClobVal(value(x)) from po_tab x’)

conn.exec(‘insert into po_xml_tab values(:1, :2)’, 1, xml1);
conn.exec(‘insert into po_xml_tab values(:1, :2)’, 2, xml2);
select_as_csv(conn, ‘select poid, XMLTYPE.getClobVal(podoc) from
po_xml_tab’)

conn.rollback

To insert a XML value, bind as a String.
To select a XML value, use XMLType.getClobVal(CLOBColumn).

I don’t know how to use it with Rails. :-<
I had not successed to use Rails with Oracle until last week, when
“Agile Web Develepment with Rails” Japanese edition was published.

I haven’t played with it but it would be cool to get it back as a REXML
document.

Yes, that would be cool. :slight_smile:

Me too.

I suspect it’s because the XMLType datatype was added in Oracle 9i. I’m not
sure if Kubo would be willing to support this or if we need to wait for
ruby-oci9. :slight_smile:

User defined types, which are added in Oracle 8, have not been
supported yet. :-p

I have a will to support XML Type. But ruby-oci8 is progressing in a
very slow tempo. It won’t be supported before summer.

–
KUBO Takehiro
email: [email protected]
web: http://www.jiubao.org
GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1 9F47 C001 1F93 AC08 2262

6

Patch submitted for adding Oracle XMLTYPE column support to Rails.
Hopefully it will be accepted, and the oracle_adapter will stop
failing when an XMLTYPE column is present.

Next step: the ‘as_xml’ plugin.

-wilig