Rewrite Merge statement to Update statement

Why can't you merge over a link?  What is the error?

Based on your previous question both databases are 11gR2.  I saw there used to be a bug prior to 10.2.0.4.

no, there are 3 databases involved.

Apex Server (11gr2)
Routing Server (11gr2)
ERP. (MS SQL Server)

When i was writing the merge statement yesterday, I was doing it to a local table that is a replica of the table i really want to update on the ERP. Once the syntax was correct, I added the database link to the true table that needs to be updated.

When executing the merge, all 3 servers are involved. data being fetched from routign server to apex, and apex server updating the SQL server

The Error is

Error starting at line : 1 in command -
MERGE INTO RMORHP@ermsd r
	USING (SELECT user_field1||route_id route, order_number, route_id, sequence_number from apex_sessiond@rn where rn_session_pkey = 28 and route_id = '658') a
	on (a.order_number = r.orhordnum)
WHEN MATCHED THEN update set r.orhrtenum = a.route
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-02070: database ERMSD does not support  in this context
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Select allOpen in new window

Cool.

Then did you add the where clause from your other question as suggested by Mark?

https://www.experts-exchange.com/questions/28473490/Update-statement-on-multiple-joins.html?anchorAnswerId=40189131#a40189131

A bit confused.

This is where I am,

UPDATE RNORHP n SET n.ORHRTENUM = (SELECT 
                                        o.USER_FIELD1||r.ROUTE_ID ERMS_ROUTE
                                      FROM 
                                        apex_sessiond@rn
                                      WHERE r.RN_SESSION_PKEY = 26 and n.ORHORDNUM = o.ORDER_NUMBER AND ROUTE_ID <> 'Unassigned' AND ROUTE_ID <> 'LI') a
                                      
                              where exists (select n.orhordnum from a join n on a.order_number = n.orhordnum);

Select allOpen in new window

I've refined it to this

UPDATE RNORHP SET ORHRTENUM = (SELECT 
                                        USER_FIELD1||ROUTE_ID ERMS_ROUTE
                                      from apex_sessiond@rn where rn_session_pkey = 28 and route_id = '658') 
                                      
                              where exists 
                                    (select NULL
                                        from RNORHP n join apex_sessiond@rn a on a.order_number = n.orhordnum);

Select allOpen in new window

now i get

SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

Select allOpen in new window

You still need the where clause on the inner query as well as on the update.

The inner query needs to return only one row.

If you can provide table setups, sample data and expected results we can provide a working example.

i get the same issue with an update statement, i got the update statement working correctly, but i get the same problem as the merge.

Is there a way to update tables on a remote sqlserver link based on values in a local view ?

SQL Error: ORA-02070: database ERMSD does not support subqueries in this context
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Select allOpen in new window

I've never set up a heterogeneous system so I cannot say how to do this.

Something in one of the sub queries is causing problems.  You would need to play around with rewriting the update to get around the issue and still update only the rows you need updated.

I might also look at writing a stored procedure to do this.  Then the select can be a loop and the update statement inside the loop.

Can you give me a an example of how to write something like this as a loop?

I am trying to write it as a pl/sql block. but am having issues with

ORA-01422: exact fetch returns more than requested number of rows

DECLARE
  ORDNUM  VARCHAR(15);
  RTENUM  VARCHAR(4);
  SEQNUM  VARCHAR(3);
  PKEY    VARCHAR(5);
BEGIN
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
INTO PKEY, RTENUM, SEQNUM, ORDNUM
FROM  RN_SESSIOND;
UPDATE RNORHP SET ORHRTENUM = RTENUM WHERE ORHORDNUM = ORDNUM;
 end;
 /

Select allOpen in new window

also, the subqueries are fine now, its a matter of updating with a select statement a remotedb.

i can however, update the DB if i did something like

"update remotedb@link set col1 = 'Blah';"

that executes successfully over the link, i suspect your theory of the loop, will work.

>>its a matter of updating with a select statement a remotedb.
>>"update remotedb@link set col1 = 'Blah';"

Since that works, then it is the sub queries throwing it off.  It's the update with a sub-select across the link.

>>Can you give me a an example of how to write something like this as a loop?

Something like:

DECLARE
BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND
) loop
UPDATE RNORHP SET ORHRTENUM = i.RTENUM WHERE ORHORDNUM = i.ORDNUM;
end loop;
 end;
 /

Select allOpen in new window

I used the above example and rewrote it to

DECLARE
BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND where rn_session_pkey = 30 and ROUTE <> '8Unassigned' and ORDER_NUMBER IN ('798560', '798425', '798573')) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = i.ORDER_NUMBER;
end loop;
 commit;
 end;
 /

Select allOpen in new window

but I am still getting

ORA-02070: database ERMSD does not support TO_NUMBER in this context
ORA-06512: at line 11
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Select allOpen in new window

note that the error changed from subquery to to_number context

Check the data types.  It looks like it is trying to do an implicit data conversion somewhere.

Make strings strings and numbers numbers explicitly with to_number and to_char function calls.

I did

BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      to_number(ROUTE) ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND where rn_session_pkey = 30 and ROUTE <> '8Unassigned' and ORDER_NUMBER IN ('798560', '798425', '798573')) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = i.ORDER_NUMBER;
end loop;
 commit;
 end;

Select allOpen in new window

ORHRTENUM is decimal,6 on the SQL Server. not sure  how to prcoeed.

the data on i.ROUTE is strictly numbers

>>the data on i.ROUTE is strictly numbers

Are you saying it is defined as a number or it is a varchar2 that only has numbers in it?

desc rn_sessiond

Name            Null Type          
--------------- ---- ------------- 
RN_SESSION_PKEY      NUMBER(38)    
ROUTE                NUMBER        
SEQUENCE_NUMBER      NUMBER(38)    
ORDER_NUMBER         NVARCHAR2(15) 

Select allOpen in new window

rn_sessiond is a view, not a table. that view is pulling table from a oracle 11gr2 db over a dblink.

the table i am updating is remote as well. mssql server.

the nvarchar2 is coming from the oracle11gr2 box for our routing and logistics software, the db was designed by that company.

ROUTE is really an nvarchar2 converted to number on the view.

Using

BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      to_number(ROUTE) ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND where rn_session_pkey = 30 and ORDER_NUMBER IN ('798560', '798425', '798573')) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = to_number(i.ORDER_NUMBER);
end loop;
 commit;
 end;

Select allOpen in new window

I am getting

Error report -
ORA-02070: database ERMSD does not support TO_NUMBER in this context
ORA-06512: at line 10
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Select allOpen in new window

afterwords, i converted to number in the a copy of the view (RNS)that's being called.

that seemed to do the trick.

BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RNS where rn_session_pkey = 30 and ORDER_NUMBER IN (798560, 798425, 798573)) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = i.ORDER_NUMBER;
end loop;
 commit;
 end;
 /

Select allOpen in new window

thank you sir, extremely helpful