Home » SQL & PL/SQL » SQL & PL/SQL » Error in reading XML message and inserting data into another DB using DB link (Oracle 11g)
Error in reading XML message and inserting data into another DB using DB link [message #602124] Fri, 29 November 2013 12:21 Go to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi Experts,

I am getting error parsed XML message data while inserting into another DB table using DB link.

ORA-22804: remote operations not permitted on object tables or user-defined type columns.

MERGE INTO unit_labels@WED_REMODE_DB old
            USING(
                select y.unit unit,y.cnd_id cnd_id,y.rate_number rate_number,y.non non
              from XmlTable(
                    '/MESSAGES/MESSAGE'
                    passing xmltype(' <MESSAGES>
<MESSAGE ID="3026900">
  <MSG_ID>3026900</MSG_ID>
  <DT_POSTED>6/20/2013 08:15:48</DT_POSTED>
  <POSTED_BY>GPD_MSG_EXTRACTOR</POSTED_BY>
  <DT_LAST_QUEUED />
<MSG>
<WORK_SET TRANSACTION_ID="@TRANS_ID" TRANSACTION_TYPE="Batch" IS_ACID="@IS_ACID">
<WORK_UNIT GROUP="rate_number" ACTION="UPDATE" AFFECTED="rate_number">
<RECORDSET TABLE_NAME="rate_number">
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<rs:data>
  <z:row cnd_id="3838" non="AT" unit="A0814818" dw_maint_flag="False" stock_status_id="0" rate_number="30" est_qty="0" candid_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_unit_PROCESS" candid_dts="2010-05-22T02:21:21.707" candid_uid="rate_number_Rules" />
  <z:row cnd_id="202" non="GB" unit="A0814819" dw_maint_flag="False" stock_status_id="0" rate_number="30" est_qty="0" candid_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_unit_PROCESS" candid_dts="2010-05-22T02:21:21.707" candid_uid="rate_number_Rules" />
  <z:row cnd_id="2828" non="BE" unit="A0814820" dw_maint_flag="False" stock_status_id="0" rate_number="30" est_qty="0" candid_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_unit_PROCESS" candid_dts="2010-05-22T02:21:21.707" candid_uid="rate_number_Rules" />
</rs:data>
  </xml>
  </RECORDSET>
  </WORK_UNIT>
  </WORK_SET>
  </MSG>
  </MESSAGE>
  </MESSAGES>')
  columns
      MESSAGE_NO  for ordinality
    , MESSAGE_ID  number  path '@ID'
    , data        xmltype path 'MSG/WORK_SET/WORK_UNIT/RECORDSET'
  ) x
, xmltable(
    xmlnamespaces('urn:schemas-microsoft-com:rowset' as "rs", '#RowsetSchema' as "z"),
    '/RECORDSET/xml/rs:data/z:row'
    passing x.data
     columns cnd_id number path './@cnd_id'
                          ,non       varchar2(2)  path './@non'
                         ,unit       varchar2(10) path './@unit'
                         ,rate_number number       path './@rate_number'
                ) y
            ) new
              on (old.unit = new.unit
          AND old.cnd_id = new.cnd_id)
            WHEN MATCHED THEN UPDATE
               SET old.rate_number = new.rate_number,
            WHEN NOT MATCHED THEN INSERT
                 (  old.unit
                   , old.cnd_id
                   , old.rate_number
                   , old.CRD_BY
                   , old.CRD_DATE       
                 ) values
                 ( new.unit
                 , new.cnd_id
                 , new.rate_number
                 , 'SYSTEM'
                 , SYSDATE
                 );
 
DDL of target table.
 
CREATE TABLE unit_labels
(   
  unit             VARCHAR2(25 CHAR)      NOT NULL,
  cnd_id           VARCHAR2(4 CHAR)       NOT NULL,
  rate_number      NUMBER,
  crd_by           VARCHAR2(100 CHAR)     NOT NULL,
  crd_date         DATE                   NOT NULL
) ;
 
ALTER TABLE unit_labels ADD (CONSTRAINT unit_labels_PK PRIMARY KEY(unit, cnd_id));


Please help me.

Thanks.
Re: Error in reading XML message and inserting data into another DB using DB link [message #602133 is a reply to message #602124] Fri, 29 November 2013 15:39 Go to previous messageGo to next message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
Senior Member

Read this article


ORA-22804: remote operations not permitted on object tables or User-defined type columns


Manu
Re: Error in reading XML message and inserting data into another DB using DB link [message #602144 is a reply to message #602133] Sat, 30 November 2013 00:48 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi,

Instead of XML message If I use tables I am able to merge data in the remote DB table.

Please help me.

Thanks.
Re: Error in reading XML message and inserting data into another DB using DB link [message #602147 is a reply to message #602144] Sat, 30 November 2013 03:15 Go to previous messageGo to next message
ThomasG
Messages: 3101
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the "XmlTable" might be an object internally, so you run into the problem.

I can think of a few options to try:

- Create a view with the XML Select and try to insert from that.
- Create a procedure on the target database that PULLS the data instead of pushing it.
- Do a PL/SQL Loop over the result set and push the single rows.

Re: Error in reading XML message and inserting data into another DB using DB link [message #602159 is a reply to message #602147] Sat, 30 November 2013 10:58 Go to previous message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi,

Can we do by crating synonym for the remote db table in local Db?

Thanks.
Previous Topic: Multiple groupings in SQL
Next Topic: Please help with multiple update
Goto Forum:
  


Current Time: Tue Oct 21 10:18:56 CDT 2014

Total time taken to generate the page: 0.08395 seconds