Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Reji.

Asked: September 05, 2000 - 12:08 pm UTC

Last updated: November 20, 2017 - 4:15 pm UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Tom:
I have two dbs running 8.1.6. I created a DB link from one
DB to other and when I tried to do a select on a table
where I have CLOB data and it's giving me error. How do I

remotely copy the lob data from one db to the other?

-Thx
-Reji


and Tom said...

You cannot select them but you can

insert .. select ...

them.

tkyte@TKYTE816> create database link ORA816.US.ORACLE.COM
2 connect to scott identified by tiger
3 using 'aria-dev.us.oracle.com';

Database link created.

tkyte@TKYTE816> select * from dual@ORA816.US.ORACLE.COM;

D
-
X


tkyte@TKYTE816> create table t ( z clob );
Table created.


tkyte@TKYTE816> insert into t select * from
t@ora816.us.oracle.com;
1 row created.

tkyte@TKYTE816> select * from t;

Z
--------------------------------------------------------------------------------
********************************************************************************....... (chopped)
********************************************************************************

Rating

  (41 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good input

Vishnu, January 24, 2002 - 10:07 am UTC

Hi tom ,
Is it possible to create a snap shot to refresh the BLOB table data(FROM A REMOTE TABLE ) in regular intervels or the sheduled DBMS_JOB .Which is a good solutioin..
thanks .
vis

Tom Kyte
January 25, 2002 - 8:17 am UTC

Yes, a snapshot would work well in this configuration.

Snap shot giving an error....on remote blob fields

vishnu, January 25, 2002 - 9:14 am UTC


Hi Tom
Thanks for the help..
I have a table by name 'MDOC' like below in database DB1.

RM_ID NUMBER
MANUFACTURER VARCHAR2(20)
CH_NAME VARCHAR2(20)
B_BLOB BLOB
B_MIME VARCHAR2(20)

IN the above B_BLOB field stores the PDF files ..and b_mime stores the mime info.

We are in the process of developing an application based on DB2(same blobtable,which needs to be refreshed every day). I Have cretaed
a DBLINK 'LINK1' for connecting to DB1.

Now i have created a snap shot bpsi_colreg.view_blob_test as
select * from DB1.MDOC@LINK1.. is giving me error.
pl help



Tom Kyte
January 25, 2002 - 1:50 pm UTC

and the error would be???? (my crystal ball is in the shop this week)

Sorry For not writing the error

vishnu, January 25, 2002 - 2:41 pm UTC

Hi Tom ,
I am sorry for not writing the error
ORA-12014: table 'DOC' does not contain a primary key constraint

Thanks
vis

Tom Kyte
January 25, 2002 - 4:24 pm UTC

umm, does your table in fact have a primary key? it's required...

Database Links and LOBs", version 8.1.6

Su-mei Y. Chang, January 25, 2002 - 5:02 pm UTC

Tom, this is wonderful. Clear, easy and simple. Appreciate
very much.



inserting an xmltype column through Database link

Venkat, August 04, 2004 - 6:35 pm UTC

Hi Tom,
I am trying to insert xmltype column to a remote table . when i try to insert , its giving me "ORA-02069: global_names parameter must be set to TRUE for this operation", But i am able to select the xmltype column from the remote table. Is there any work around available.

For example.
In the remote server i am having a table T(xmlcolumn xmltype).

i am trying to insert into table t as follows

INSERT INTO t@dlink(xmlcolumn) VALUES(xmltype('<rowset><row>23</row></rowset>'));

its giving the above error, where as i am able to do the below..

DECLARE
z xmltype;
BEGIN
SELECT B INTO Z FROM t@dlink A ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;

Am i missing out something???


Tom Kyte
August 05, 2004 - 12:27 pm UTC

[tkyte@localhost tkyte]$ oerr ora 2069
02069, 00000, "global_names parameter must be set to TRUE for this operation"
// *Cause: A remote mapping of the statement is required but cannot be achieved
// because global_names should be set to TRUE for it to be achieved
// *Action: Issue alter session set global_names = true if possible




A reader, August 20, 2004 - 10:59 am UTC

Hi Tom,
I am trying to select XMLTYPE cloumn over the database link  and it is giving me problem...

SQL> desc t1@CRMDEV.WORLD;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 A                                                  VARCHAR2(1000)
 B                                                  SBLINT.XMLTYPE
 C                                                  DATE




  1*  select a,c from t1@CRMDEV.WORLD
SQL> /

A                                                  C
-------------------------------------------------- ---------
SIEBEL_INBOUND_QUEUE                               16-AUG-04

SQL> select a,b from t1@CRMDEV.WORLD;
ERROR:
ORA-00942: table or view does not exist



SQL> 


Is this bug? ... We are using 

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Thanks 

Tom Kyte
August 21, 2004 - 10:40 am UTC

why does it say "sblint.xmltype"?

DB_links

A reader, November 20, 2004 - 9:25 am UTC

Hello Tom,

DDL for table:
CREATE TABLE TT1
(
  MONTHS_CLOSED  NUMBER,
  DATE_CLOSED    DATE
)
/

data:
insert into tt1 values(110, to_date('18-jan-1993', 'dd-mon-yyyy'));

SQL> show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE
SQL> select * from basua.tt1@prod1;

MONTHS_CLOSED DATE_CLOSED
------------- ---------------
          110 18-JAN-93

SQL> SELECT * FROM basua.tt1@prod1
  2  WHERE UPPER(date_closed) <> LOWER(date_closed)
  3  /

MONTHS_CLOSED DATE_CLOSED
------------- ---------------
          110 18-JAN-93


SQL> delete FROM basua.tt1@prod1
  2   WHERE UPPER(date_closed) <> LOWER(date_closed)
  3  /

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> SELECT * FROM basua.tt1@prod1
  2  WHERE isnumber(date_closed) =0
  3  /

MONTHS_CLOSED DATE_CLOSED
------------- ---------------
          110 18-JAN-93

SQL> delete from basua.tt1@prod1
  2  WHERE isnumber(date_closed) =0
  3  /
WHERE isnumber(date_closed) =0
      *
ERROR at line 2:
ORA-02069: global_names parameter must be set to TRUE for this operation

the above function exist on the database where I am running the query from.

When I am selecting data over the db_link it works fine, however, I am getting the above error while deleting a record. Could you help how to resolve please? 

Tom Kyte
November 20, 2004 - 9:44 am UTC

the select can be performed locally.

the delete cannot -- it must be performed remotely.

[tkyte@localhost tkyte]$ oerr ora 2069
02069, 00000, "global_names parameter must be set to TRUE for this operation"
// *Cause: A remote mapping of the statement is required but cannot be achieved
// because global_names should be set to TRUE for it to be achieved

// *Action: Issue alter session set global_names = true if possible

in a distributed environment, global_names should really be true, that would be the path to "solving" this.

DB_links

A reader, November 20, 2004 - 10:06 am UTC

Thanks Tom,

I tried the following. But it didn't work. Could you please point out what I require to do.


re-created the database links. 
CREATE DATABASE LINK prod1.world
CONNECT TO basua
IDENTIFIED BY basua123
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST =hostname 
)(PORT = 1521)))(CONNECT_DATA =(SID = prod1)))'
/


