Forum: Ruby on Rails Oracle XMLType data

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
William G. (Guest)
on 2006-03-06 21:29
(Received via mailing list)
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
Tony C. (Guest)
on 2006-03-07 01:04
(Received via mailing list)
I haven't played with it but it would be cool to get it back as a REXML
document.

Tony
Daniel B. (Guest)
on 2006-03-07 01:16
(Received via mailing list)
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. :)

>     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. :)

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

Regards,

Dan
KUBO Takehiro (Guest)
on 2006-03-07 15:15
(Received via mailing list)
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 = '<root><key>John</key><val>Smith</val></root>'
xml2 = '<root><key>bar</key><val></val></root>'

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. :)

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. :)

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: removed_email_address@domain.invalid
web:   http://www.jiubao.org
GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1  9F47 C001 1F93 AC08 2262
William G. (Guest)
on 2006-03-09 06:57
(Received via mailing list)
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
William G. (Guest)
on 2006-03-11 01:51
(Received via mailing list)
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
This topic is locked and can not be replied to.