RE: :Oracle - Any advance on inserting CLOB to XMLTYPE?

I ran this against v1.19 and had the same limit.

-----Original Message-----
From: Paul Gallagher [mailto:gallagher.paul@gmail.com]=20
Sent: Friday, February 16, 2007 8:25 PM
To: Garrett, Philip (MAN-Corporate)
Cc: Reidy, Ron; dbi-users@perl.org
Subject: Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?

Thanks Philip for the extra info.

I've attached a test case I'm working with .. wondering if anyone
using DBD 1.19 can try this out too and report the results?

Ron, can you check this also ... maybe your code is different in some
way?

For me the limit is at 63/64 elements (results are in the file)

perl xmltypeinsert-testcase.pl ORCL scott tiger 63
- is ok
perl xmltypeinsert-testcase.pl ORCL scott tiger 64
- fails


On 2/13/07, Garrett, Philip (MAN-Corporate)
wrote:
>
> I can confirm I have the same problems as Paul when inserting large
> XMLType.
>
> Running on:
> 1. SUSE LINUX Enterprise Server 9 (i586)
> 2. DBI v1.50
> 3. DBD::Oracle v1.18
> 4. Oracle client: 9.2.0.4.0
> 5. Oracle server: 9.2.0.7.0 - 64bit
>
> Regards,
> Philip
>
> Paul Gallagher wrote:
> > Thanks for the info Ron. I think you may not have quite hit the
limit
> > though.
> >
> > I tried your code (are you explicitly typing the bind to
:file_header?
> > I'm assuming not), and for small data sizes its ok, but once I go
very
> > large it fails.
> >
> > # fyi, I'm creating a long structure like this. max i 10 is ok, max
i
> > 3000 is not:
> > my @books;
> > my %dslong;
> > for (my $i=3D1; $i<3000; $i++) {
> > push(@books, {id =3D> $i, title =3D> [ "the book $i title" ] } =
);
> > }
> > $dslong{"book"} =3D \@books;
> > # and binding like this:
> > $sth->bind_param(":file_header", XMLout( \%dslong , RootName =3D>
> > "books") );
> >
> > NB: I'm presently testing this with DBD-Oracle-1.17 and DBI-1.52-r1
on
> > Windows (ActiveState)
> >
> > On 2/13/07, Reidy, Ron wrote:
> >> Well, it works for me out of the box as advertised.
> >>
> >> Code snippet:
> >>
> >> my $sth_admin_audit_files =3D $dbh->prepare(qq{
> >> INSERT INTO array_audit.admin_audit_files
> >> (instance_id
> >> ,file_crdt
> >> ,fname
> >> ,file_header
> >> ,file_header_raw
> >> )
> >> VALUES
> >> (:instance_id
> >> ,TO_DATE(:file_crdt, 'DD/MM/YYYY HH24:MI:SS')
> >> ,:fname
> >> ,SYS.XMLType.CREATEXML(:file_header)
> >> ,:file_header_raw
> >> )
> >> RETURNING admin_audit_file_id, crmo
> >> INTO :admin_audit_file_id, :crmo
> >> }) || die $DBI::errstr;
> >>
> >> Running on
> >>
> >> 1. RH Linux 3.0 and 4.0
> >> 2. DBI v1.47
> >> 3. DBD::Oracle v1.16
> >> 4. Oracle v9.2.0.5.0; v10.2.1.0.3; v10.2.0.2.0
> >>
> >> My files are around the order of 37Kb in size and I do nothing
> >> special
> >> with them (aside from convert the text into XML). My user has only
> >> insert on the table with the XMLTYPE in it.
> >>
> >> Does the user running the Perl program have INSERT privs on the
> >> table?
> >> Is there a synonym issue or a role issue?
> >>
> >> --
> >> Ron Reidy
> >> Lead DBA
> >> Array BioPharma, Inc.
> >>
> >>
> >> -----Original Message-----
> >> From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
> >> Sent: Monday, February 12, 2007 12:04 PM
> >> To: dbi-users@perl.org
> >> Subject: DBD::Oracle - Any advance on inserting CLOB to XMLTYPE?
> >>
> >> It seems the current state of affairs is that for inserting to
> >> XMLTYPE
> >> fields:
> >> a) for <32k, can just insert text
> >> b) for >32k, must insert to CLOB, then use a procedure to update
> >> XMLTYPE (see
> >>
> http://www.issociate.de/board/post/206125/DBD::Oracle_and_XM LType.html
> >> for a summary of the details)
> >>
> >> Personally I've tried just about every trick in the book to try and
> >> get a direct-insert of large xml documents into xmltype fields, but
> >> to
> >> no avail.
> >>
> >> What troubles me is that this _should_ work:
> >> # NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
> >> INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))"
> >>
> >> $sth =3D $dbh->prepare( "INSERT INTO tryit (formname, x) VALUES =
(?,
> >> XMLTYPE(?))" );
> >> $sth->bind_param(1, "INSERTXMLTYPE" );
> >> $sth->bind_param(2, XMLout( \%dslong , RootName =3D> "books"), { =
TYPE
> >> =3D>
> >> SQL_CLOB } );
> >> $sth->execute or warn "INSERTXMLTYPE creation failure";
> >>
> >> but it actually just gives ORA-00942: table or view does not exist.
> >> this is a bogus message (search metalink for "XMLTYPE ORA-00942").
> >> try
> >> an alternative like "INSERT INTO tryit (formname, x) VALUES (?,
> >> XMLTYPE(CAST(? as CLOB)))" and you get ORA-00932: inconsistent
> >> datatypes.
> >>
> >> All of the above is old news I think.
> >>
> >> Why I raise this now is that I discovered the python guys seem to
> >> have
> >> got it working OK. See
> >> =
http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=3 D20050401
> >>
> >> connection =3D cx_Oracle.Connection("user/pw tns")
> >> cursor =3D connection.cursor()
> >> cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> >> cursor.execute("insert into xmltable values (xmltype(:value))",
> >> value =3D "A very long XML string")
> >>
> >> Seems very much like a binding issue on the DBI/DBD side.
> >>
> >> Any thoughts?
> >>
> >> ~paul
> >>
> >> This electronic message transmission is a PRIVATE communication
> >> which contains information which may be confidential or privileged.
> >> The information is intended to be for the use of the individual or
> >> entity named above. If you are not the intended recipient, please
be
> >> aware that any disclosure, copying, distribution or use of the
> >> contents of this information is prohibited. Please notify the
sender
> >> of the delivery error by replying to this message, or notify us by
> >> telephone (877-633-2436, ext. 0), and then delete it from your
> >> system.
>
>

Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?

