Solved

getting error when inserting data from remote table

Posted on 2011-09-02
21
943 Views
Last Modified: 2012-06-21
SYS@db1> insert into bginfo.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null);
insert into bginfo.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null)
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-02063: preceding line from cdb1
0
Comment
Question by:ajaybelde
  • 10
  • 9
  • 2
21 Comments
 

Author Comment

by:ajaybelde
Comment Utility
SYS@db1> desc dba_audit_trail
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OS_USERNAME                                        VARCHAR2(255)
 USERNAME                                           VARCHAR2(30)
 USERHOST                                           VARCHAR2(128)
 TERMINAL                                           VARCHAR2(255)
 TIMESTAMP                                          DATE
 OWNER                                              VARCHAR2(30)
 OBJ_NAME                                           VARCHAR2(128)
 ACTION                                    NOT NULL NUMBER
 ACTION_NAME                                        VARCHAR2(28)
 NEW_OWNER                                          VARCHAR2(30)
 NEW_NAME                                           VARCHAR2(128)
 OBJ_PRIVILEGE                                      VARCHAR2(16)
 SYS_PRIVILEGE                                      VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(1)
 GRANTEE                                            VARCHAR2(30)
 AUDIT_OPTION                                       VARCHAR2(40)
 SES_ACTIONS                                        VARCHAR2(19)
 LOGOFF_TIME                                        DATE
 LOGOFF_LREAD                                       NUMBER
 LOGOFF_PREAD                                       NUMBER
 LOGOFF_LWRITE                                      NUMBER
 LOGOFF_DLOCK                                       VARCHAR2(40)
 COMMENT_TEXT                                       VARCHAR2(4000)
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENTID                               NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER
 PRIV_USED                                          VARCHAR2(40)
 CLIENT_ID                                          VARCHAR2(64)
 ECONTEXT_ID                                        VARCHAR2(64)
 SESSION_CPU                                        NUMBER
 EXTENDED_TIMESTAMP                                 TIMESTAMP(6) WITH TIME ZONE
 PROXY_SESSIONID                                    NUMBER
 GLOBAL_UID                                         VARCHAR2(32)
 INSTANCE_NUMBER                                    NUMBER
 OS_PROCESS                                         VARCHAR2(16)
 TRANSACTIONID                                      RAW(8)
 SCN                                                NUMBER
 SQL_BIND                                           NVARCHAR2(2000)
 SQL_TEXT                                           NVARCHAR2(2000

SYS@cdb1> desc info.testing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DB_UNIQUE_NAME                                     VARCHAR2(30)
 OS_USERNAME                                        VARCHAR2(255)
 USERNAME                                           VARCHAR2(30)
 USERHOST                                           VARCHAR2(128)
 TERMINAL                                           VARCHAR2(255)
 TIMESTAMP                                          DATE
 OWNER                                              VARCHAR2(30)
 OBJ_NAME                                           VARCHAR2(128)
 ACTION                                    NOT NULL NUMBER
 ACTION_NAME                                        VARCHAR2(28)
 NEW_OWNER                                          VARCHAR2(30)
 NEW_NAME                                           VARCHAR2(128)
 OBJ_PRIVILEGE                                      VARCHAR2(16)
 SYS_PRIVILEGE                                      VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(1)
 GRANTEE                                            VARCHAR2(30)
 AUDIT_OPTION                                       VARCHAR2(40)
 SES_ACTIONS                                        VARCHAR2(19)
 LOGOFF_TIME                                        DATE
 LOGOFF_LREAD                                       NUMBER
 LOGOFF_PREAD                                       NUMBER
 LOGOFF_LWRITE                                      NUMBER
 LOGOFF_DLOCK                                       VARCHAR2(40)
 COMMENT_TEXT                                       VARCHAR2(4000)
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENTID                               NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER
 PRIV_USED                                          VARCHAR2(40)
 CLIENT_ID                                          VARCHAR2(64)
 ECONTEXT_ID                                        VARCHAR2(64)
 SESSION_CPU                                        NUMBER
 EXTENDED_TIMESTAMP                                 TIMESTAMP(6) WITH TIME ZONE
 PROXY_SESSIONID                                    NUMBER
 GLOBAL_UID                                         VARCHAR2(32)
 INSTANCE_NUMBER                                    NUMBER
 OS_PROCESS                                         VARCHAR2(16)
 TRANSACTIONID                                      RAW(8)
 SCN                                                NUMBER
 SQL_BIND                                           NVARCHAR2(2000)
 SQL_TEXT                                           NVARCHAR2(2000)


0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 334 total points
Comment Utility
>>ORA-22992: cannot use LOB locators selected from remote tables

Error seems self explanatory.  CLOBs across database links are problematic.

See if any of these solutions will work for you:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:950029833940
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
the problem is the underlying sys.aud$ table has CLOB columns
0
 

Author Comment

by:ajaybelde
Comment Utility
yes sdstuber, i am seeing clob objects in aud$, but i am not clob objects in dba_audit_trail, can u help me how to do this..
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
dba_audit_trail selects from aud$,  so you are pulling the CLOB

but,  dba_audit_trail cuts the CLOB's off at 2000 characters.

you want Oracle to resolve the substr calls before trying to send the data across the link, so you'll have varchar2  results instead of clob results

sometimes you can do that simply by sticking in rownum, because rownum doesn't exist until the results are generated.

this will mean adding an extra column to your testing table though

insert into bginfo.testing@cdb1(select 'db1',rownum, a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null);
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you could also try ordering the data, for the same reason.
ordering doesn't happen until the results are done
this way might be slower but it won't require modifying your table structure


insert into bginfo.testing@cdb1(select 'db1', a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null order by timestamp);
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you could also try ordering the data, for the same reason.
ordering doesn't happen until the results are done
this way might be slower but it won't require modifying your table structure


insert into bginfo.testing@cdb1(select 'db1', a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null order by timestamp);
0
 

Author Comment

by:ajaybelde
Comment Utility
SYS@db1> insert into info.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate and timestamp is null order by times
insert into info.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate and timestamp is null order by timestamp)
                                                                                                                                                                                                                *
