On Wed, Mar 22, 2006 at 08:15:46AM -0500, [EMAIL PROTECTED] wrote: > This patch allows LOBs to be bound to input variables. I wanted to > send large amounts of data to stored procs without using a temp table > (or, at least wanted to see if I could).
Great. Thanks Jeffrey. Could you resend that as a context diff (diff -u)? See http://search.cpan.org/~timb/DBD-Oracle/Oracle.pm#CONTRIBUTING Checking the return value of OCILobLocatorAssign and reporting errors like we do for other OCI calls would also be great. Finally, any chance you could write some tests (in the t/*.t files) for it and include them in the diff? > I've thought of extending ora_auto_lob to automatically write and read > LOBs for OCI_STMT_BEGIN/DECLARE, would this be worthwhile? I'm not sure what you mean here. Tim. > -Jeff > > > dbdimp.c > > 1419a1420 > > && !sv_derived_from(newvalue, "OCILobLocatorPtr" ) /* input > LOB locator*/ > > oci8.c > > 690a691 > > D_imp_dbh_from_sth ; > 719a721,731 > > /* accept input LOBs */ > > > > if (sv_derived_from(phs->sv, "OCILobLocatorPtr")) { > > OCILobLocator *src; > > OCILobLocator **dest; > > src = INT2PTR(OCILobLocator *, SvIV(SvRV(phs->sv))); > > dest = (OCILobLocator **) phs->progv; > > > > OCILobLocatorAssign(imp_dbh->svchp, imp_dbh->errhp, src, dest); > > } > > > > > sample script: > > # .. $dbh = DBI->connect ... > > > my $clob; > > > my $create_sth = $dbh->prepare( <<EOQ, { ora_auto_lob => 0 } ); > BEGIN > DBMS_LOB.CREATETEMPORARY(:clob, TRUE); > END; > EOQ > > > $create_sth->bind_param_inout( ':clob', \$clob, 64, { ora_type => > ORA_CLOB } ); > $create_sth->execute; > > > my $str = 'x' x 500_000; > substr( $str, 400_000, 10 ) = '0123456789'; > $dbh->ora_lob_write( $clob, 1, $str ); > > > my $substr_sth = $dbh->prepare( <<EOQ, { ora_auto_lob => 0 } ); > BEGIN > :out := DBMS_LOB.SUBSTR( :clob, :len, :pos ); > END; > EOQ > > > my $ret; > $substr_sth->bind_param_inout( ':out', \$ret, 1000 ); > $substr_sth->bind_param( ':clob', $clob, { ora_type => ORA_CLOB } ); > $substr_sth->bind_param( ':pos', 400_000 + 1 ); > $substr_sth->bind_param( ':len', 10 ); > > > $substr_sth->execute; > > > print "RET = '$ret'\n"; > > > my $free_sth = $dbh->prepare( <<EOQ, { ora_auto_lob => 0 } ); > BEGIN > DBMS_LOB.FREETEMPORARY(:clob); > END; > EOQ > > > $free_sth->bind_param( ':clob', $clob, { ora_type => ORA_CLOB } ); > $free_sth->execute; > > > > ** ** ** PRIVILEGED AND CONFIDENTIAL ** ** ** > This email transmission contains privileged and confidential information > intended only for the use of the individual or entity named above. Any > unauthorized review, use, disclosure or distribution is prohibited and may be > a violation of law. If you are not the intended recipient or a person > responsible for delivering this message to an intended recipient, please > delete the email and immediately notify the sender via the email return > address or mailto:[EMAIL PROTECTED] Thank you. > > - end - >
