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

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

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=20

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]=20
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=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
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?

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?

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.=20
>=20
> 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.
>=20
> # 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") );=20
>=20
> NB: I'm presently testing this with DBD-Oracle-1.17 and DBI-1.52-r1 on
> Windows (ActiveState)
>=20
> On 2/13/07, Reidy, Ron wrote:
>> Well, it works for me out of the box as advertised.
>>=20
>> Code snippet:
>>=20
>> 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;
>>=20
>> Running on
>>=20
>> 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
>>=20
>> My files are around the order of 37Kb in size and I do nothing
>> special=20
>> with them (aside from convert the text into XML). My user has only
>> insert on the table with the XMLTYPE in it.
>>=20
>> Does the user running the Perl program have INSERT privs on the
>> table?=20
>> Is there a synonym issue or a role issue?
>>=20
>> --
>> Ron Reidy
>> Lead DBA
>> Array BioPharma, Inc.
>>=20
>>=20
>> -----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?
>>=20
>> It seems the current state of affairs is that for inserting to
>> XMLTYPE=20
>> 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)
>>=20
>> 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=20
>> no avail.
>>=20
>> What troubles me is that this _should_ work:
>> # NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
>> INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))"
>>=20
>> $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>=20
>> SQL_CLOB } );
>> $sth->execute or warn "INSERTXMLTYPE creation failure";
>>=20
>> 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=20
>> an alternative like "INSERT INTO tryit (formname, x) VALUES (?,
>> XMLTYPE(CAST(? as CLOB)))" and you get ORA-00932: inconsistent
>> datatypes.
>>=20
>> All of the above is old news I think.
>>=20
>> Why I raise this now is that I discovered the python guys seem to
>> have=20
>> got it working OK. See
>> http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=3 D20050401
>>=20
>> 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")
>>=20
>> Seems very much like a binding issue on the DBI/DBD side.
>>=20
>> Any thoughts?
>>=20
>> ~paul
>>=20
>> 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. =20

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

something didn't like me attaching the .pl file, so resending with a
link instead.

You can find the testcase at:
http://paulg.homelinux.com:8000/sources/xmltypeinsert-testca se.pl

On 2/17/07, Paul Gallagher wrote:
> 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?

Thanks Philip for the extra info.

I've posted a test case I'm working with at
http://paulg.homelinux.com:8000/sources/xmltypeinsert-testca se.pl ..
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.
> > >
> > >
> >
> >
>