SQL> alter session set global_names = true
  2  /

Session altered.

SQL>  delete from basua.tt1@prod1.world
  2  where isnumber(date_closed) =0
  3  /
 delete from basua.tt1@prod1.world
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
ORA-02063: preceding line from prod1.WORLD

ORA-02019 connection description for remote database not found

Cause: An attempt was made to connect or log in to a remote database using a connection description that could not be found.

Action: Specify an existing database link. Query the data dictionary to see all existing database links. See your operating system-specific Net8 documentation for valid connection descriptors.

 

Tom Kyte
November 20, 2004 - 10:14 am UTC

<quote>
Query the data dictionary to see all
existing database links.
</quote>

show us that the link actually exists after you get the error.

Db_links

A reader, November 20, 2004 - 11:37 am UTC

Thanks very much Tom,

select substr(OWNER,1,8), substr(DB_LINK,1,10), substr(username, 1, 10), substr(host,1,200), created
from dba_db_links where owner='BASUA'
order by created desc
/


SUBSTR(OWNER,1,8) SUBSTR(DB_LINK,1,10) SUBSTR(USERNAME,1,10)
------------------------ ------------------------------ ------------------------------
SUBSTR(HOST,1,200)
------------------------------------------------------------------------------------------------------------------------------------
CREATED
---------------
BASUA PROD1.WORLD BASUA
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST =gbtsaix1
)(PORT = 1521)))(CONNECT_DATA =(SID = PROD1)))
20-NOV-04


Tom Kyte
November 20, 2004 - 11:44 am UTC

no, full cut and paste.

login
run query
see error
run this query.


verbaitim cut and paste, no editing.

Passing BLOB as parameter over database link

Ken Lee, May 03, 2007 - 11:31 am UTC

Tom,

I am not real clear on the locater part of LOBs and why they seems to cause problems.

On a remote database, I have a function that accepts a BLOB as a parameter. In the local database, I want to pass the BLOB as a parameter to the remote database across a database link.

Something like

declare
 l_blob BLOB;
 l_result NUMBER;
begin
  select blob_field into l_blob from some_table;
  l_result := schema.function@dblink(l_blob);
  dbms_output.put_line('Result is ' || to_char(l_result));
end;
/


This doesn't work for me.
1. Is it possible to pass BLOB as parameter accross database link?

2. Thinking that this has to do with lob locators, what is the reason why?

3. What are possible work arounds?
Tom Kyte
May 03, 2007 - 10:43 pm UTC

1) nope
2) because lob locators are not supported over dblinks
3) use a global temporary table

insert into gtt@remote_site select blob from local_table where .....;
call remote procedure and have it process that blob.

varrays or user defined types as columns and dblinks

XML-Newbie, May 04, 2007 - 9:13 am UTC

Hi Tom,
We have issues with getting the table data from source to target (both are DB - 10.2.0.2) over a DBLink specifically for tables which have object types, varrays etc.
we get
"ORA-22804: remote operations not permitted on object tables or
user-defined type columns". As a workaround, on metalink a very simple example has been given.
In our scenario, we have lots of varrays in a single table itself. We kind of thought about using xmltype/clob for getting the table data from source to target and then convert the xmltype column data which has the xml data from the varray column type back into the required object based tables.
We can successfully get the table from source by applying sys_xmlgen() to create the xml and store in another table and bring it to target.
On target , since this table has lots of varrays inside, the extract() ( haven't tried the xquery methods yet) method to do the whole xml parsing/extraction looks complicated and there will definitely performance issues as well.
So, is there a way to convert an xml that is conferred to an object type column in table back into the database table directly? Looked at the xml schema generation etc.. Looked at structured xml storage of xmltype..Couldn't figure out/understand an easier way. Please answer.


Tom Kyte
May 04, 2007 - 1:15 pm UTC

can unnesting be used instead - to make them rows and columns again?

creating view on remote table with BLOB cols

A reader, September 18, 2007 - 3:46 pm UTC

Tom,
Can we create a view that goes over a dblink and fetches a BLOB column from a different database?
If not, whats the work aorund? (I need a viw because I want to pass one of the conditions dynamically and not get a huge set of data from source)

thanks a lot!
Tom Kyte
September 19, 2007 - 8:40 am UTC

the view would return a lob locator.

this lob locator (a pointer) would need to be dereferenced by the client, the client would send the lob locator back to the wrong database (the local one they are connected to) - that local database would not be able to process the lob locator.


Forget about the view for a minute, it is a red herring (a view is just stored sql here).

You are referencing a remote LOB, there are various restrictions on this - that vary by version (of which you do not specify...)

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_working.htm#i1006278

that is for 10gr2 - you could

insert into T select ...... <your query>;

(T is a global temporary table perhaps, or a real table - depending on your needs) to copy the blob over to the local table - and then open a cursor as select * from T - if you modified the blog, you would have to use an update with a select to move it back)

db links, lobs, functions?

Curtis, December 23, 2007 - 5:05 am UTC

Tom,

I have been using the insert .... select clob from ... syntax to copy LOBs to a second database with an after insert/update trigger. I am trying to identify differences between LOBs with dbms_crypto.hash() due to limited bandwidth between databases. Is there a way other than creating a view on the remote database to call a function on a lob?

CLOB Problem in INSERTING !!!!!!!Plz Help

INDRANIL DAS, August 17, 2009 - 2:17 am UTC

Hi Tom,
Good Morning,
We are facing below issues for CLOB , we thought that 'select is not possible ...but insert is possible....' 
---------------
Could you please suugest any alternatives, as we need to transfer some of the BLOB/CLOB
data by selecting the coulmns from couple of tables by joining them from one database to another
database ?

Thanks in advance for your help.


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show user
USER is "SYS"
SQL> select name from v$database;

NAME
---------
LIVE10G


SQL> Create database link ngp10
  2   connect to system
  3   identified by passwd
  4   using '(DESCRIPTION =
  5       (FAILOVER=on)
  6       (LOAD_BALANCE=off)
  7       (ADDRESS_LIST=
  8          (ADDRESS=(PROTOCOL=IPC)(KEY=NGP10P2))
  9          (ADDRESS=(PROTOCOL=TCP)(HOST=ngpp-physn4.apmoller.net)(PORT=1527))
 10          (ADDRESS=(PROTOCOL=IPC)(KEY=NGP10P1))(ADDRESS=(PROTOCOL=TCP)(HOST=ngpp-physn3.apmoller.
net)(PORT=1527))
 11       )
 12       (CONNECT_DATA=(SERVICE_NAME=ngp10p.apmoller.net))
 13     )';

