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 <jjn1...@yahoo.com> wrote:
> From: John Napiorkowski <jjn1...@yahoo.com>
> Subject: Re: [Dbix-class] Oracle xmltype
> To: "DBIx::Class user and developer list" <dbix-class@lists.scsys.co.uk>
> Date: Thursday, April 30, 2009, 9:29 AM
>
>
> --- On Thu, 4/30/09, Morgon Hed <morgon...@yahoo.com>
> wrote:
>
> > From: Morgon Hed <morgon...@yahoo.com>
> > Subject: [Dbix-class] Oracle xmltype
> > To: dbix-class@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
>
_______________________________________________
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