Sorry to be a Johnny come lately on this (pun intended)

I have been playing with this myself and like you chaps have come up with a
blank. End up with ORA-00942 error

I did spend some time to see how the python guys did it but as I can see
they just did it as a cursor which I think we can do as well in DBD::Oracle.
It is too late at night for me to give it a try though.

I have stepped though all the code and can see nothing awry.

I think our only alternative is to build up the clob in hunks then try to
run it using the LOB Locator Methods We could indicate this with a new ORA
type called ORA_XMLTYPE or alike????

Cheers

Of by the way any of you going the
http://vienna.yapceurope.org/ye2007/index.html YAPC in Viena in Aug.


""Garrett, Philip (MAN-Corporate)"" wrote in
message news:D9C13100F14E4C4795A1E83B125B40350232BB81@MSCEXCHS02.man .co...
I ran this against v1.19 and had the same limit.

-----Original Message-----
From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
Sent: Friday, February 16, 2007 8:25 PM
To: Garrett, Philip (MAN-Corporate)
Cc: Reidy, Ron; dbi-users@perl.org
Subject: Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?

Thanks Philip for the extra info.

I've attached a test case I'm working with .. wondering if anyone
using DBD 1.19 can try this out too and report the results?

Ron, can you check this also ... maybe your code is different in some
way?

For me the limit is at 63/64 elements (results are in the file)

perl xmltypeinsert-testcase.pl ORCL scott tiger 63
- is ok
perl xmltypeinsert-testcase.pl ORCL scott tiger 64
- fails