Database link created.

SQL> select * from dual@ngp10;

D
-
X

SQL> show user
USER is "SYS"
SQL> select name from v$database;

NAME
---------
LIVE10G

SQL> select so_numbers from edoc2.pouch_release@ngp10 where so_numbers is not null;
select so_numbers from edoc2.pouch_release@ngp10 where so_numbers is not null
                                                       *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables


SQL> create table t ( z clob );

Table created.

SQL> insert into t select so_numbers from edoc2.pouch_release@ngp10 where so_numbers is not null and
     rownum<3;
insert into t select so_numbers from edoc2.pouch_release@ngp10 where so_numbers is not null and     
                                                                    *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
Regards
INDRANIL DAS.
Oracle DBA,IBM

Tom Kyte
August 24, 2009 - 8:45 am UTC

the where clause processing is happening locally - that is the cause here, if you create a view to make the where clause have to be processed on the remote site, it'll work.


On Tom's suggestion about "Insert in to a clob field using dblink"

Prem, May 05, 2010 - 4:58 am UTC

Hi tom,

Your answer to the first question in this thread helped me a lot.
Your answers to the questions are very simple and short...Thanks a lot !

So as you said
"insert into t select * from t@ora816.us.oracle.com; "

Is it doing a Bulk insert ?

Tom Kyte
May 06, 2010 - 1:54 pm UTC

it is doing it in bulk - in a single sql statement - yes.

if you

fetched a row
inserted a row
fetched a row
inserted a row
.....


it would be "non bulk"

Thanks Tom

Prem, May 07, 2010 - 5:22 am UTC

Thanks Tom.

I have one basic question,
why i am not allowed to insert in to a CLOB field through DBlink directly as i do for other datatype's?

Is it because of memory reference ?
Tom Kyte
May 07, 2010 - 8:33 am UTC

you are allowed to??

what you cannot do is reference a remote lob locator in a local database. a lob locator is a pointer, and in the local database, it is a meaningless pointer, it only makes sense in the remote database.


VarChar Performance

Luis Oscar Mendoza Guzman, May 07, 2010 - 2:24 pm UTC

Hi tom,

I found on this link http://ss64.com/ora/syntax-datatypes.html a note that say's this "Over time, when varchar2 columns are updated they will sometimes create chained rows - because CHAR columns are fixed width they are not affected by this - so less DBA effort is required to maintain performance" and my question is, this is true? and how i could avoid it?
Tom Kyte
May 08, 2010 - 7:25 am UTC

Ok, let me rebuff with:

Every time, a CHAR consumes maximum space which will always cause a table to be larger than it should be - this leads to larger segments which even a DBA will not be able to fix to perform better"

Now what, I think I win. I can fix a migrated row in a couple of milliseconds - how can you fix a table that is potentially many orders of magnitude larger than it should be.


Never use char, never - that is my "rule". The performance overhead of migrated rows is so blown out of proportion - and even in the rare case where it could possibly become an issue, it is rather easy to 'fix'

..DB link and CLOB

Manoj Kaparwan, January 12, 2011 - 9:41 am UTC

Tom,
Thanks for your time.

Source Database:

CREATE TABLE t1 ( x number, y clob, z varchar2(50))

insert into t1 values ( 1, to_clob('AAAA'), '1111')

insert into t1 values ( 2, to_clob('BBBB'), '0000')


SQL>> select * from t1;

         X Y          Z
---------- ---------- ----------
         1 AAAA       1111
         2 BBBB       2222

SQL>
SQL>
SQL> drop table t2;

Table dropped.

SQL> create table t2 as select * from t1;

Table created. 
SQL> select * from t2;

         X Y          Z
---------- ---------- ----------
         1 AAAA       1111
         2 BBBB       2222

SQL>



Target DB:

create database link source_db connect to a identified by a
using 'source_db_alias'

SQL>  select   a.z  from t1@source_db a inner join t2@source_db b on a.x=b.x;

Z
--------------------------------------------------
1111
2222

SQL> select   decode(a.z,'1111','PASS','?') from t1@source_db a ,  t2@source_db b where  a.x=b.x;

DECO
----
PASS
?

 


BUT




SQL> select   decode(a.z,'1111','PASS','dono') from t1@source_db a inner join t2@source_db b on a.x=b.x;
select   decode(a.z,'1111','PASS','?') from t1@source_db a inner join t2@source_db b on a.x=b.x
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables


Looks any aggregate on remote columns ( non CLOB) with ANSI way is an issue?

regards

..db link and CLOB

Manoj Kaparwan, January 12, 2011 - 9:54 am UTC

Tom
Apologies..
select decode(a.z,'1111','PASS','dono') from t1@source_db a inner join t2@source_db b on
a.x=b.x;

should be
select decode(a.z,'1111','PASS','?') from t1@source_db a inner join t2@source_db b on
a.x=b.x;

result of editing ..
:)

Tom Kyte
January 12, 2011 - 11:04 am UTC

looks like it is, do you have support? can you file a bug?

..db link and CLOB

Manoj Kaparwan, January 12, 2011 - 12:49 pm UTC

Thanks Tom
yes we have support.

will file a bug.

earlier missed to share the version ;)

 SQL> select *from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for HPUX: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

 

..db link and CLOB

Manoj kaparwan, January 13, 2011 - 9:44 pm UTC

Tom,

I have 3-2756048551 raised with Support.

regards

How to insert select xmltype over db link?

Jim, February 25, 2011 - 7:01 pm UTC

Hi, Tom,

How are you?
I have a question regarding how to insert select xmltype from remote site.

desc remote_tbl

Name Null? Type
----------------------------------------- -------- MEMBER_ID NOT NULL NUMBER
XML_CONTENT SYS.XMLTYPE

At local site, if I do
create table local as select * from remote_tbl@my_db_link. it works just fine.

But if I do,
insert into local_tbl select * from remote@my_db_link, it gives me error saying

ERROR at line 1:
ORA-22804: remote operations not permitted on object tables or user-defined type columns


Why is that and how to resolve/work around it?

Thanks,
Tom Kyte
February 28, 2011 - 8:08 am UTC

ops$tkyte%ORA11GR2> create table t ( x xmltype );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( xmltype( '<a>hello world</a>' ) );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t2 as select * from t@ora11gr2@loopback;

Table created.

ops$tkyte%ORA11GR2> insert into t2 select * from t@ora11gr2@loopback;

1 row created.

ops$tkyte%ORA11GR2> select * from t2;

