Select a CLOB field from a remote table

Posted on 2003-02-19
Medium Priority
Last Modified: 2008-01-09
How do I use dbms_lob to select a field from a remote table using sqlplus?
Question by:acoble
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

GunShy earned 400 total points
ID: 7983272
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.

Expert Comment

by:Tom Knowlton
ID: 9432409
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question