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"} = [EMAIL PROTECTED];
> # 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
>> 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_XMLType.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=20050401
>>
>> connection = cx_Oracle.Connection("user/pw <at> 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.