X
-------------------------------------------------------------------------------
<a>hello world</a>
<a>hello world</a>





ops$tkyte%ORA10GR2> create table t ( x xmltype );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( xmltype( '<a>hello world</a>' ) );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2 as select * from t@ora10gr2@loopback;

Table created.

ops$tkyte%ORA10GR2> insert into t2 select * from t@ora10gr2@loopback;

1 row created.




what release?

Regarding xmltype over dblink

Jim, February 28, 2011 - 2:51 pm UTC

Hi, Tom,

Thank you for looking into this.
The DB version is 10.2

One discrepency I found is the following.
At the remote side the type for xml_content is "PUBLIC.XMLTYPE"
and when I issue

create table asdf as select * from remote_table@db_link, the column type of the xmltype becomes "SYS.XMLTYPE"

If I issue the following command.

create table asdf
(
some other columns....
xml_content public.xmltype
)

It gives me error saying invalid datatype.

What is difference between public.xmltype, sys.xmltype and xmltype and why it doesn't compatible with each other?


Any idea?

Thanks,
Tom Kyte
February 28, 2011 - 3:06 pm UTC

ops$tkyte%ORA11GR2> create table t ( x xmltype, y sys.xmltype );

Table created.

ops$tkyte%ORA11GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 PUBLIC.XMLTYPE STORAGE BINAR
                                                   Y
 Y                                                 SYS.XMLTYPE STORAGE BINARY




public would be the public synonym (and is what should typically be used) whereas SYS would be someone being explicit.

ops$tkyte%ORA11GR2> create table tt as select * from t@ora11gr2@loopback;

Table created.

ops$tkyte%ORA11GR2> desc tt;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 SYS.XMLTYPE STORAGE BINARY
 Y                                                 SYS.XMLTYPE STORAGE BINARY



they should be interchangeable - the synonym just points to the type owned by sys.

and as posted above, I'm not having any issues in 10.2.0.4 or 11.2.0.2:

ops$tkyte%ORA11GR2> insert into t values ( '<a>hello</a>', '<b>world</b>' );

1 row created.

ops$tkyte%ORA11GR2> insert into tt select * from t@ora11gr2@loopback;

1 row created.

ops$tkyte%ORA11GR2> select * from t;

X
-------------------------------------------------------------------------------
Y
-------------------------------------------------------------------------------
<a>hello</a>
<b>world</b>


ops$tkyte%ORA11GR2> select * from tt;

X
-------------------------------------------------------------------------------
Y
-------------------------------------------------------------------------------
<a>hello</a>
<b>world</b>




do you have a more specific version?

Jim, February 28, 2011 - 5:48 pm UTC

Hi, Tom,

Remote is 11.2.0.1.0 and local is 10.2.0.4.0.

Thanks,
Jim


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production






SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Tom Kyte
March 01, 2011 - 8:55 am UTC

ops$tkyte%ORA10GR2> create database link ora11gr2
  2  connect to ops$tkyte identified by foobar
  3  using 'ora11gr2';

Database link created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

ops$tkyte%ORA10GR2> select * from v$version@ora11gr2;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> desc t@ora11gr2;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y                                                 PUBLIC.XMLTYPE

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t as select * from t@ora11gr2;

Table created.

ops$tkyte%ORA10GR2> insert into t select x, xmltype( t_remote.y.getclobval() )  from t@ora11gr2 t_remote;

1 row created.

ops$tkyte%ORA10GR2> select * from t;

         X
----------
Y
-------------------------------------------------------------------------------
         1
<a>hello world</a>

         1
<a>hello world</a>



I was able to reproduce - it shouldn't have happened, but the above works around the issue for now.

Now different error

Jim, March 01, 2011 - 1:47 pm UTC

Hi, Tom,

Thanks a lot and your workaround works.
However, I am now getting a different error.

When trying your solution, I got the following error.
xmltype(x.XML_CONTENT.getclobval()),
*
ERROR at line 16:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00004: internal error "MultiChar overflow"
Error at line 11
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1


Both source and target is using UTF8.

Here is source

select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 11.2.0.1.0


And here is target

select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 10.2.0.4.0

Any idea how to resolve this?

Thanks,
Tom Kyte
March 01, 2011 - 2:02 pm UTC

I'll have to refer you to support on this one, but start with:



LPX-00004: "MultiChar overflow" from Loading XML File in Database with UTF8 Character Set (Doc ID 942375.1)

global names

sam, November 21, 2011 - 3:52 pm UTC

Tom:

I am getting strange error when i try to update a local and remote table in a stored procedure. I have a db link setup and it works fine in SQL*PLUS. When I do teh submit over mod_plsql i get this

ORA-02069: global_names parameter must be set to TRUE for this operation
ORA-06512: at "TIPADMIN.QA_PKG", line 2180
ORA-06512: at line 64


The SP is

update T1
set col1 = p_col1,
col2 = p_col2
where orgcode = p_orgcode;

update T1@XX_link
set col1 = p_col1
col2 - p_col2
where orgcode = p_orgocde;

commit;

DO i need to do teh ALTER session in the PL/sql routine or change that in the local or remote database?


Tom Kyte
November 21, 2011 - 6:08 pm UTC

There could be a few things here.

a) it could be that in your sqlplus session - you are using an entirely different tnsnames.ora file to connect to the remote database then the mod_plsql one does. If so, then you are in dangerous territory.

b) it could be that in your sqlplus session, global names is false - whereas in mod_plsql it is true.


You need to figure out which is true first. I would start by showing my global names status in sqlplus and then having a small mod_plsql routine to show me the same in html.


Make sure you are connecting the same in both environments to help rule out (a) above. That is, if you are connecting via a listener in (a) but not (b) - make them the same. And vice versa.

If you connect via 'bequeath' (no sqlnet) you might use one set of tnsnames.ora files

if you connect via a listner, you might use a second set

If you connect to a shared server you might use a third set


Make sure you are connecting "the same"

link

A reader, November 21, 2011 - 11:53 pm UTC

Tom:

The way I udnerstand it, sql*plus for windows and mod_plsql are both *clients* to the database.
so each will have a client tnsnames.ora that uses alias that points to a service name on the server database which has its own tnsnames.ora.

correct?

How do you check global names in both clients.


I am not sure what happened but it seems to have worked after I copied the formatting package to remote database.
It did not read it from the local database.

update T1@DB_LINK
set phone=format_pkg@DB_LINK(p_phone)
where .....
Tom Kyte
November 22, 2011 - 8:09 am UTC

each client can have a tnsnames.ora that can point to the database.

However, when using a database link, the dedicated server or shared server is used to resolve that tnsname - the client tnsnames is NOT used for the database link, the dedicated or shared server's tnsname would be (assuming you are using tnsnames as a name resolution).

