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

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

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

Thanks
vis

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

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

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 

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? 

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.

 

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,

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?

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,
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,

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?

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

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 ?

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 ?

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,
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

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 ..
:)

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

 

Tom,

I have 3-2756048551 raised with Support.

regards

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,

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.

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,

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

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

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>

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

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>

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:

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:

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

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

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.

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!

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

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.

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.

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.

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

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.

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> 

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

2 rows created.

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.

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

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/

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

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?

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?

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;

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;
/>

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.