On 2/13/07, Garrett, Philip (MAN-Corporate)
wrote:
>
> I can confirm I have the same problems as Paul when inserting large
> XMLType.
>
> Running on:
> 1. SUSE LINUX Enterprise Server 9 (i586)
> 2. DBI v1.50
> 3. DBD::Oracle v1.18
> 4. Oracle client: 9.2.0.4.0
> 5. Oracle server: 9.2.0.7.0 - 64bit
>
> Regards,
> Philip
>
> Paul Gallagher wrote:
> > Thanks for the info Ron. I think you may not have quite hit the
limit
> > though.
> >
> > I tried your code (are you explicitly typing the bind to
:file_header?
> > I'm assuming not), and for small data sizes its ok, but once I go
very
> > large it fails.
> >
> > # fyi, I'm creating a long structure like this. max i 10 is ok, max
i
> > 3000 is not:
> > my @books;
> > my %dslong;
> > for (my $i=1; $i<3000; $i++) {
> > push(@books, {id => $i, title => [ "the book $i title" ] } );
> > }
> > $dslong{"book"} = \@books;
> > # and binding like this:
> > $sth->bind_param(":file_header", XMLout( \%dslong , RootName =>
> > "books") );
> >
> > NB: I'm presently testing this with DBD-Oracle-1.17 and DBI-1.52-r1
on
> > Windows (ActiveState)
> >
> > On 2/13/07, Reidy, Ron wrote:
> >> Well, it works for me out of the box as advertised.
> >>
> >> Code snippet:
> >>
> >> my $sth_admin_audit_files = $dbh->prepare(qq{
> >> INSERT INTO array_audit.admin_audit_files
> >> (instance_id
> >> ,file_crdt
> >> ,fname
> >> ,file_header
> >> ,file_header_raw
> >> )
> >> VALUES
> >> (:instance_id
> >> ,TO_DATE(:file_crdt, 'DD/MM/YYYY HH24:MI:SS')
> >> ,:fname
> >> ,SYS.XMLType.CREATEXML(:file_header)
> >> ,:file_header_raw
> >> )
> >> RETURNING admin_audit_file_id, crmo
> >> INTO :admin_audit_file_id, :crmo
> >> }) || die $DBI::errstr;
> >>
> >> Running on
> >>
> >> 1. RH Linux 3.0 and 4.0
> >> 2. DBI v1.47
> >> 3. DBD::Oracle v1.16
> >> 4. Oracle v9.2.0.5.0; v10.2.1.0.3; v10.2.0.2.0
> >>
> >> My files are around the order of 37Kb in size and I do nothing
> >> special
> >> with them (aside from convert the text into XML). My user has only
> >> insert on the table with the XMLTYPE in it.
> >>
> >> Does the user running the Perl program have INSERT privs on the
> >> table?
> >> Is there a synonym issue or a role issue?
> >>
> >> --
> >> Ron Reidy
> >> Lead DBA
> >> Array BioPharma, Inc.
> >>
> >>
> >> -----Original Message-----
> >> From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
> >> Sent: Monday, February 12, 2007 12:04 PM
> >> To: dbi-users@perl.org
> >> Subject: DBD::Oracle - Any advance on inserting CLOB to XMLTYPE?
> >>
> >> It seems the current state of affairs is that for inserting to
> >> XMLTYPE
> >> fields:
> >> a) for <32k, can just insert text
> >> b) for >32k, must insert to CLOB, then use a procedure to update
> >> XMLTYPE (see
> >>
> http://www.issociate.de/board/post/206125/DBD::Oracle_and_XM LType.html
> >> for a summary of the details)
> >>
> >> Personally I've tried just about every trick in the book to try and
> >> get a direct-insert of large xml documents into xmltype fields, but
> >> to
> >> no avail.
> >>
> >> What troubles me is that this _should_ work:
> >> # NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
> >> INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))"
> >>
> >> $sth = $dbh->prepare( "INSERT INTO tryit (formname, x) VALUES (?,
> >> XMLTYPE(?))" );
> >> $sth->bind_param(1, "INSERTXMLTYPE" );
> >> $sth->bind_param(2, XMLout( \%dslong , RootName => "books"), { TYPE
> >> =>
> >> SQL_CLOB } );
> >> $sth->execute or warn "INSERTXMLTYPE creation failure";
> >>
> >> but it actually just gives ORA-00942: table or view does not exist.
> >> this is a bogus message (search metalink for "XMLTYPE ORA-00942").
> >> try
> >> an alternative like "INSERT INTO tryit (formname, x) VALUES (?,
> >> XMLTYPE(CAST(? as CLOB)))" and you get ORA-00932: inconsistent
> >> datatypes.
> >>
> >> All of the above is old news I think.
> >>
> >> Why I raise this now is that I discovered the python guys seem to
> >> have
> >> got it working OK. See
> >> http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=2 0050401
> >>
> >> connection = cx_Oracle.Connection("user/pw tns")
> >> cursor = connection.cursor()
> >> cursor.setinputsizes(value = cx_Oracle.CLOB)
> >> cursor.execute("insert into xmltable values (xmltype(:value))",
> >> value = "A very long XML string")
> >>
> >> Seems very much like a binding issue on the DBI/DBD side.
> >>
> >> Any thoughts?
> >>
> >> ~paul
> >>
> >> This electronic message transmission is a PRIVATE communication
> >> which contains information which may be confidential or privileged.
> >> The information is intended to be for the use of the individual or
> >> entity named above. If you are not the intended recipient, please
be
> >> aware that any disclosure, copying, distribution or use of the
> >> contents of this information is prohibited. Please notify the
sender
> >> of the delivery error by replying to this message, or notify us by
> >> telephone (877-633-2436, ext. 0), and then delete it from your
> >> system.
>
>

Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?