And that is where the problem can come in, the dedicated or shared servers environment will be used to find the tnsnames.ora file - and the dedicated or shared server can have a different environment depending on how you access it.

o if you use bequeath, no network, no listener - you will be using dedicated server and that dedicated server will inherit YOUR environment, your oracle_home, your oracle_sid, your TNS_ADMIN if you have it set. So, your TNS_ADMIN might be set to /mp1 (mount point one)

o if you use dedicated server via a listener, the dedicated server will be spawned by the listener. The listener might have a tns_admin set to /mp2. That dedicated server can be using a tnsnames that is different from the previous example.

o if you use shared server, the shared servers environment will come from the environment that was in place when the database instance was started. It could have a tns_admin set to /mp3 - a mount point different from either of the prior two.





To check global names, you can query v$parameter.

ops$tkyte%ORA11GR2> select value from v$parameter where name = 'global_names';

VALUE
-------------------------------------------------------------------------------
FALSE

ops$tkyte%ORA11GR2> alter session set global_names = true;

Session altered.

ops$tkyte%ORA11GR2> select value from v$parameter where name = 'global_names';

VALUE
-------------------------------------------------------------------------------
TRUE




If you do not have access to v$parameter, you can use dbms_utility:

ops$tkyte%ORA11GR2> create or replace
  2  function get_param( p_name in varchar2 )
  3  return varchar2
  4  as
  5      l_param_type  number;
  6      l_intval      binary_integer;
  7      l_strval      varchar2(256);
  8      invalid_parameter exception;
  9      pragma exception_init( invalid_parameter, -20000 );
 10  begin
 11      begin
 12          l_param_type :=
 13          dbms_utility.get_parameter_value
 14          ( parnam => p_name,
 15              intval => l_intval,
 16            strval => l_strval );
 17      exception
 18          when invalid_parameter
 19          then
 20              return '*access denied*';
 21      end;
 22      if ( l_param_type = 0 )
 23      then
 24          l_strval := to_char(l_intval);
 25      end if;
 26      return l_strval;
 27  end get_param;
 28  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set serveroutput on
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> exec dbms_output.put_line( get_param( 'global_names' ) );
0

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> alter session set global_names = true;

Session altered.

ops$tkyte%ORA11GR2> exec dbms_output.put_line( get_param( 'global_names' ) );
1

PL/SQL procedure successfully completed.


link

A reader, November 22, 2011 - 12:00 pm UTC

Tom:

Thanks for the great info.

1) If you have 10 databases/instances on one server/machine, do you normally have 10 tnsnames.ora files or just one file?


2) Is there a way to run the local.format_pkg using this update for remote table or I have to copy it over to remote database too and reference it like this?

update T1@DB_LINK
set phone=format_pkg@DB_LINK(p_phone)
where .....

I know i can create local variables in the LOCAL and then modify the SQL to reference the local variable.


Happy ThanksGiving!
Tom Kyte
November 22, 2011 - 1:23 pm UTC

1) one listener, one set of configuration files for the listener.

As for the tnsnames.ora - it depends. I'd like to have one if at all possible - but if different people own and manage different instances and versions, you'll probably have one per Oracle_home.


2) I'd rather see this get executed at the remote site entirely. It would be rather painful to pull data back locally and then execute a remote procedure call to update the row and send it back.

If you cannot initiate this update from the remote site - consider using dbms_sql@db_link to remotely prepare and execute a plsql block that would just be:

begin
update t1 set phone = format_pkg..... where ....;
end;

with appropriate binds.

LOb handling without Global Temporary Table

A reader, January 16, 2012 - 4:15 pm UTC

Hello Tom,
I m trying to pull LOb type and xml type values from remote tables...i know we cant use LObs over Dblinks..U suggested to create a global temporary table on remote site but the problem here is the otehr team donot want to have any views on thier side, i have to get all the data on to our database...
hows that possible ???
Tom Kyte
January 17, 2012 - 3:19 pm UTC

U suggested

they did? where? I don't see any followups by U? I'm confused??


I suggested to actually create a global temporary table locally, not remotely.

and what do global temporary tables have to do with views anway?

re-read this, you can insert into YOUR_LOCAL_TABLE select * from table@remote_site

with lobs - you want a local table, not remote, to insert into

Opening a Cursor to move CLOB data

Gary Toft, June 05, 2012 - 2:08 pm UTC

Perhaps this is something that only works in 11g, but I found that starting a loop routine using a cursor works perfectly when attempting to insert CLOB data via a database link.
For Example: After creating the "localtable" to match the "remotetable" in the target environment
=========================================================
Declare
v1 integer;

cursor c1 is select tablekey, clobfield from remotetable@dblink;

begin

for rec in c1
loop

insert into localtable (tablekey,clobfield)
values(rec.tablekey, rec.clobfield);

-- optional intermittent commit device below

V1 := (V1+1);

IF V1 = 100 THEN
COMMIT;
V1 := 0;
END IF;
END LOOP;

COMMIT;

END;

/
========================================================
Rapidly moved over 2 million records using this method.
Tom Kyte
June 05, 2012 - 5:27 pm UTC

-- optional intermittent commit device below

V1 := (V1+1);

IF V1 = 100 THEN
COMMIT;
V1 := 0;
END IF;


that is not optional, it is horrible, it must be removed, please do not do that.

Or write the ton of extra code you would need to make your process restartable so that when it DOES FAIL (because you know it will) you can actually restart and FINISH what you started.


and more importantly, no, it doesn't work. but using an insert as select (you know - NO CODE) does - as has been printed above...



ops$tkyte%ORA11GR2> begin
  2          for c in (select * from t1@ora11gr2.localdomain@loopback )
  3          loop
  4                  insert into t2 (x,y) values (c.x, c.y);
  5          end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 2


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t2 (x,y) select x,y from t1@ora11gr2.localdomain@loopback;

2 rows created.

Curious Follow Up Response

Gary Toft, June 06, 2012 - 2:23 pm UTC

I'm really puzzled by the response since there wasn't any indication on why my suggestion would not work or why using a integer variable for intermittent commits during a loop was horrible. I and many others have used this technique for years successfully committing rows instead of eating up the undo tablespace and I have NEVER come across a ora-1555 error. Opening a cursor to select a clob data field does work. As indicated, I moved over 2 million records by that method. Using the insert method you're suggesting results in the error message as shown in your response. I would welcome an explanation as to why the optional commit section is "horrible". DBAsupport.com shows it as a recommended method when moving millions of records.
Tom Kyte
June 06, 2012 - 2:44 pm UTC

there wasn't a suggestion on why it wouldn't work - I demonstrated *it doesn't work*

You wrote to do a cursor for loop, selecting a lob locator out of a remote database and inserting it locally (and using the horrible worst practice of the intermittent commit without any restartable code!)

