1

Once a week, a procedure in a package is run from a cron job, only to give the error

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

The error appears to occur during an EXECUTE IMMEDIATE 'MERGE' statement.

However, without changing any of the data involved, merely recompiling the package and rerunning the procedure works. Next week, the issue happens again.

The merge statement's source comes from a staging table that is loaded from an external table first using an INSERT /*+ APPEND*/, followed by a COMMIT.

There are several other procedures within the package that do the same thing with different tables (all using the same staging table) that do not have a problem. Each procedure is run in sequence.

Any ideas?

XING
  • 9,608
  • 4
  • 22
  • 38
Nick
  • 73
  • 1
  • 2
  • 7
  • See the accepted solution of this question, please: http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables – UltraCommit Sep 16 '16 at 14:49
  • 2
    QUOTE: "This is usually caused by duplicates in the query specified in USING clause. This probably means that TABLE_A is a parent table and the same ROWID is returned several times. You could quickly solve the problem by using a DISTINCT in your query (in fact, if 'Y' is a constant value you don't even need to put it in the query)." – UltraCommit Sep 16 '16 at 14:50
  • [edit] your question and add the complete merge statement and the definition of the tables involved (as `create table` statements) –  Sep 16 '16 at 16:42
  • There is no duplicates in the query. As I mentioned in my description, the data is not touched, and is the same as it was when it failed. The only thing that happens is I recompile the package, and then it works. – Nick Sep 16 '16 at 17:25
  • It might be that there is in the database another package or procedure that DROP and CREATE a table or view used by your package. After DROP the package is decompiled. After CREATE the package remains decompiled until you compile it, and then it works. You have to see the list of the tables / views used by your package, and then you have to list all the procedures / packages that use the list of tables / views you have extracted. – UltraCommit Sep 17 '16 at 08:58
  • Do you use package variables to build the merge statement? – 0xdb Sep 23 '16 at 12:46
  • Sorry it took me so long to get back. After yet another recompile, the package continued to work. I've been keeping an eye on it (it only runs once a week), and it hasn't had a problem since. Have no idea what the problem was, as recompiling only fixed it for one week for about three weeks, and then it just decided to stay fixed after the fourth week's recompile. – Nick Nov 04 '16 at 14:36

0 Answers0