RE: Accessing Remote LOBs in Oracle

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:imharisa@xxxxxxxxxx]
Sent: Wednesday, March 05, 2008 3:06 PM
To: dbi-users@xxxxxxxx
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:esimon@xxxxxxxxxxxxxx]
Sent: Wednesday, March 05, 2008 1:13 PM
To: dbi-users@xxxxxxxx
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 foo@db1;

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

.

Relevant Pages

  • RE: Accessing Remote LOBs in Oracle
    ... Accessing Remote LOBs in Oracle ... Does not look too complicated but I am not sure which OCI client you have to use. ... in db2 and have a stored procedure update a mirrored table in db1? ...
    (perl.dbi.users)
  • RE: Accessing Remote LOBs in Oracle
    ... I think that would make the database link transparent to you. ... Accessing Remote LOBs in Oracle ... CREATE TABLE foo ( ...
    (perl.dbi.users)
  • Re: Error in calling stored procedure via DB link
    ... "Paul Clement" wrote: ... Below is an Oracle KB article that documents the issue. ... Calling Stored Procedure over Dblink Using Oracle OleDb Provider Fails ... > symptom: Using command type adCmdStoredProc ...
    (microsoft.public.vb.database.ado)
  • Re: Performance problems with StoredProcedure in Web application
    ... you could probably just ignore my comments about stored procedure performance as compared to textual queries because they ... As for your question about how to avoid using a cursor (as I suggested was possible in Oracle) read the following paragraph for more ... The .NET Framework Data Provider for Oracle does not support batched SQL statements. ... CURSOR output parameters to fill a DataSet, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Passing Back Table Information from Oracle .NET Stored Procedures
    ... stored procedure, not in the typical PL/SQL sense. ... I don't care if it's MS SQL in-line code or Oracle in-line code, MS SQL Server, Oracle Proc, or this so called .NET Stored Procedure using a Select statement, they are all returning a RESULT SET whether it be one row or multiple rows. ... I used SqlContext above because I don't want to bother registering at the Oracle site to download their Oracle Database Extensions for .NET in order to get at the presumably extant OracleContext class. ...
    (microsoft.public.dotnet.languages.csharp)