You cannot do that, I demonstrated that *that will not work*

here it is again:

ops$tkyte%ORA11GR2> begin
  2          for c in (select * from t1@ora11gr2.localdomain@loopback )
  3          loop
  4                  insert into t2 (x,y) values (c.x, c.y);
  5          end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 2


it does not work (that is 11.2.0.3). You cannot use a lob locator selected from a remote table.

The way to do it is as demonstrated, in a single insert as select which *does work* over a database link.


or why using a integer variable for intermittent
commits during a loop was horrible.


I did address that:

Or write the ton of extra code you would need to make your process restartable so that when it DOES FAIL (because you know it will) you can actually restart and FINISH what you started.

and be prepared to deal with

o possible waits for log write as you commit
o increased redo generation
o increased undo generation
o slower overall performance in general because you are doing slow by slow processing (no bulk collecting over a dblink either) instead of set based processing.


I and many others have used this technique
for years successfully committing rows instead of eating up the undo tablespace
and I have NEVER come across a ora-1555 error.


you got lucky. I and many others have actually had code *fail* - ora-1555 is just one of an infinite number of errors.

say the power goes out?
say the instance crashes?
say you run out of space in the tablespace you are inserting into?
say error X in general happens - what the heck happens to your code then?

furthermore, if you were to do an insert /*+ APPEND */ - a direct path load, you would generate approximately 0 bytes of undo against the table you are inserting into.


Opening a cursor to select a
clob data field does work.


prove it, run it against a simple 1 row table for us. I did. It does not work. You must have been doing something *else*. You were not doing the psuedo code you outlined, that does not work, has not worked.

do the work in sqlplus showing all of the tables (describes) and show us the plsql block that makes it work.


Using the insert method you're suggesting results in the error message
as shown in your response.


are we reading the same page???


I demonstrated way back in version 8.1.6 that INSERT AS SELECT works????



DBAsupport.com shows it as a
recommended method when moving millions of records.


did dbasupport also supply the fact that you sort of need a little bit of extra logic to make that really work? or where they just "hoping" that nothing would ever fail???


Thanks for the response

Gary Toft, June 06, 2012 - 3:00 pm UTC

I'm aware of your unquestionable credentials as an Oracle expert. I used TOAD instead of SQL Plus and perhaps that altered my result. But I did indeed use a cursor to move the CLOB data. I setup the cursor in the define section. I guess I've been lucky for almost a decade now since I've been using that intermittent commit statement for nearly that long. Typically, it would be set much, much higher than the 100 records shown in my example. I felt that your first response was caustic and didn't really offer help. I know you don't have time to educate on your website. That's what your books provide. Perhaps suggesting one of your books would have been more helpful. Otherwise, I would suggest changing the website name from AskTom to GetHarrassedbyTom. Perhaps if I were in your position, given the level of frustration inherent with being the expert, I would respond similarly.
Tom Kyte
June 06, 2012 - 3:27 pm UTC

I used TOAD
instead of SQL Plus and perhaps that altered my result.


nope, you cannot reference a remote lob locator, it doesn't work.

I would not mind being proven wrong - but we'll need your entire working example.

I setup the cursor in the define section.

doesn't matter, there is no difference.

I felt that your
first response was caustic and didn't really offer help.


I've been trying to educate about that commit in a loop stuff for about 20 years now.

And it *did offer help* as I demonstrated the speediest way to do this, with the least amount of code, that actually *works*.


I know you don't have
time to educate on your website.


this is where I do most of the education - most of it. the books are derived from this.

Otherwise, I would
suggest changing the website name from AskTom to GetHarrassedbyTom.


you posted something that *does not work*, *will not work* - I demonstrated that, I showed that, and I provided the way to do it - demonstrated it does work, has worked since at least 8.1.6.

All I ask is you post a simple example showing your technique selecting a remote lob locator and successfully inserting it into a local table - using TOAD if you like, toad can be cut and pasted from.


I don't know what could be more clear than this:

ops$tkyte%ORA11GR2> begin
  2          for c in (select * from t1@ora11gr2.localdomain@loopback )
  3          loop
  4                  insert into t2 (x,y) values (c.x, c.y);
  5          end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 2




ops$tkyte%ORA11GR2> !oerr ora 22992
22992, 00000, "cannot use LOB locators selected from remote tables"
// *Cause:  A remote LOB column cannot be referenced.
// *Action:  Remove references to LOBs in remote tables.

ops$tkyte%ORA11GR2> 


followed by:


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t2 (x,y) select x,y from t1@ora11gr2.localdomain@loopback;

2 rows created.



showing it working using insert as select (the right way to do this - you'll see this sort of stuff in my books over and over)...



Pasted Code CLOB moved using cursor or "Just call me Lucky"

Gary Toft, June 06, 2012 - 3:46 pm UTC

DECLARE

V1 INTEGER;

CURSOR C1 IS SELECT LDOWNERTABLE, LDOWNERCOL, LDKEY, LDTEXT FROM MAX41.LONGDESCRIPTION@CNV;

BEGIN
 V1 := 0;

 FOR REC IN C1 
 
     LOOP
     
INSERT INTO LONGDESC (LDOWNERTABLE, LDOWNERCOL, LDKEY, LDTEXT)
VALUES (REC.LDOWNERTABLE,REC.LDOWNERCOL,REC.LDKEY,REC.LDTEXT);

V1:=(V1+1);

IF V1 = 1000 THEN
COMMIT;
V1 := 0;
END IF;
    END LOOP;

COMMIT;

END;

/
-- using TOAD's feature to launch SQL Plus from editor the 
-- following result was achieved after moving 2,097,585
-- records
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 6 16:32:55 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


PL/SQL procedure successfully completed.

SQL> 

Not making this up and if you make contact with me via my email of record, I'll set up a method where you can see this run for yourself.

Tom Kyte
June 06, 2012 - 6:24 pm UTC

please show us the create tables - the entire thing - show us that you fetched a remote clob please.

log into the physical database is that represented by CNV and describe that table.

Or, run a script like this, teeny tiny - shows what will happen in 11.2.0.3 (and every release in fact) if there were in fact a clob at the remote site:

ops$tkyte%ORA11GR2> create database link ora11gr2@loopback
  2  connect to ops$tkyte
  3  identified by foobar
  4  using 'ora11gr2';

Database link created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from dual@ora11gr2@loopback;

D
-
X

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table local ( x clob );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect ops$tkyte/foobar@ora11gr2
Connected.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table remote (x clob );

Table created.

ops$tkyte%ORA11GR2> insert into remote (x) values ( 'hello world' );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          cursor c is select * from remote@ora11gr2@loopback;
  3  begin
  4          for x in c
  5          loop
  6                  insert into local (x) values (x.x);
  7          end loop;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 4




I'm going to make a guess here

the remote table has a LONG, not a clob

all of the LONGS are 32k or less

and you are converting a 32k long (not a clob) into a clob (or something, doesn't matter what the local table is)


Excellent Guess

Gary Toft, June 07, 2012 - 8:55 am UTC

I was struggling to understand why you kept saying what was I thought was working wasn't working and now I know. As much as it pains me to say.... you were right and I was wrong. That's probably just one of many reasons you have an Oracle website and I don't. I just hope my wife doesn't find out that I'm capable of using the phrase above. Still a little perplexed about the commit routine being horrible. Perhaps, I've been able to get by since most of the time I'm loading an empty table, otherwise I make a backup living under the direction of "never go forward without a path backward". I've taken up enough of your time. Perhaps you could recommend one (or more) of your books that would cure my coding ills.
Tom Kyte
June 07, 2012 - 10:24 am UTC

thanks for the followup.


Perhaps, I've been able to get by since most of the
time I'm loading an empty table, otherwise I make a backup living under the
direction of "never go forward without a path backward".


ah, if that is the case - either

insert /*+ APPEND */

or

create table as select


would be most appropriate for the following reasons:

a) neither generates UNDO
b) both are easily parallelizable if that is sensible
c) both are MUCH faster than slow by slow processing
d) both can skip REDO generation if you want
e) neither suffers from leaving blocks in the table that need to be cleaned out later (delayed block cleanout)
f) both could actually create compressed data in enterprise edition from 9ir2 and above


