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?
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.
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?