ERROR at line 1:
ORA-00907: missing right parenthesis
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
oh sorry, the order by isn't allowed on the insert,  did you try the rownum method?
0
 

Author Comment

by:ajaybelde
Comment Utility
no..what will rownum does..? can u explain me once..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ajaybelde
Comment Utility
SYS@db1> insert into info.testing@cdb1(select 'UTOLHO01',rownum,a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null);
insert into bginfo.testing@uapxho1(select 'UTOLHO01',rownum,a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null)
                   *
ERROR at line 1:
ORA-00913: too many values
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
did you add the extra column ?
0
 

Author Comment

by:ajaybelde
Comment Utility
no dude..
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
that would do it.


if you include rownum in the select output, you must have a corresponding numeric column in the same position of the table.

or, specify the column order (better option, more robust)  in both the insert and the select
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 total points
Comment Utility
>>no dude..

Like sdstuber suggested here and why I mentioned it in your other question:  You should not use insert into with a select *.

You should specify the individual column on the insert side and the select side.

This way you know with 100% accuracy what goes where.
0
 

Author Comment

by:ajaybelde
Comment Utility
SQL> /
insert into info.testing@prod(select 'dev',os_username,username,userhost,terminal,timestamp,owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_bind,sql_text  from dba_audit_trail a where timestamp < sysdate or timestamp is null)
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-02063: preceding line from PROD
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 166 total points
Comment Utility
same problem mentioned above http:#36473589

you are selecting the lobs from dba_audit_trail because dba_audit_trail selects from aud$


either don't select them  (sql_bind, sql_text)  or you must force the dba_audit_trail to resolve those fields to varchar2 before sending to the remote system.
0
 

Author Comment

by:ajaybelde
Comment Utility
SQL> /
insert into info.testing@prod(select 'dev',os_username,username,userhost,terminal,timestamp,owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn from dba_audit_trail where timestamp < sysdate or timestamp is null)
                   *
ERROR at line 1:
ORA-00947: not enough values
0
 

Author Comment

by:ajaybelde
Comment Utility
 1  insert into bginfo.testing@prod(db_unique_name,os_username,username,userhost,terminal,timestamp,
  2  owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,
  3  grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,
  4  comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,
  5  extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_text,sql_bind)
  6  (select d.db_unique_name,os_username,username,userhost,terminal,timestamp,
  7  owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,
  8  grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,
  9  comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,
 10  extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_text,sql_bind from v$database d,
 11  dba_audit_trail
 12* where timestamp < sysdate or timestamp is null)
SQL> /
extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_text,sql_bind from v$database d,
                                                                             *
ERROR at line 10:
ORA-02070: database  does not support  in this context

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you've changed the query by adding additional table
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
ajaybelde,

are you sure you got your answer?

http:#36473589 answers the question as to why the error occured but it wasn't selected.

Of the other 3 that were selected, none of the suggestions to address the clob issue were followed.

You did switch to named columns vs '*', but that was a peripheral issue, it had nothing to do with the clob problem you asked about.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now