Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

Select a CLOB field from a remote table

How do I use dbms_lob to select a field from a remote table using sqlplus?
1 Solution
Below is the restrictions placed on accessing a LOB through a remote database.  In short, you can't.
If you read below it will give what you can and can't do.

I've been forced to select information from the remote database directly, spool it into a text file and then use sql ldr to load the files into my database.

I know the format below isn't great if you want to provide your email address I can send it to you in a better format, or you can also reference the following:

Oracle8i Application Developer's Guide - Large Objects (LOBs)
Chapter 9 of 10

The use of LOBs are subject to some restrictions:
Distributed LOBs are not supported. Specifically, this means that the user cannot use a remote locator in the SELECT and WHERE clauses. This includes using DBMS_LOB package functions. In addition, references to objects in remote tables with or without LOB attributes are not allowed.

Invalid operations. For example, the following operations are invalid:
SELECT lobcol from table1@remote_site;
INSERT INTO lobtable select type1.lobattr from table1@remote_site;
SELECT dbms_lob.getlength(lobcol) from table1@remote_site;

Valid operations. Valid operations on LOB columns in remote tables include:
CREATE TABLE t as select * from table1@remote_site;
INSERT INTO t select * from table1@remote_site;
UPDATE t set lobcol = (select lobcol from table1@remote_site);
INSERT INTO table1@remote...
UPDATE table1@remote...
DELETE table1@remote...
Table type and clauses not supporting LOBs
LOBs are not supported in the following table types and clauses:
Clustered tables and thus LOBs cannot be a cluster key.
GROUP BY, ORDER BY, SELECT DISTINCT, aggregates and JOINS. However, UNION ALL is allowed on tables with LOBs. UNION, MINUS, and SELECT DISTINCT are allowed on LOB attributes if the object type has a MAP or ORDER function.
Index organized tables. LOBs however, are supported in non-partitioned index organized tables.
NCLOBs are not allowed as attributes in object types when you create tables, but NCLOB parameters are allowed in methods. NCLOBs store fixed-width data.
ANALYZE and ESTIMATE. LOBS are not supported in the ANALYZE... COMPUTE or ESTIMATE STATISTICS statements.
Trigger Body. You can use the LOB column or LOB attribute in a trigger body subject to the following conditions. In general, the :new and :old LOB values bound in the trigger are read-only which means that you cannot write to the LOB. More specifically:
In before row and after row triggers -
you can read the :old value of a LOB in both the triggers.
you can read the :new value of the LOB only in an after-row trigger.
In INSTEAD OF triggers on views, you can read both the :new and :old values.
You cannot specify the LOB column in an OF clause (Note that a BFILE can be modified without updating the underlying tables on which it is based).
If you use OCI functions or DBMS_LOB routines to update LOB values or LOB attributes on object columns, the functions or routines will not fire the triggers defined on the tables containing the columns or attributes.
Client-side PL/SQL procedures. These may not call DBMS_LOB package routines. However, you can use server-side PL/SQL procedures or anonymous blocks in Pro*C/C++ to call DBMS_LOB package routines.
Read-Only Support for External LOBs (BFILEs). Oracle8i supports read-only operations on external LOBs. If you need to update or write to external LOBs, you have to develop client side applications suited to your needs
CACHE / NOCACHE / CACHE READS. CACHE READS LOBs are supported in this release. If you use CACHE READS LOBs and then downgrade to 8.0 or 8.1.5, your CACHE READS LOBs generates a warning and becomes CACHE LOGGING LOBs. You can explicitly alter the LOBs' storage characteristics later if you do not want your LOBs to be CACHE LOGGING.
Tom KnowltonWeb developerCommented:
No comment has been added lately (218 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to GunShy http:#7983272

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --


EE Cleanup Volunteer

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now