Home » SQL & PL/SQL » SQL & PL/SQL » merge causes problem in distributed environment (merged)
merge causes problem in distributed environment (merged) [message #268247] Tue, 18 September 2007 01:06 Go to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
I have two database instance on oracle 10 G

MERGE INTO LRD_BUS_PRIORITY b1
USING(SELECT DISTINCT BASEVERSION, VEHICLEID, ROUTEID, TRIPID, SENDTIME, JUNCTIONID, MOVEMENTNR, TRIGGERPOINTID, TELEGRAM, REPETITIONS, ACK FROM LRDSTAGING.BUS_PRIORITY WHERE ROUTEID IS NOT NULL) b2
ON(b1.VehicleId = b2.VehicleId
AND b1.SendTime = b2.SendTime
AND b1.JunctionId = b2.JunctionId
AND b1.TriggerPointId = b2.TriggerPointId)
WHEN NOT MATCHED THEN
INSERT ( Baseversion, VehicleId, TripId, RouteId, SendTime
, JunctionId, MovementNr
, TriggerPointId, Telegram
, Repetitions, ACK)
VALUES( 1, b2.VehicleId, b2.TripId, b2.RouteId, b2.SendTime
, b2.JunctionId, b2.MovementNr
, b2.TriggerPointId, b2.Telegram
, b2.Repetitions, b2.ACK)

when I ran this command I got the
error ORA-02064: distributed operation not supported


when I run the insert and update separately then
it works fine
the LRD_BUS_PRIORITY is a synonym
made from a table in remote database as
i.e.

INSERT into LRD_BUS_PRIORITY( Baseversion,
VehicleId,
TripId,
RouteId,
SendTime,
JunctionId,
MovementNr,
TriggerPointId,
Telegram,
Repetitions,
ACK)
SELECT DISTINCT 1 BASEVERSION,
VEHICLEID,
ROUTEID,
TRIPID,
SENDTIME,
JUNCTIONID,
MOVEMENTNR,
TRIGGERPOINTID,
TELEGRAM,
REPETITIONS,
ACK
FROM LRDSTAGING.BUS_PRIORITY
WHERE ROUTEID IS NOT NULL;

update LRD_BUS_PRIORITY set Baseversion=1;

definition of synonym is

create public synonym LRD_BUS_PRIORITY for LRD.BUS_PRIORITY@lrd1011

I am confused as all dml insert, update, delete works fine
on this synonym , then why the error comes in merge statement
as merge is also a dml

PLEASE help me.......................
and reply ASAP
Re: problem in running in distributed environment [message #268253 is a reply to message #268247] Tue, 18 September 2007 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

ASAP is useless, we answer when we want. By the way, each and every one thinks his question is urgent and other ones not.

Currently, merge statement does not support distributed transaction. It is fixed in 11g (or it should be).

Regards
Michel
Re: problem in running in distributed environment [message #268339 is a reply to message #268247] Tue, 18 September 2007 04:33 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
My oracle version is

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

and the table is partitioned table and script is

create table BUS_PRIORITY
(
BASEVERSION NUMBER(10),
VEHICLEID NUMBER(10) not null,
ROUTEID NUMBER(10),
TRIPID NUMBER(10),
SENDTIME DATE not null,
JUNCTIONID NUMBER(10) not null,
MOVEMENTNR NUMBER(3),
TRIGGERPOINTID NUMBER(10) not null,
TELEGRAM VARCHAR2(40),
REPETITIONS NUMBER(2),
ACK NUMBER(1)
)
partition by range (SENDTIME)
(
partition BUS_PRIORITY_P20061110 values less than (TO_DATE(' 2006-11-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LRD_DATA1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition BUS_PRIORITY_P20061208 values less than (TO_DATE(' 2006-12-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LRD_DATA1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition BUS_PRIORITY_P20070105 values less than (TO_DATE(' 2007-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LRD_DATA1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
);



T know merge is used in distributed environment
but I am not able to run the merge command with this
configuration

Re: problem in running in distributed environment [message #268353 is a reply to message #268247] Tue, 18 September 2007 05:20 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
create table BUS_PRIORITY
(
BASEVERSION NUMBER(10),
VEHICLEID NUMBER(10) not null,
ROUTEID NUMBER(10),
TRIPID NUMBER(10),
SENDTIME DATE not null,
JUNCTIONID NUMBER(10) not null,
MOVEMENTNR NUMBER(3),
TRIGGERPOINTID NUMBER(10) not null,
TELEGRAM VARCHAR2(40),
REPETITIONS NUMBER(2),
ACK NUMBER(1)
)
partition by range (SENDTIME)
(
partition BUS_PRIORITY_P20061110 values less than (TO_DATE(' 2006-11-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LRD_DATA1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition BUS_PRIORITY_P20061208 values less than (TO_DATE(' 2006-12-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LRD_DATA1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition BUS_PRIORITY_P20070105 values less than (TO_DATE(' 2007-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LRD_DATA1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
);

this is the script of the table
that I am using but giving error
when using with the MERGE command
Re: problem in running in distributed environment [message #268360 is a reply to message #268353] Tue, 18 September 2007 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted, re-read the sticky.

Regards
Michel
merge causes problem in distributed environment [message #268692 is a reply to message #268247] Wed, 19 September 2007 06:54 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
hi,

I am working on Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

I have two databases and I am running a package on
one database that use to update the other database
table .

In the package I have used MERGE statement a lot
and while running the process the
error comes

ORA-01008: not all variables bound
ORA-02063: preceding line from LRD1011

the LRD1011 is the db link between them

and there is no variable in this all data comes from the table
then how can this be done
is there any permission that I have to give to dblink or package

bcoz same statement when I ran from a tool it works fine








Re: merge causes problem in distributed environment [message #268721 is a reply to message #268692] Wed, 19 September 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said in your previous topic:
MERGE DON'T WORK IN A DISTRIBUTED ENVIRONMENT

You can ask the question as you want, the answer is the same.

Regards
Michel
Re: merge causes problem in distributed environment (merged) [message #268850 is a reply to message #268247] Wed, 19 September 2007 22:35 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
But merge worked well in distributed environment
as i have tested it by creating a package and calling
merge into it works fine

but it gives error when I am using already defined
package .

How come same merge works fine in sqlplus

U have to think again
Re: merge causes problem in distributed environment (merged) [message #268851 is a reply to message #268247] Wed, 19 September 2007 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>How come same merge works fine in sqlplus
Please test again but before issuing MERGE statement do the following:
SQL> SET ROLE NONE
SQL> MERGE .........

Use CUT & PASTE to post whole session back here.
Re: merge causes problem in distributed environment (merged) [message #268860 is a reply to message #268247] Wed, 19 September 2007 23:21 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
the same error comes from the package as


SQL> set role none
2 /

Role set

SQL>
SQL> begin
2 -- Call the procedure
3 LRDSTAGING_MAIN.P_KPI(2,20061209);
4 end;
5 /

begin
-- Call the procedure
LRDSTAGING_MAIN.P_KPI(2,20061209);
end;

ORA-20100: Unexpected error occurred in P_KPI. ORA-20100: Unexpected error occurred in P_UNASSIGNED_MILES. ORA-01008: not all variables bound
ORA-02063: preceding line from LRD1011
ORA-06512: at "LRDSTAGING.LRDSTAGING_MAIN", line 1536
ORA-06512: at "LRDSTAGING.LRDSTAGING_MAIN", line 1561
ORA-01008: not all variables bound
ORA-02063: preceding line from LRD1011


but while running this merge from another package works fine

as

begin
2 -- Call the procedure
3 t.pro;
4 end;
5 /

PL/SQL procedure successfully completed

the merge that shows error in above will run fine in the lower one test package
Re: merge causes problem in distributed environment (merged) [message #268861 is a reply to message #268247] Wed, 19 September 2007 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
One definite fact - Privs acquired via ROLE do NOT apply within PL/SQL procedures.

You could be hitting a bug that is mis-reporting the "priv" failure.
Re: merge causes problem in distributed environment (merged) [message #268873 is a reply to message #268247] Wed, 19 September 2007 23:53 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
what do u mean i am not getting u

tell me how can I check this thing
that u r talking about

please share something with me
Re: merge causes problem in distributed environment (merged) [message #268876 is a reply to message #268247] Wed, 19 September 2007 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
As far as I know, "u" is not member of this forum.

You're On Your Own.
Re: merge causes problem in distributed environment (merged) [message #268880 is a reply to message #268247] Thu, 20 September 2007 00:02 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
hi buddy

without being a memeber of this forum
I am not able to put the replies, ok

and u can check my other topics also
if u have any kind of doubt

Re: merge causes problem in distributed environment (merged) [message #268882 is a reply to message #268873] Thu, 20 September 2007 00:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
vikeshcool@hotmail.com wrote on Thu, 20 September 2007 06:53
what do u mean i am not getting u

As it happens, I have a spare-keyboard, WITH a funcioning u-key.
It's free if you come and pick it up.

If you would have read the sticky, you would understand both this joke and anacedent's
Re: merge causes problem in distributed environment (merged) [message #290644 is a reply to message #268247] Mon, 31 December 2007 00:25 Go to previous message
rjdkolb
Messages: 1
Registered: December 2007
Junior Member
I had the same problem

It seems to be fixed in :
- 10.2.0.4 (Server Patch Set)
- 11.1.0.6 (Base Release) 11.1.0.6 (Base Release)

http://forums.oracle.com/forums/thread.jspa?messageID=2196892
Or translate : [Edit MC remove link that is a copy of Metalink note]

[Updated on: Mon, 31 December 2007 00:48] by Moderator

Report message to a moderator

Previous Topic: how to excute the output from query
Next Topic: materialized view
Goto Forum:
  


Current Time: Mon Dec 05 09:10:38 CST 2016

Total time taken to generate the page: 0.06305 seconds