I'm barely familiar with stored procedures. How would you leverage a stored procedure here? Would it be to create an actual table in db2 and have a stored procedure update a mirrored table in db1?
Also, I'm paranoid about the stored procedure introducing more overhead, as I am moving LOB data frequently in and out of these tables. -----Original Message----- From: Ian Harisay [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2008 3:06 PM To: dbi-users@perl.org Subject: RE: Accessing Remote LOBs in Oracle Have you tried handling this thru a stored procedure? I think that would make the database link transparent to you. -----Original Message----- From: Eric Simon [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2008 1:13 PM To: dbi-users@perl.org Subject: Accessing Remote LOBs in Oracle Hi, Has anyone else needed to write into LOB columns on remote Oracle databases through a database link (created by the Oracle statement 'CREATE PUBLIC DATABASE LINK ...'). For example, we have this: In Instance 1: (db1) ==================== CREATE TABLE foo ( a NUMBER(10), b CLOB ); In Instance 2: (db2) ==================== CREATE SYNONYM foo FOR [EMAIL PROTECTED]; Our Perl script creates a database handle connected to db2 and tries to insert values into foo (which, because it is a synonym will insert the values into the foo table on db1), but fails with the following error: Fatal: DBD::Oracle::st execute failed: ORA-22992: cannot use LOB locators selected from remote tables (DBD ERROR: error possibly near <*> indicator at char 36 in 'INSERT INTO foo (a,b) VALUES (:p1,:<*>p2)') [for Statement "INSERT INTO foo (a,b) VALUES (:p1,:p2)" with ParamValues: :p1='1',:p2=undef] As of Oracle 10gR2, Oracle claims to provide the facility to access remote LOBs through OCI (and they provide a code example): http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_data_interface.htm#CACIFCJF Is there anyone capable of incorporating this new facility into DBD::Oracle? I'm new to this package, but I could try if I was pointed in the right direction. Thanks!! -- Eric Simon
