Problem Description
I like to differentiate between columns of table HISTORY from two databases using database link as below and it resulted error ORA-02070.
SQL> select * from cols where table_name='HISTORY' minus select * from cols@tiger1.net where table_name='HISTORY';
select * from cols where table_name='HISTORY' minus select * from cols@tiger1.net where table_name='HISTORY'
*
ERROR at line 1:
ORA-02070: database TIGER1.NET does not support operator NLS_CHARSET_DECL_LEN
in this context
I found that the cols contain LONG column and hence result above error. If I only query long datatype data_default then same error return with different error message.
SQL> select data_default from cols minus select data_default from cols@tiger1.net;
select data_default from cols minus select data_default from cols@tiger1.net
*
ERROR at line 1:
ORA-02070: database does not support in this context
Experiment and cause of the Error
The reason behind the error is the long datatype of data_default column. Wheenever you go to access long datatype column of cols then above error returns. However for experiment I created LONG datatype column table but did not get above error. I conclude that only SYSTEM defined table/view/synonym throw above error and user defined table/view/synonym throw ORA-00997.
For experiment,
1)Create table t with only long column in both database and then try to use minus operation using database link.
SQL> create table t(a long);
Table created.
SQL> select * from t minus select * from t@tiger1.net;
select * from t minus select * from t@tiger1.net
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
ORA-02063: preceding line from TIGER1.NET
So in order to avoid above error just don't do any operation over LONG column through database link.
Monday, December 29, 2008
Subscribe to:
Post Comments (Atom)
Tag Cloud
10.2g
11g
About Oracle
Administration
Alerts
Archival
ASM
Audit
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Pump
Data Type
Database Administration
DBConsole
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Firefox
Firmware
Flashback
Forum
Functions
Globalization Support
History
HTML
IE
Import
Indexes
initializaion parameter
Installation
Internals
Internet
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Money
Multimedia
MySQL
Net Services
Network
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Utilities
Version
Views
Vmware
Windows
Wordpress

0 comments:
Post a Comment