0

I'm using Oracle SQL Developer for find the issue with the data that the MERGE statement raises the ORA-30926 error.

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

The problem I have is that this issue is happening on production server and we don't have insert privileges - we can only query the data.

So, I decide to use CTEs for build the sub-query "with the data the table used by the MERGE statement".

The CTE is as follows:

WITH SAMPLE_TABL
AS
(
SELECT 8 IIY_CODEN, '1016755' NUM_DCSM, 1 RW_NSKD FROM DUAL UNION ALL 
SELECT 1 IIY_CODEN, '30029' NUM_DCSM, 1 RW_NSKD FROM DUAL UNION ALL 
SELECT 1 IIY_CODEN, '21652019' NUM_DCSM, 1 RW_NSKD FROM DUAL UNION ALL 
SELECT 1 IIY_CODEN, '10038' NUM_DCSM, 1 RW_NSKD FROM DUAL UNION ALL 
SELECT 1 IIY_CODEN, '110004567448' NUM_DCSM, 1 RW_NSKD FROM DUAL
[...] -- 32706 rows 
)
SELECT T1.* 
FROM SAMPLE_TABL T1;

I'm constructing this sub-query for emulate the content of a table that is used on a MERGE statement that raises the ORA-30926 error.

With this result of the sub-query, I then run the SELECT that is used on the MERGE statement and export such results. Then, I can check which are the duplicated rows.

The SELECT used in the MERGE has already a DISTINCT keyword on it, so, I'm narrow it down to steps for see the data that is executed on that specific MERGE.

The information that is used on the CTE was supplied by the customer on a Excel file, so, I had to create the CTE in this way.

NOTE: If anyone know alternative(s) about how this issue can be handled, I'm open to suggestions.

This CTE has 32706 rows and, testing in a different database, the query completes after 12 minutes.

I tried this query on production and I have to cancel the query after 24 minutes and the cancellation took another more minutes.

I suspect this "result" is due to oracle/sql developer limitations in GUI or probably the database engine itself, but, I really don't know how then I can have a running sub-query with these 32K rows in order to check further the cause of the ORA-30926 error.

These are the details of the SQL Developer instance I'm using - and I know that according to this comment, this SQL Developer version is very old, but, that's what I got for work.

About
---------

Oracle SQL Developer 17.2.0.188
 Versión 17.2.0.188
Versión Interna 188.1159

IDE Version: 13.0.0.1.42.170225.0201
Product ID: oracle.sqldeveloper
Product Version: 17.2.0.188.1159

Versión
-------

Componente  Versión
==========  =======
Oracle IDE  17.2.0.188.1159
Plataforma Java(TM) 1.8.0_131
Soporte de Control de Versiones 17.2.0.188.1159

I narrow this question as down to:

How I can have a running and working sub-query with 32K rows?

Marco Aurelio Fernandez Reyes
  • 2,071
  • 2
  • 9
  • 33
  • What's the goal here? Why are you constructing this 32,706-row query result? – Andy Lester Oct 11 '22 at 21:03
  • 1
    I'm constructing this sub-query for emulate the content of a table that is used on a MERGE statement that raises the `ORA-30926` error. With this result of the sub-query, I then run the SELECT that is used on the MERGE statement and export such results. Then, I can check which are the duplicated rows. The SELECT used in the MERGE has already a DISTINCT, so, I'm narrow it down to steps for see the data that is executed on that specific MERGE. – Marco Aurelio Fernandez Reyes Oct 11 '22 at 21:06
  • Why can't you just query the actual table that is used in the merge? – Alex Poole Oct 11 '22 at 21:22
  • @AlexPoole the actual table is empty and the 32K rows was provided by the customer in an excel file... – Marco Aurelio Fernandez Reyes Oct 11 '22 at 21:39
  • Use either global temporary tables or bind collections. Moreover in case of Excel(it stores data as xml), it's much easier to bind it as `XMLtype` and use it with `xmltable()` – Sayan Malakshinov Oct 12 '22 at 00:55
  • @SayanMalakshinov thanks for your comment. About the global temporary, we don't have those privileges on production (*where the issue is*) and asking for such privileges is time-consuming; about the Excel, can you point me to a sample to follow, please? – Marco Aurelio Fernandez Reyes Oct 12 '22 at 13:18

0 Answers0