Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

getting error when inserting data from remote table

Posted on 2011-09-02
21
Medium Priority
?
984 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
[X]
Welcome to Experts Exchange

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

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 2
21 Comments
 

Author Comment

by:ajaybelde
ID: 36473530
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1336 total points
ID: 36473556
>>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 74

Expert Comment

by:sdstuber
ID: 36473589
the problem is the underlying sys.aud$ table has CLOB columns
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ajaybelde
ID: 36473884
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 74

Expert Comment

by:sdstuber
ID: 36473958
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 74

Expert Comment

by:sdstuber
ID: 36473970
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 74

Expert Comment

by:sdstuber
ID: 36473971
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
ID: 36474314
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 74

Expert Comment

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

Author Comment

by:ajaybelde
ID: 36474552
no..what will rownum does..? can u explain me once..
0
 

Author Comment

by:ajaybelde
ID: 36474597
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 74

Expert Comment

by:sdstuber
ID: 36474614
did you add the extra column ?
0
 

Author Comment

by:ajaybelde
ID: 36475247
no dude..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36475398
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 36475414
>>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
ID: 36484795
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 74

Assisted Solution

by:sdstuber
sdstuber earned 664 total points
ID: 36486060
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
ID: 36486180
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
ID: 36486266
 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 74

Expert Comment

by:sdstuber
ID: 36486342
you've changed the query by adding additional table
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36496513
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 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