[Dbix-class] Oracle xmltype

[Morgon Hed]

Ok thanks, that is at least giving me a starting point.
One more quirk though:

In order to get to the raw XML in plain DBI I have to do the follwing:

select xmltype.getclobval(xml_column) from table

i.e. I cannot simply select from xml_column but I have to pass that to the PL/SQL function xmltype.getclobval to get the xml as text.

How can I do that in the DBIx::Class scenario?

Many thanks!

--- On Thu, 4/30/09, John Napiorkowski <jjn1056 at yahoo.com> wrote:

> From: John Napiorkowski <jjn1056 at yahoo.com>
> Subject: Re: [Dbix-class] Oracle xmltype
> To: "DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
> Date: Thursday, April 30, 2009, 9:29 AM
> 
> 
> --- On Thu, 4/30/09, Morgon Hed <morgonhed at yahoo.com>
> wrote:
> 
> > From: Morgon Hed <morgonhed at yahoo.com>
> > Subject: [Dbix-class] Oracle xmltype
> > To: dbix-class at lists.scsys.co.uk
> > Date: Thursday, April 30, 2009, 7:28 AM
> > 
> > Hi,
> > 
> > I am rather new to DBIx::Class and I wonder if
> someone
> > could explain to me how to deal with Oracle
> > XmlType-columns.
> > 
> > I have a table which contains a column of type
> XmlType
> > and  I want that column to be inflated/deflated as
> an
> > object of it's own.
> > 
> > What I need to do in order to get to the raw XML in
> plain
> > DBI is to do something like the following:
> > 
> > $sth->bind_param(":xml", $xml, { ora_type =>
> > ORA_XMLTYPE } );
> > 
> > And I cannot figure out how to incorporate this
> binding
> > into DBIx::Class.
> > 
> > Please note that I want both to retrieve and to update
> this
> > column.
> > 
> > Many thanks!
> > 
> > Not 100% sure I know what you need, but I imagine this
> could be handled the same way we hacked bind parameters to
> handle how some databases need to know about bytea or blob
> types.  You might want to check out
> DBIx::Class::Storage::DBI::Pg and in particular the method
> 'bind_attribute_by_data_type' which is used to add some bind
> parameter support for postgresql.  Adding this to the
> Oracle DBD should be easy, something like:
> 
> (in package DBIx::Class::Storage::DBI::Oracle)
> 
> use DBD::Oracle qw(:ora_types); ## just guessing
> 
> sub bind_attribute_by_data_type {
>   my ($self,$data_type) = @_;
> 
>   my $bind_attributes = {
>     xml => { pg_type =>
> DBD::Oracle::ORA_XMLTYPE },
>   };
>  
>   if( defined $bind_attributes->{$data_type} ) {
>     return $bind_attributes->{$data_type};
>   }
>   else {
>     return;
>   }
> }
> 
> 
> and then in your storage class, under
> __PACKAGE__->add_columns you'd do:
> 
> __PACKAGE__->add_columns(
>   'xmltype' => {
>     data_type => 'xml',
>     is_nullable => 1,
>   },
>   .....
> 
> And it should just work, at least for reading and writing,
> if that's what you mean.  If you are asking about
> deeper support for the way Oracle (and now Postgresql I
> believe) has implemented sql functions and extensions to do
> stuff like selecting bits inside an xml stored doc, or doing
> joins againt info stored inside a xml document, right now I
> think you'll need to bind the functions as some of the
> documented examples for using functions in your SQLA.
> 
> jnapiorkowski
> 
> 
> John
> 
> 
> 
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>