Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30926: unable to get a stable set of rows in the source tables (Oracle 10g)
ORA-30926: unable to get a stable set of rows in the source tables [message #352538] Wed, 08 October 2008 05:29 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

I get the following error when i run the below query inside the package

MERGE INTO mytable P
     USING IB_EXT_OCUVUELOS_V E
     ON (P.FLTDEP_DT = E.FLTDEP_DT
      AND P.FLTNO = E.FLTNO
      AND P.CARRIER_CODE = E.CARRIER_CODE
      AND P.ORIGIN = E.ORIGIN
      AND P.DESTINATION = E.DESTINATION)
     WHEN MATCHED THEN
    UPDATE
     SET type_of_aircraft = E.type_of_aircraft,
      tail_number = E.tail_number,
      ptow = E.ptow,
      densification = E.densification,
      dep_dt = E.dep_dt,
      temperature = E.temperature,
      origin_area = E.origin_area,
      destination_area = E.destination_area,
      payload = E.payload,
      no_of_passengers = E.no_of_passengers,
  baggage_weight = E.baggage_weight,
  no_of_pallets = E.no_of_pallets,
  no_of_containers = E.no_of_containers,
  outlier = null
      WHEN NOT MATCHED THEN
     INSERT (CARRIER_CODE, FLTNO, SUFFIX, TYPE_OF_AIRCRAFT, TAIL_NUMBER, PTOW, DENSIFICATION, FLTDEP_DT, DEP_DT, TEMPERATURE
  ,ORIGIN_AREA, ORIGIN, DESTINATION_AREA, DESTINATION, PAYLOAD, NO_OF_PASSENGERS, BAGGAGE_WEIGHT, NO_OF_PALLETS, NO_OF_CONTAINERS)
     VALUES (
      E.CARRIER_CODE, E.FLTNO, E.SUFFIX, E.TYPE_OF_AIRCRAFT, E.TAIL_NUMBER, E.PTOW, E.DENSIFICATION, E.FLTDEP_DT, E.DEP_DT, E.TEMPERATURE
  ,E.ORIGIN_AREA, E.ORIGIN, E.DESTINATION_AREA, E.DESTINATION, E.PAYLOAD, E.NO_OF_PASSENGERS, E.BAGGAGE_WEIGHT, E.NO_OF_PALLETS, E.NO_OF_CONTAINERS);



ORA-30926: unable to get a stable set of rows in the source tables

Can any one pls guide over this


Thanks in Advance

[Edit MC: table name changed at OP's request]

[Updated on: Thu, 23 October 2008 00:39] by Moderator

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #352539 is a reply to message #352538] Wed, 08 October 2008 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-30926: unable to get a stable set of rows in the source tables
 *Cause:  A stable set of rows could not be got because of large dml
          activity or a non-deterministic where clause.
 *Action: Remove any non-deterministic where clauses and reissue the dml.

Regards
Michel
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #352543 is a reply to message #352538] Wed, 08 October 2008 05:54 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


ie ,


Quote:
ON (P.FLTDEP_DT = E.FLTDEP_DT
AND P.FLTNO = E.FLTNO
AND P.CARRIER_CODE = E.CARRIER_CODE
AND P.ORIGIN = E.ORIGIN
AND P.DESTINATION = E.DESTINATION)


Make sure that ON condition results in One to One relation beween Source and Target table data .

Thumbs Up
Rajuvan.
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #352556 is a reply to message #352543] Wed, 08 October 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what the "Action" says?

Regards
Michel
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #352562 is a reply to message #352538] Wed, 08 October 2008 06:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes . Obviously ...

But the Action ,

 Remove any non-deterministic where clauses and reissue the dml


Speaks in more technical terms . It is Database Jargon .


Quote:
Jargon is terminology that relates to a specific activity, profession or group. Much like slang it develops as a kind of shorthand, to quickly express ideas that are frequently discussed between members of a group. In many cases a standard term may be given a more precise or specialized usage among practitioners of a field. In many cases this may cause a barrier to communication as many may not understand. In general, jargon is distinguished from argot.


And I tried to make it simple .

Don't you think it is made simpler ? Sad


Thumbs Up
Rajuvan.

[Updated on: Wed, 08 October 2008 06:52]

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #352567 is a reply to message #352562] Wed, 08 October 2008 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not a SQL forum? Should not the posters knows what is where clause? Non-deterministic is not a database jargon.

In addition, if OP don't understand something, it should be able to ask otherwise he does not deserve any explainations. Up (or down) to which level will you spoonfeed?
Is not "One to One relation beween Source and Target table data" database jargon?

Regards
Michel
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #352574 is a reply to message #352538] Wed, 08 October 2008 07:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

NO comments ..

I were OP , and I were told to Remove any non-deterministic where clauses , trust me , I wont understand the 'Action' to solve the issue unless i go for more detailed google search ( I am not discouraging the google search ) . And it would be more simpler for me understand "One to One relation" rather than "on-deterministic where clause" . Excuse me for not being not so professional .


I think we need to re-define the term " spoon feeding"

Thumbs Up
Rajuvan.
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #352576 is a reply to message #352567] Wed, 08 October 2008 07:23 Go to previous message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
hi guys,

I was trying with the condition given in the query all the condition satisfies i didnt get a way to solve this problem
but i tried the below one where the payload updation has been commented and the merge statement runs sucessfully

MERGE INTO mytable P
     USING IB_EXT_OCUVUELOS_V E
     ON (P.FLTDEP_DT = E.FLTDEP_DT
      AND P.FLTNO = E.FLTNO
      AND P.CARRIER_CODE = E.CARRIER_CODE
      AND P.ORIGIN = E.ORIGIN
      AND P.DESTINATION = E.DESTINATION)
     WHEN MATCHED THEN
    UPDATE
     SET type_of_aircraft = E.type_of_aircraft,
      tail_number = E.tail_number,
      ptow = E.ptow,
      densification = E.densification,
      dep_dt = E.dep_dt,
      temperature = E.temperature,
      origin_area = E.origin_area,
      destination_area = E.destination_area,
      [B]--payload = E.payload,[/B]
      no_of_passengers = E.no_of_passengers,
 baggage_weight = E.baggage_weight,
  no_of_pallets = E.no_of_pallets,
  no_of_containers = E.no_of_containers,
  outlier = null
      WHEN NOT MATCHED THEN
     INSERT (CARRIER_CODE, FLTNO, SUFFIX, TYPE_OF_AIRCRAFT, TAIL_NUMBER, PTOW, DENSIFICATION, FLTDEP_DT, DEP_DT, TEMPERATURE
  ,ORIGIN_AREA, ORIGIN, DESTINATION_AREA, DESTINATION, PAYLOAD, NO_OF_PASSENGERS, BAGGAGE_WEIGHT, NO_OF_PALLETS, NO_OF_CONTAINERS)
     VALUES (
      E.CARRIER_CODE, E.FLTNO, E.SUFFIX, E.TYPE_OF_AIRCRAFT, E.TAIL_NUMBER, E.PTOW, E.DENSIFICATION, E.FLTDEP_DT, E.DEP_DT, E.TEMPERATURE
  ,E.ORIGIN_AREA, E.ORIGIN, E.DESTINATION_AREA, E.DESTINATION, E.PAYLOAD, E.NO_OF_PASSENGERS, E.BAGGAGE_WEIGHT, E.NO_OF_PALLETS, E.NO_OF_CONTAINERS);


[Edit MC: table name changed at OP's request]

[Updated on: Thu, 23 October 2008 00:40] by Moderator

Report message to a moderator

Previous Topic: call an exe file from a DB Procedure
Next Topic: creating a excel file data from the sql prompt
Goto Forum:
  


Current Time: Sun Dec 04 02:12:28 CST 2016

Total time taken to generate the page: 0.09313 seconds