Hai.
Can we use DBLink to access/edit, I mean to SELECT or INSERT a BLOB table in the remote database?
We are using Oracle9i. If not, then what is the workarund for this problem?
regards,
Dileep.
:confused:
Printable View
Hai.
Can we use DBLink to access/edit, I mean to SELECT or INSERT a BLOB table in the remote database?
We are using Oracle9i. If not, then what is the workarund for this problem?
regards,
Dileep.
:confused:
No, you can not use BLOBs over database links.
Workarounds? Perhaps you could make use of snapshots (LOBs can be replicated in snapshots).
INSERT & SELECT of LOBS are no problem via dblink.
Hai.
I'm getting confused here. One says SELECT/INSERT is not possible using DBLinks and other says its possible.
Please let me know which is right?
regards,
Dileep Tallam.
Test it,and tell us.
In 9i? I don't think so. Would you care to demonstrate how would you select LOB columns *directly* from the remote table over database link on 9i?Quote:
Originally Posted by ebrian
In 10gRel2 you can access remote LOBs, but only through OCI, JDBC, and PL/SQL interfaces, not directly with SQL.
Hai everybody.
I have tried by creating dblinks on two databases pointing to eachother. I created one table each on each database with same names test1. Please see the following:
SQL>create user test1 identified by test1;
SQL>grant connect, resource to test1;
SQL>alter user test1 default tablespace users;
SQL>conn test1/test1
SQL>create sequence photonum;
SQL>create table photoalbm (photoid number(5), photo blob);
SQL>create or replace trigger photoid
before insert on photoalbm
for each row
begin
select photonum.nextval into :new.photoid from dual;
end;
/
SQL>create view v_photoalbm
as select photoid, dbms_lob.getlength(photo) photo from photoalbm;
SQL>create database link testlk
connect to test1 identified by test1
using 'INTDAG';
SQL> select photoid, dbms_lob.getlength(photo)
from photoalbm@testlk;
select photoid, dbms_lob.getlength(photo)
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
Now, I tried using the view instead of table directly.
SQL> insert into photoalbm
select * from v_photoalbm@testlk;
2 select * from v_photoalbm@testlk
*
ERROR at line 2:
ORA-00942: table or view does not exist
ORA-02063: preceding line from TESTLK
Again I tried using the table name:
SQL> insert into photoalbm
select * from photoalbm@testlk;
0 rows created.
Nothing works. I created a view and tried to access the view instead of table according to one of the Asktom post.....
Please let me know........
regards,
Dileep Tallam.
Hai Jurij Modic,
Also, You said that there is no way in Oracle9i to SELECT/INSERT BLOBS in remote database using DBLink. Its possible in Oracle10g but through OCI, JDBC and PL/SQL and not using SQL statements. Could you let let me know more about this.
Ofcourse some one said to use Snapshots, but again they are readonly. I mean we can only SELECT from them and no INSERT/UPDATE is possible if we use SNAPSHOTS. Am I right?
regards,
Dileep Tallam.
Its possible over dblink.
For this you need the Transparent Gateway connection which almost supports all the functionality over db's using dblink. To install this needs separate Oracle license.
The other way is Generic connectivity which has limitations, still you can do most with an ODBC drivers installed which has capability to support multiple active ODBC cursors.
On most Oracle server you have the generic connectivity. Check your db what way it connects to remote db. Main restrictions of using Generic connectivity is "BLOB/CLOB data cannot be read through passthrough queries", "cannot involve in 2 phase commit". For further ref: Metalink
I have a remote database with blob column tables and they populate the local db through triggers, procedures, and ofcourse dblink. I have a licensed TG connection.
Hope this helps the DBA community.
Hai Basan.
I dont have much information about Transparent Gateways. I know that they are used to connect to other database from Oracle transperantly or so. Is it possible to use Transparent Gateway to connect to Oracle DB from another Oracle DB? I have never seen even while installing the Oracle S/W this option. I only see Transparent Gateways for DB2, MS-SQL Server etc.
Please let me know more about this.
Also, How to check whether my database is using Generic Connectivity or not?
Also, you said that you are able to access LOBS using PL/SQL, TRIGGERS etc using DBLinks. Are you able to use direct SQL also to SELECT data from remote Database using DBLink?
regards,
Dileep Tallam.
The problem comes into play when you try to select the actual LOB locator remotely, however straight INSERTs aren't a problem.
However, if I do a COMMIT and then go to the remote database and do the same query, I get the following:Code:SQL> desc test_clob1
Name Null? Type
----------------------------------------- -------- -----------------------
A1 NUMBER
A2 CLOB
SQL> desc test_clob2@remotedb
Name Null? Type
----------------------------------------- -------- -----------------------
A1 NUMBER
A2 CLOB
SQL> declare
2 big_text varchar2(32767) := rpad('#',32767,'#');
3 begin
4 insert into test_clob1 values (1, big_text );
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select a1, dbms_lob.getlength(a2) from test_clob1;
A1 DBMS_LOB.GETLENGTH(A2)
---------- ----------------------
1 32767
SQL> select count(*) from test_clob2@remotedb;
COUNT(*)
----------
0
SQL> insert into test_clob2@remotedb
2 select * from test_clob1;
1 row created.
SQL> select a1, dbms_lob.getlength(a2) from test_clob2@remotedb;
select a1, dbms_lob.getlength(a2) from test_clob2@remotedb
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
SQL> commit;
Commit complete.
Code:SQL> select a1, dbms_lob.getlength(a2) from test_clob2;
A1 DBMS_LOB.GETLENGTH(A2)
---------- ----------------------
1 32767
You are right.Quote:
Originally Posted by tvrdileep
You can see HS folder under Oracle_home and in tnsnames file service name for tg4msql (for ms sqlserver) listed with HS = ok.Quote:
Originally Posted by tvrdileep
No, only through procedures and triggers.Quote:
Originally Posted by tvrdileep
Hai Everybody.
I came to know from above discussions that we can only INSERT into LOB Column of remote database table using DBLink. SELECT is not possible using DBLink.
I would like to what is the workaround for this? I want to do both SELECT and INSERT into remote database using DBLink. I mean if its possible by using JAVA/PL SQL, can anyone tell me what is the procedure?
regards,
Dileep Tallam.
You can access this two procedures even from PL/SQL by publishing itCode:import java.sql.*;
import java.io.*;
import oracle.sql.*;
public class ....
{
public static Blob getBlob
{
Blob srcbloc;
DriverManager.......;
Connection conn_src = ...........
Statement stmt = ...
ResultSet rs_src = stmt.executeQuery("
Select pic from tab_pics where id = 1");
srcblob = ((OracleResultSet)rs_src).getBLOB(1);
return srcBlob;
}
public static void loadBlob throws exception
{
DriverManager.......;
Connection conn_dest = ...........;
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement("insert into tab_pic values(1, ?)");
stmt.setBlob(1, getBlob);
ops.execute();
conn_dest.commit();
}
}
Hai Tabreaz.
Thanks for your info. One of our development guy said that he could neither SELECT/INSERT data from/into LOB column of a remote database table using DBLink. Could you tell me if its possible by using DBLink?
Try it out using PL/SQL or Java Stored procedure, no resources for testing such.
If you face any problem, then someone for sure help you in specific area.
Hai.
I have read that if one uses JDBC OCI Drivers then we can use java.sql and java streams for SELECT/INSERT of LOB data and if one uses JDBC Thin Drivers then we have to use PL/SQL and DBMS_LOB Package. I would like to if I'm correct?
regards,
Dileep Tallam.
Dont know what you waiting for, just test it once. All the alternatives.
All the best