RE: Accessing Remote LOBs in Oracle

No problem. I hope this is thorough (and yet not too thorough...)

============================
== Setting Up the Problem ==
============================

First I create a tablespace and user in the remote instance
-----------------------------------------------------------
sqlplus "sys@xxxxxxxxxxxxxxxxxxxxx as sysdba"

SYS> CREATE TABLESPACE esimon DATAFILE SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 3G SEGMENT SPACE MANAGEMENT AUTO;

SYS> CREATE USER esimon IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE esimon TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON esimon;

SYS> GRANT CREATE SESSION, CREATE TABLE TO esimon;

SYS> quit


Second, I create a tablespace and user in the local instance
------------------------------------------------------------
sqlplus "sys@xxxxxxxxxxxxxxxxxxxx as sysdba"

SYS> CREATE TABLESPACE esimon DATAFILE SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 3G SEGMENT SPACE MANAGEMENT AUTO;

SYS> CREATE USER esimon IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE esimon TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON esimon;

SYS> GRANT CREATE SESSION, CREATE TABLE, CREATE SYNONYM TO esimon;

SYS> CREATE PUBLIC DATABASE LINK remote USING 'remote.theiqgroup.com';

SYS> quit


Third, I build a table in the remote instance
---------------------------------------------
sqlplus esimon@xxxxxxxxxxxxxxxxxxxxx

ESIMON> CREATE TABLE test_remote_lob (id NUMBER(20),text CLOB);

ESIMON> quit


Fourth, I build a synonym to that remote table in the local instance
--------------------------------------------------------------------
sqlplus esimon@xxxxxxxxxxxxxxxxxxxx

ESIMON> CREATE SYNONYM test_remote_lob FOR esimon.test_remote_lob@remote;

ESIMON> INSERT INTO test_remote_lob (id,text) VALUES (3,'foo');

1 row created.

ESIMON> SELECT * FROM test_remote_lob;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables



no rows selected

ESIMON> quit

[NOTE: when I run the above SELECT statement from esimon@remote, I get the data we would expect:]

ID TEXT
-- ----
3 foo


=======================================================
== Using DBI to Access the Table Through the Synonym ==
=======================================================
Here's my Perl script. $db is a database handle that is connected to esimon@xxxxxxxxxxxxxxxxxxxxx

use DBD::Oracle qw(:ora_types);

# This works perfectly, just like our INSERT statement from SQLPLUS above worked.
my $st = $db->prepare('INSERT INTO test_remote_lob (id,text) VALUES (?,?)');
$st->bind_param(1,'42');
$st->bind_param(2,'foo');
$st->execute;


# This crashes with the 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 51 in
# 'INSERT INTO test_remote_lob (id,text) VALUES (:p1,:<*>p2)') [for Statement
# "INSERT INTO test_remote_lob (id,text) VALUES (?,?)" with ParamValues: :p1='42', :p2='foo']
my $st = $db->prepare('INSERT INTO test_remote_lob (id,text) VALUES (?,?)');
$st->bind_param(1,'42');
$st->bind_param(2,'foo',{ora_type => ORA_CLOB,ora_field => 'text'});
$st->execute;


# This crashes with the similar error:
# Fatal: DBD::Oracle::db selectrow_array failed: ORA-22992: cannot use LOB locators selected
# from remote tables (DBD ERROR: OCIStmtFetch) [for Statement "SELECT id, text FROM test_remote_lob"]
$st = $r->db->selectrow_array('SELECT id, text FROM test_remote_lob');
while (my($id,$text) = @{$st->fetch || []}) {
print $id . ' - ' . $text . "\n";
}



--
Eric Simon


-----Original Message-----
From: John Scoles [mailto:scoles@xxxxxxxxxxx]
Sent: Thursday, March 06, 2008 12:17 PM
To: Eric Simon
Cc: 'John Scoles'; dbi-users@xxxxxxxx
Subject: Re: Accessing Remote LOBs in Oracle

What I am going to need is some detailed code examples of what you want
to do.

SQL, DATA perl examples etc and of course the SQL to generate the tables
fields etc.


If you are using the 10.2.0 client that should be the same version as in
you link and should be able to do it. There are some limits of course
as outlined in the doc.

The only problem I see is that I only have a 10ex oracle db to play with
so I might not be able to do a link between two databases using link (at
least not easily)

cheers

.