Well I have been hacking this one to death this weekend. Did manage to get
it to work using a SP that creats a lob pointer before we do the update but
this is a very bad hack on my part. I think this will require lob locators
as the function SYS.XMLType.CREATEXML requires all the CLOB there before it
runs, So we might have to

1) prepare the SQL
2) upload the CLOB
3) bind
4) execute the statment.

I do not like this myself.

I will look at the Lob locators and see if I can work it out with that later
this week.

cheers All

""John Scoles"" wrote in message
news:20070406013714.19853.qmail@lists.develooper.com...
> Sorry to be a Johnny come lately on this (pun intended)
>
> I have been playing with this myself and like you chaps have come up with
> a blank. End up with ORA-00942 error
>
> I did spend some time to see how the python guys did it but as I can see
> they just did it as a cursor which I think we can do as well in
> DBD::Oracle. It is too late at night for me to give it a try though.
>
> I have stepped though all the code and can see nothing awry.
>
> I think our only alternative is to build up the clob in hunks then try to
> run it using the LOB Locator Methods We could indicate this with a new
> ORA type called ORA_XMLTYPE or alike????
>
> Cheers
>
> Of by the way any of you going the
> http://vienna.yapceurope.org/ye2007/index.html YAPC in Viena in Aug.
>
>
> ""Garrett, Philip (MAN-Corporate)"" wrote in
> message news:D9C13100F14E4C4795A1E83B125B40350232BB81@MSCEXCHS02.man .co...
> I ran this against v1.19 and had the same limit.
>
> -----Original Message-----
> From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
> Sent: Friday, February 16, 2007 8:25 PM
> To: Garrett, Philip (MAN-Corporate)
> Cc: Reidy, Ron; dbi-users@perl.org
> Subject: Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?
>
> Thanks Philip for the extra info.
>
> I've attached a test case I'm working with .. wondering if anyone
> using DBD 1.19 can try this out too and report the results?
>
> Ron, can you check this also ... maybe your code is different in some
> way?
>
> For me the limit is at 63/64 elements (results are in the file)
>
> perl xmltypeinsert-testcase.pl ORCL scott tiger 63
> - is ok
> perl xmltypeinsert-testcase.pl ORCL scott tiger 64
> - fails
>
>
> On 2/13/07, Garrett, Philip (MAN-Corporate)
> wrote:
>>
>> I can confirm I have the same problems as Paul when inserting large
>> XMLType.
>>
>> Running on:
>> 1. SUSE LINUX Enterprise Server 9 (i586)
>> 2. DBI v1.50
>> 3. DBD::Oracle v1.18
>> 4. Oracle client: 9.2.0.4.0
>> 5. Oracle server: 9.2.0.7.0 - 64bit
>>
>> Regards,
>> Philip
>>
>> Paul Gallagher wrote:
>> > Thanks for the info Ron. I think you may not have quite hit the
> limit
>> > though.
>> >
>> > I tried your code (are you explicitly typing the bind to
> :file_header?
>> > I'm assuming not), and for small data sizes its ok, but once I go
> very
>> > large it fails.
>> >
>> > # fyi, I'm creating a long structure like this. max i 10 is ok, max
> i
>> > 3000 is not:
>> > my @books;
>> > my %dslong;
>> > for (my $i=1; $i<3000; $i++) {
>> > push(@books, {id => $i, title => [ "the book $i title" ] } );
>> > }
>> > $dslong{"book"} = \@books;
>> > # and binding like this:
>> > $sth->bind_param(":file_header", XMLout( \%dslong , RootName =>
>> > "books") );
>> >
>> > NB: I'm presently testing this with DBD-Oracle-1.17 and DBI-1.52-r1
> on
>> > Windows (ActiveState)
>> >
>> > On 2/13/07, Reidy, Ron wrote:
>> >> Well, it works for me out of the box as advertised.
>> >>
>> >> Code snippet:
>> >>
>> >> my $sth_admin_audit_files = $dbh->prepare(qq{
>> >> INSERT INTO array_audit.admin_audit_files
>> >> (instance_id
>> >> ,file_crdt
>> >> ,fname
>> >> ,file_header
>> >> ,file_header_raw
>> >> )
>> >> VALUES
>> >> (:instance_id
>> >> ,TO_DATE(:file_crdt, 'DD/MM/YYYY HH24:MI:SS')
>> >> ,:fname
>> >> ,SYS.XMLType.CREATEXML(:file_header)
>> >> ,:file_header_raw
>> >> )
>> >> RETURNING admin_audit_file_id, crmo
>> >> INTO :admin_audit_file_id, :crmo
>> >> }) || die $DBI::errstr;
>> >>
>> >> Running on
>> >>
>> >> 1. RH Linux 3.0 and 4.0
>> >> 2. DBI v1.47
>> >> 3. DBD::Oracle v1.16
>> >> 4. Oracle v9.2.0.5.0; v10.2.1.0.3; v10.2.0.2.0
>> >>
>> >> My files are around the order of 37Kb in size and I do nothing
>> >> special
>> >> with them (aside from convert the text into XML). My user has only
>> >> insert on the table with the XMLTYPE in it.
>> >>
>> >> Does the user running the Perl program have INSERT privs on the
>> >> table?
>> >> Is there a synonym issue or a role issue?
>> >>
>> >> --
>> >> Ron Reidy
>> >> Lead DBA
>> >> Array BioPharma, Inc.
>> >>
>> >>
>> >> -----Original Message-----
>> >> From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
>> >> Sent: Monday, February 12, 2007 12:04 PM
>> >> To: dbi-users@perl.org
>> >> Subject: DBD::Oracle - Any advance on inserting CLOB to XMLTYPE?
>> >>
>> >> It seems the current state of affairs is that for inserting to
>> >> XMLTYPE
>> >> fields:
>> >> a) for <32k, can just insert text
>> >> b) for >32k, must insert to CLOB, then use a procedure to update
>> >> XMLTYPE (see
>> >>
>> http://www.issociate.de/board/post/206125/DBD::Oracle_and_XM LType.html
>> >> for a summary of the details)
>> >>
>> >> Personally I've tried just about every trick in the book to try and
>> >> get a direct-insert of large xml documents into xmltype fields, but
>> >> to
>> >> no avail.
>> >>
>> >> What troubles me is that this _should_ work:
>> >> # NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
>> >> INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))"
>> >>
>> >> $sth = $dbh->prepare( "INSERT INTO tryit (formname, x) VALUES (?,
>> >> XMLTYPE(?))" );
>> >> $sth->bind_param(1, "INSERTXMLTYPE" );
>> >> $sth->bind_param(2, XMLout( \%dslong , RootName => "books"), { TYPE
>> >> =>
>> >> SQL_CLOB } );
>> >> $sth->execute or warn "INSERTXMLTYPE creation failure";
>> >>
>> >> but it actually just gives ORA-00942: table or view does not exist.
>> >> this is a bogus message (search metalink for "XMLTYPE ORA-00942").
>> >> try
>> >> an alternative like "INSERT INTO tryit (formname, x) VALUES (?,
>> >> XMLTYPE(CAST(? as CLOB)))" and you get ORA-00932: inconsistent
>> >> datatypes.
>> >>
>> >> All of the above is old news I think.
>> >>
>> >> Why I raise this now is that I discovered the python guys seem to
>> >> have
>> >> got it working OK. See
>> >> http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=2 0050401
>> >>
>> >> connection = cx_Oracle.Connection("user/pw tns")
>> >> cursor = connection.cursor()
>> >> cursor.setinputsizes(value = cx_Oracle.CLOB)
>> >> cursor.execute("insert into xmltable values (xmltype(:value))",
>> >> value = "A very long XML string")
>> >>
>> >> Seems very much like a binding issue on the DBI/DBD side.
>> >>
>> >> Any thoughts?
>> >>
>> >> ~paul
>> >>
>> >> This electronic message transmission is a PRIVATE communication
>> >> which contains information which may be confidential or privileged.
>> >> The information is intended to be for the use of the individual or
>> >> entity named above. If you are not the intended recipient, please
> be
>> >> aware that any disclosure, copying, distribution or use of the
>> >> contents of this information is prohibited. Please notify the
> sender
>> >> of the delivery error by replying to this message, or notify us by
>> >> telephone (877-633-2436, ext. 0), and then delete it from your
>> >> system.
>>
>>
>
>

Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?