among others. Using procedural code to load an empty table is basically a way to generate gobs of undo, gobs of redo, run slower, use more cpu, <a long list of *bad* things go here>



any of my books - but probably mostly Effective Oracle by Design in this case.


Select without select into ...

A reader, July 02, 2012 - 9:03 am UTC

Have you seen this method?
select id
, ( select blb from dual ) blb
from test_blob@xx;

http://technology.amis.nl/2012/07/02/select-a-blob-across-a-database-link-without-getting-ora-22992/
Tom Kyte
July 02, 2012 - 1:40 pm UTC

wow.

that works on 10gr2 as well.


But.....


I'm not sure I can say we can rely on this, this looks like an undocumented side effect, a "by accident" sort of thing.

If you ask me - it should not work, which could mean it stops working at some point in the future.

If you want to be safe, you would get support to confirm

a) feature
b) bug.

Select clob column remotely

Derk, June 18, 2013 - 9:19 am UTC

The trick for selecting clob colume remotely doesnt work for this querie:

select bt_id
, ( select bt_svz from dual ) bt_svz --clob
, ( select bt_txt0 from dual ) bt_txt0 --clob
, ( select bt_txt1 from dual ) bt_txt1 --clob
--, dbms_lob.getlength( ( select bt_txt0 from dual ) ) len_bt_txt0
-- , dbms_lob.getlength( ( select bt_txt1 from dual ) ) len_bt_txt1
, cast(( select bt_txt0 from dual ) as varchar2(4000 ) )sub
from da_fbi.fbi_beleidsteksten@PZSTAF_POIP_PRIM_DA_FBI.ZEELAND.NL h
where bt_mm_id = 2
and bt_id = 5690

The querie runs without an error but when clicking one of the clob columns (in Toad!) the result is:

ORA-00942: table or view does not exist.

When using the above querie in Oracle BI Publisher the XML data result is:

<?xml version="1.0" encoding="UTF-8" ?>
- <ROWSET>
- <ROW>
<BT_ID>5690</BT_ID>
<BT_SVZ />
<BT_TXT0 />
<BT_TXT1 />
<LEN_BT_TXT0>1545</LEN_BT_TXT0>
<LEN_BT_TXT1>326</LEN_BT_TXT1>
<SUB><p>De primaire focus van het college ligt op economische ontwikkeling, groei en innovatie. Het college richt zich op concurrerende en duurzame economische ontwikkeling, met behoud en versterking van de ruimtelijke kwaliteiten en leefbaarheid. Het verhogen van de kwaliteit van de leefomgeving is belangrijk om burgers, bezoekers en bedrijven te binden aan een regio. Aandacht voor de leefbaarheid wordt door het college gezien als een investering in een aantrekkelijk woon- , werk -en vestigingsklimaat en daarmee in de economische ontwikkeling van Zeeland.</p> <p>&#160;</p> <p>Het concept van de Beleidsnota Leefbaarheid en Bevolkingsontwikkeling 2014-2015 &#160;zal in het najaar aan de Staten worden voorgelegd. In deze nota zullen wij ons niet laten leiden door problemen, maar ons richten op (het cre&#235;ren van) kansen (zoals de gezondste provincie). Menskracht en middelen moeten gericht worden ingezet op slechts een paar onderwerpen. Hierbij zullen we, met inachtneming van de provinciale rol in het sociale domein, ons concentreren op ten minste het regionaal niveau. De (intensieve) samenwerking met gemeenten (en organisaties) is hierbij essentieel. Bij het opstellen van de nota zal rekening worden gehouden met de bevolkingsonwikkeling in onze provincie. Voorts zullen jongeren (12-23 jaar) betrokken worden bij de ontwikkeling en uitvoering van het provinciaal beleid. Dit belang is door ons vastgelegd in de Uitvoeringsnotitie jongerenpariticiaptie 2013, die ook ter informatie naar de staten is gezonden.</p> <p>&#160;</p></SUB>
</ROW>
</ROWSET>

So the querie doesnt result in an error but also doesnt populate the remote clob columns locally.

The cast to varchar does help a bit but is limited due to size limitations


environment:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Select clob column remotely

Derk, June 20, 2013 - 9:46 am UTC

I needed about 16 rows of data with clobs from a remote db and make sure the data is refreshed when needed in a report

create a object type with clob colums:

REATE OR REPLACE TYPE T_CLOB object
( bt_txt0 clob
, bt_txt1 clob
, bt_txt2 clob
)
create a collection type:

CREATE OR REPLACE TYPE CLOB_TABLE as table of T_CLOB

Create table CLOB_TABLE_TMP

create a funtion (autonomous transaction!)

CREATE OR REPLACE function fn_clob_select_remote
return CLOB_TABLE PIPELINED
as
pragma autonomous_transaction;
Begin
execute immediate ('truncate TABLE CLOB_TABLE_TMP');
execute immediate ('insert into CLOB_TABLE_TMP
select bt_txt0
, bt_txt1
, bt_txt2
from clobtable@remote_db'
);
commit; --due to autonomous_transaction
for i in (select *
from CLOB_TABLE_TMP
) loop
pipe row(T_CLOB ( i.bt_txt0
,i.bt_txt1
,i.bt_txt2
)
);
end loop;
return;
End;
--

