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"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > 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)"" <[EMAIL PROTECTED]> wrote in
> > message news:[EMAIL PROTECTED]
> > I ran this against v1.19 and had the same limit.
> >
> > -----Original Message-----
> > From: Paul Gallagher [mailto:[EMAIL PROTECTED]
> > 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) <[EMAIL PROTECTED]>
> > 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"} = [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.
> >>
> >>
> >
> >
>
