DBD::Oracle and XMLType

--0-1249414226-1114689004=:25930
Content-Type: text/plain; charset=us-ascii


Is there any way to bind a CLOB into the XMLType.createxml() call?

The createXML has 4 different signatures, one is varchar2, one is clob.

If the data is larger than 32k, I need to bind in a clob, but I am not really sure how to do it.

I read the DBD::Oracle docs and looked at the examples for manually using the ora_lob methods, but I am not sure if I can use this or notI might be able to.

Currently, I let the server(PL/SQL) do it. I pass a PL/SQL function a path, and PL/SQL creates the temporary lob and returns it to the createxml constructor, but if the server cannot access the file, this isn't an option. So I want to understand whether I can call this type method and pass it a CLOB directly from Perl.

I could insert the CLOB in another column using the traditional approach of getting the lob locator for the column and writing the clob data, and than calling another statement to create the xmltype from the CLOB column I just inserted, but I would rather go directly to the xmlType than insert another column.

any ideas?

Job

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--0-1249414226-1114689004=:25930--

Re: DBD::Oracle and XMLType

--0-384006531-1114691551=:8900
Content-Type: text/plain; charset=us-ascii

After a little searching of archives, I found the same exact problem and the same self-proposed workaround almost a year ago. Is there a better way without creating another column in a temp table? I couldn't find a follow-up to this post.

Job

To: dbi-users-help[at]perl.org, dbi-users[at]perl.org
Subject: Oracle 9 XMLTYPE insert
Message-ID: <20020424190852.C22854[at]byrd.biostat.ufl.edu>
From: marklee[at]ufl.edu (Mark Stillwell)


I'm using DBI 1.21 and DBD 1.12 with an oracle9i database backend.

Here is my problem, I have a table named 'test' with three fields: eid
(integer), x (SYS.XMLTYPE) and formname (text)

I create a database handler and connect to the database just fine.

I create a new statement handler with the following command:

$sth = $dbh->prepare("INSERT INTO test (eid, x, formname) VALUES (?,
SYS.XMLTYPE.CREATEXML(?), ?");

I loop over some data, $eid gets and integer, $xmlvalue gets a string,
and $formname gets a string. So long as $xmlvalue is relatively short
$sth->execute($eid, $xmlvalue, $formname); works great, but as soon as
it becomes long enough to force the use of clob's I have a problem.

So I tried the following:

$sth->bind_param(1, $i);
$sth->bind_param(2, $xmlvalue, { ora_type => ORA_CLOB });
$sth->bind_param(3, $intable);
$sth->execute;

This works great if column 'x' is a normal CLOB and I omit the
sys.xmltype.createxml statement above, but when 'x' is of type
sys.xmltype I get the following error:

nvalid LOB locator specified
ORA-06512: at "SYS.XMLTYPE", line 0

Right now I've hacked the setup so there is a supplemental table called
'y' of type CLOB that I submit to, then I do $dbh->do("UPDATE test SET x
= SYS.XMLTYPE.CREATEXML(y)");, which works but doesn't seem like the
right way to do this.

Is there any way to do what I want in the current version of
DBI/OracleDBD?



Job Miller wrote:

Is there any way to bind a CLOB into the XMLType.createxml() call?

The createXML has 4 different signatures, one is varchar2, one is clob.

If the data is larger than 32k, I need to bind in a clob, but I am not really sure how to do it.

I read the DBD::Oracle docs and looked at the examples for manually using the ora_lob methods, but I am not sure if I can use this or notI might be able to.

Currently, I let the server(PL/SQL) do it. I pass a PL/SQL function a path, and PL/SQL creates the temporary lob and returns it to the createxml constructor, but if the server cannot access the file, this isn't an option. So I want to understand whether I can call this type method and pass it a CLOB directly from Perl.

I could insert the CLOB in another column using the traditional approach of getting the lob locator for the column and writing the clob data, and than calling another statement to create the xmltype from the CLOB column I just inserted, but I would rather go directly to the xmlType than insert another column.

any ideas?

Job

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


---------------------------------
Do you Yahoo!?
Make Yahoo! your home page
--0-384006531-1114691551=:8900--

Re: DBD::Oracle and XMLType

On 04/28/2005 05:32 AM, Job Miller said:

> After a little searching of archives, I found the same exact problem
> and the same self-proposed workaround almost a year ago. Is there a
> better way without creating another column in a temp table? I
> couldn't find a follow-up to this post.

There is an ORA_CLOB ora_type, but there is a 32KB limit when passing
values to PL/SQL. So It appears that the workaround you found using an
intermediate table is still needed for larger CLOBs.

http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Placeholder _Binding_Attributes

http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Handling_LO Bs

> Job Miller wrote:
>
> Is there any way to bind a CLOB into the XMLType.createxml() call?

--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.