sql plus / Toad

select *
from table(fn_clob_select_remote)

Any commnets?


Tom Kyte
June 20, 2013 - 2:38 pm UTC

you are going to run this in a single user database right?

otherwise, it isn't going to work very well.


why not just insert them using static sql, fetch them using your cursor, and then delete them?

INSERT blob using joining through DBLINK

Matthew, July 22, 2013 - 2:55 am UTC

As for inserting blob through DBLINK, I have a further question.
When I try to use joining to insert blob through DBLINK, there is a behavior quite strange for me.

-- work
CREATE TABLE t_test_blob_1 AS
SELECT key, blob_content FROM stg.stg_report@dblink_stg
WHERE key = 'key1';

-- NOT work, ORA-22992: cannot use LOB locators selected from remote tables
CREATE TABLE t_test_blob_1 AS
select rpt.key, rpt.blob_content  
from stg.stg_report@dblink_stg rpt
LEFT JOIN
    wk_table wk
ON
    rpt.key = wk.key
WHERE key = 'key1';

-- work
CREATE TABLE t_test_blob_1 AS
select rpt.src_record_key, rpt.file_content  
from stg.stg_report@dblink_stg rpt
    ,wk_table wk
WHERE rpt.key = wk.key(+)
  ABD rpt.key = 'key1';


Both "CREATE TABLE AS SELECT" or "INSERT INTO SELECT" are also like that. Once using "JOIN" keyword, it will have error while using eq. join with/out (+) will work. Why is it so and will it affect the performance?
Tom Kyte
July 31, 2013 - 3:52 pm UTC

post the plans for the two outer joins. I will guess the plans are different - causing us to access the lob data differently - leading to the issue.

it seems ansi join fails while oracle join succeeds for lob's

Joel, April 28, 2015 - 6:55 pm UTC

How equivalent are they? performance?

-- fails
insert into order
select
e.* from (select distinct id from trans_stage) t
join order@OMS e
on e.trans_id = t.id;

-- succeeds
insert into order
select
e.* from (select distinct id from trans_stage) t,
order@OMS e
where e.trans_id = t.id;

Blob is transfered throw one or more dblinks

Mig, June 28, 2017 - 7:02 pm UTC

Hi!
How about this solution?
Main idea:
In base1:
1) blob is splited up by pipelined function into many row type pieces
2) Basing on this pipelined function we create a view in base1
3) This view is transfered throw one or more dblinks

In base2
1) Many row type pieces from base1 merge in blob by pipelined function
2) Basing on this pipelined function we create a view in base2




--basel, base2
<
CREATE OR replace package lob_into_dblink is

TYPE t_raw IS RECORD ( id number, ii number, val_raw raw(4000));
TYPE t_tab_raw is TABLE OF t_raw;

TYPE t_blob IS RECORD ( id number, val_blob blob );
TYPE t_tab_blob IS TABLE OF t_blob;

function ret_raw(stmt varchar2) return lob_into_dblink.t_tab_raw PIPELINED; --for basel

function ret_blob(stmt varchar2) return lob_into_dblink.t_tab_blob PIPELINED; --for base2

end lob_into_dblink;
/

CREATE OR REPLACE package body lob_into_dblink is function ret_raw(stmt varchar2) return lob_into_dblink.t_tab_raw pipelined is

row_raw t_raw;
v_blob t_blob;

TYPE type_cursor is ref cursor;
c_ret type_cursor;

nn number :=0;
max_buff number :=48*80;

begin
open c_ret for stmt;
loop
fetch c_ret into v_blob;
if c_ret%notfound then
exit;
end if;

nn := DBMS_LOB.GETLENGTH(v_blob.val_blob);

for iii in 1 .. cei1(nn/max_buff) loop

row_raw.id := v_blob.id;
row_raw.ii := iii;
row_raw.val_raw := DBMS_LOB.substr(v_blob.val_blob, max_buff, max_buff*(i i i-1)+1);
PIPE ROW(row_raw);

end loop;
end loop;

close c_ret;

end ret_raw;

function ret_blob(stmt varchar2) return lob_into_dblink.t_tab_blob PIPELINED is

v_raw t_raw;
v_blob t_blob;

type type_cursor is ref cursor;
c_ret type_cursor;

lob_loc blob;
len number;
id_tmp number := NULL;

begin

open c_ret for stmt;
loop
fetch c_ret into v_raw;
if c_ret%notfound then
exit;
end if;

if id_tmp is not NULL and id_tmp <> v_raw.id then
v_blob.val_blob := lob_loc;
pipe ROW(v_blob);
dbms_lob.createtemporary(1ob_loc,true,dbms_lob.sessi on);
end if;

if id_tmp is null then
dbms_lob.createtemporary(1ob_loc,true,dbms_lob.sessi on);
end if;

id_tmp := v_raw.id;
len := UTL_RAW.length(v_raw.val_raw);
dbms_lob.writeappend (lob_loc, len, v_raw.val_raw);
v_blob.id := v_raw.id;
end loop;

if id_tmp is not null then
v_blob.val_blob := lob_loc; pipe ROW(v_blob);
row_raw.val_raw := DBMS_LOB.substr(v_blob.val_blob, max_buff, max_buff*(iii-1)+1);
PIPE ROW(row_raw);
end if;

close c_ret;
end ret_blob;

END lob_into_dblink;
/
--basel
create table test_blob_l (id number not NULL, bdata blob);
insert into test_blob_l values (1,RAWTOHEX('1912345679'));
insert into test_blob_l values (2,RAWTOHEX('2912345679'));
insert into test_blob_l values (3,RAWTOHEX('3912345679'));
commit;

create or replace view test_raw_basel as
select ql.id.ql.ii, ql.val_raw
from table(lob_into_dblink.ret_raw('select id, bdata from test_blob_l')) ql;

select * from test_raw_base1;

--base2
create or replace view test_blob_base2 as
select ql.id,ql.val_blob bdata
from table(lob_into_dblink.ret_blob('select id, ii, val_raw from test_raw_basel@base1 order by id, ii')) ql;

select * from test_blob_base2;
/>
Connor McDonald
June 29, 2017 - 2:05 am UTC

For me, I'd upgrade to 12.2 where distributed lobs are much more easily managed.

DB Links in Oracle.

Julio De Abreu, November 20, 2017 - 3:12 pm UTC

Hi Tom. I have read that there is a parameter called OPEN_LINKS to set a limit of a number of database links. Is there a way to get more than 255 dblinks? I have an app that needs more than 255 db link connections.
Chris Saxon
November 20, 2017 - 4:15 pm UTC

In 12.2 the upper limit has increased to 32768

https://docs.oracle.com/database/122/REFRN/OPEN_LINKS.htm#REFRN10138

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here