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 "[EMAIL PROTECTED] 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 "[EMAIL PROTECTED] 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 [EMAIL PROTECTED]
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 [EMAIL PROTECTED]
ESIMON> CREATE SYNONYM test_remote_lob FOR [EMAIL PROTECTED];
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 [EMAIL PROTECTED], 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 [EMAIL
PROTECTED]
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:[EMAIL PROTECTED]
Sent: Thursday, March 06, 2008 12:17 PM
To: Eric Simon
Cc: 'John Scoles'; dbi-users@perl.org
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