FYI, my original vision for this was that the Oracle::OCI module would
provide full access to the (vast and powerful) Oracle OCI API.

DBD::Oracle and Oracle::OCI work together such that any Oracle::OCI
function that needs an OCI handle can be given a suitable DBI handle.

Here's an example using OCILobRead and OCILobWrite to edit a lob in
chunks. Notice how $dbh is used to supply the relevant handles:

for ( my $offset=1; $chunk == 5 ; $offset += $chunk ) {
OCILobRead($dbh, $dbh, $lob_locator, $chunk, $offset,
oci_buf_len($lob_buf, 200, \$chunk), 0,0, 0,0 );
$lob_buf =~ s/old/new/g;
$status = OCILobWrite($dbh, $dbh, $lob_locator, length($lob_buf), $offset,
oci_buf_len($lob_buf), OCI_ONE_PIECE, 0,0, 0, 1 )
if $chunk;
warn get_oci_error($dbh, $status, 'OCILobWrite') if $status != OCI_SUCCESS;
}

For more examples, including OCIDescribeAny, see http://search.cpan.org/src/TIMB/Oracle-OCI-0.06/05dbi.t

If someone else would like to take over maintenance of Oracle::OCI
I'd be very happy. It deserves to be more widely used.

Tim.

On Mon, Apr 09, 2007 at 02:33:41PM -0400, John Scoles wrote:
> Well I have been hacking this one to death this weekend. Did manage to get
> it to work using a SP that creats a lob pointer before we do the update but
> this is a very bad hack on my part. I think this will require lob locators
> as the function SYS.XMLType.CREATEXML requires all the CLOB there before it
> runs, So we might have to
>
> 1) prepare the SQL
> 2) upload the CLOB
> 3) bind
> 4) execute the statment.
>
> I do not like this myself.
>
> I will look at the Lob locators and see if I can work it out with that later
> this week.
>
> cheers All
>
> ""John Scoles"" wrote in message
> news:20070406013714.19853.qmail@lists.develooper.com...
> > Sorry to be a Johnny come lately on this (pun intended)
> >
> > I have been playing with this myself and like you chaps have come up with
> > a blank. End up with ORA-00942 error
> >
> > I did spend some time to see how the python guys did it but as I can see
> > they just did it as a cursor which I think we can do as well in
> > DBD::Oracle. It is too late at night for me to give it a try though.
> >
> > I have stepped though all the code and can see nothing awry.
> >
> > I think our only alternative is to build up the clob in hunks then try to
> > run it using the LOB Locator Methods We could indicate this with a new
> > ORA type called ORA_XMLTYPE or alike????
> >
> > Cheers
> >
> > Of by the way any of you going the
> > http://vienna.yapceurope.org/ye2007/index.html YAPC in Viena in Aug.
> >
> >
> > ""Garrett, Philip (MAN-Corporate)"" wrote in
> > message news:D9C13100F14E4C4795A1E83B125B40350232BB81@MSCEXCHS02.man .co...
> > I ran this against v1.19 and had the same limit.
> >
> > -----Original Message-----
> > From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
> > Sent: Friday, February 16, 2007 8:25 PM
> > To: Garrett, Philip (MAN-Corporate)
> > Cc: Reidy, Ron; dbi-users@perl.org
> > Subject: Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?
> >
> > Thanks Philip for the extra info.
> >
> > I've attached a test case I'm working with .. wondering if anyone
> > using DBD 1.19 can try this out too and report the results?
> >
> > Ron, can you check this also ... maybe your code is different in some
> > way?
> >
> > For me the limit is at 63/64 elements (results are in the file)
> >
> > perl xmltypeinsert-testcase.pl ORCL scott tiger 63
> > - is ok
> > perl xmltypeinsert-testcase.pl ORCL scott tiger 64
> > - fails
> >
> >
> > On 2/13/07, Garrett, Philip (MAN-Corporate)
> > wrote:
> >>
> >> I can confirm I have the same problems as Paul when inserting large
> >> XMLType.
> >>
> >> Running on:
> >> 1. SUSE LINUX Enterprise Server 9 (i586)
> >> 2. DBI v1.50
> >> 3. DBD::Oracle v1.18
> >> 4. Oracle client: 9.2.0.4.0
> >> 5. Oracle server: 9.2.0.7.0 - 64bit
> >>
> >> Regards,
> >> Philip
> >>
> >> Paul Gallagher wrote:
> >> > Thanks for the info Ron. I think you may not have quite hit the
> > limit
> >> > though.
> >> >
> >> > I tried your code (are you explicitly typing the bind to
> > :file_header?
> >> > I'm assuming not), and for small data sizes its ok, but once I go
> > very
> >> > large it fails.
> >> >
> >> > # fyi, I'm creating a long structure like this. max i 10 is ok, max
> > i
> >> > 3000 is not:
> >> > my @books;
> >> > my %dslong;
> >> > for (my $i=1; $i<3000; $i++) {
> >> > push(@books, {id => $i, title => [ "the book $i title" ] } );
> >> > }
> >> > $dslong{"book"} = \@books;
> >> > # and binding like this:
> >> > $sth->bind_param(":file_header", XMLout( \%dslong , RootName =>
> >> > "books") );
> >> >
> >> > NB: I'm presently testing this with DBD-Oracle-1.17 and DBI-1.52-r1
> > on
> >> > Windows (ActiveState)
> >> >
> >> > On 2/13/07, Reidy, Ron wrote:
> >> >> Well, it works for me out of the box as advertised.
> >> >>
> >> >> Code snippet:
> >> >>
> >> >> my $sth_admin_audit_files = $dbh->prepare(qq{
> >> >> INSERT INTO array_audit.admin_audit_files
> >> >> (instance_id
> >> >> ,file_crdt
> >> >> ,fname
> >> >> ,file_header
> >> >> ,file_header_raw
> >> >> )
> >> >> VALUES
> >> >> (:instance_id
> >> >> ,TO_DATE(:file_crdt, 'DD/MM/YYYY HH24:MI:SS')
> >> >> ,:fname
> >> >> ,SYS.XMLType.CREATEXML(:file_header)
> >> >> ,:file_header_raw
> >> >> )
> >> >> RETURNING admin_audit_file_id, crmo
> >> >> INTO :admin_audit_file_id, :crmo
> >> >> }) || die $DBI::errstr;
> >> >>
> >> >> Running on
> >> >>
> >> >> 1. RH Linux 3.0 and 4.0
> >> >> 2. DBI v1.47
> >> >> 3. DBD::Oracle v1.16
> >> >> 4. Oracle v9.2.0.5.0; v10.2.1.0.3; v10.2.0.2.0
> >> >>
> >> >> My files are around the order of 37Kb in size and I do nothing
> >> >> special
> >> >> with them (aside from convert the text into XML). My user has only
> >> >> insert on the table with the XMLTYPE in it.
> >> >>
> >> >> Does the user running the Perl program have INSERT privs on the
> >> >> table?
> >> >> Is there a synonym issue or a role issue?
> >> >>
> >> >> --
> >> >> Ron Reidy
> >> >> Lead DBA
> >> >> Array BioPharma, Inc.
> >> >>
> >> >>
> >> >> -----Original Message-----
> >> >> From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
> >> >> Sent: Monday, February 12, 2007 12:04 PM
> >> >> To: dbi-users@perl.org
> >> >> Subject: DBD::Oracle - Any advance on inserting CLOB to XMLTYPE?
> >> >>
> >> >> It seems the current state of affairs is that for inserting to
> >> >> XMLTYPE
> >> >> fields:
> >> >> a) for <32k, can just insert text
> >> >> b) for >32k, must insert to CLOB, then use a procedure to update
> >> >> XMLTYPE (see
> >> >>
> >> http://www.issociate.de/board/post/206125/DBD::Oracle_and_XM LType.html
> >> >> for a summary of the details)
> >> >>
> >> >> Personally I've tried just about every trick in the book to try and
> >> >> get a direct-insert of large xml documents into xmltype fields, but
> >> >> to
> >> >> no avail.
> >> >>
> >> >> What troubles me is that this _should_ work:
> >> >> # NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
> >> >> INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))"
> >> >>
> >> >> $sth = $dbh->prepare( "INSERT INTO tryit (formname, x) VALUES (?,
> >> >> XMLTYPE(?))" );
> >> >> $sth->bind_param(1, "INSERTXMLTYPE" );
> >> >> $sth->bind_param(2, XMLout( \%dslong , RootName => "books"), { TYPE
> >> >> =>
> >> >> SQL_CLOB } );
> >> >> $sth->execute or warn "INSERTXMLTYPE creation failure";
> >> >>
> >> >> but it actually just gives ORA-00942: table or view does not exist.
> >> >> this is a bogus message (search metalink for "XMLTYPE ORA-00942").
> >> >> try
> >> >> an alternative like "INSERT INTO tryit (formname, x) VALUES (?,
> >> >> XMLTYPE(CAST(? as CLOB)))" and you get ORA-00932: inconsistent
> >> >> datatypes.
> >> >>
> >> >> All of the above is old news I think.
> >> >>
> >> >> Why I raise this now is that I discovered the python guys seem to
> >> >> have
> >> >> got it working OK. See
> >> >> http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=2 0050401
> >> >>
> >> >> connection = cx_Oracle.Connection("user/pw tns")
> >> >> cursor = connection.cursor()
> >> >> cursor.setinputsizes(value = cx_Oracle.CLOB)
> >> >> cursor.execute("insert into xmltable values (xmltype(:value))",
> >> >> value = "A very long XML string")
> >> >>
> >> >> Seems very much like a binding issue on the DBI/DBD side.
> >> >>
> >> >> Any thoughts?
> >> >>
> >> >> ~paul
> >> >>
> >> >> This electronic message transmission is a PRIVATE communication
> >> >> which contains information which may be confidential or privileged.
> >> >> The information is intended to be for the use of the individual or
> >> >> entity named above. If you are not the intended recipient, please
> > be
> >> >> aware that any disclosure, copying, distribution or use of the
> >> >> contents of this information is prohibited. Please notify the
> > sender
> >> >> of the delivery error by replying to this message, or notify us by
> >> >> telephone (877-633-2436, ext. 0), and then delete it from your
> >> >> system.
> >>
> >>
> >
> >
>