Oracle crash creating/executing view from another view in a CTE-CodePudding

I'm trying to create a view of a CTE in Oracle 19c, but any attempt to execute the SQL or create a view from it results in a crash of the session process, giving me a "No more data to read from socket" in SQL Developer and terminating my session.

I've got a table, DATA, with a few columns, including PROJECT_ID and DATA_IDENTITY. I can select from this table just fine. I've created a recursive CTE that does some work on this table, resulting in a subset of the data being returned when the view is executed. I then save this CTE as a view, ELEMENTS_BY_PROJECT_V - I can select from this view just fine.

Now I'm working with another recursive CTE, HIERARCHY_BY_ELEMENT_V, which uses that view as its first table. Here's an outline of the two CTE queries that I've described.

CREATE OR REPLACE VIEW ELEMENTS_BY_PROJECT_V AS
WITH 
    HISTORY(PROJECT_ID, COMMIT_ID, PREVIOUS_ID, LVL) AS (...),
    ELEMENT_DATA(PROJECT_ID, COMMIT_ID, DATA_IDENTITY, E_DATA, LVL) AS (...),
    LATEST_VERSIONS(LVL, DATA_IDENTITY_ID) AS (...)
SELECT D.PROJECT_ID, D.COMMIT_ID, D.DATA_IDENTITY, D.E_DATA FROM LATEST_VERSIONS V, ELEMENT_DATA D WHERE V.LVL=D.LVL AND V.DATA_IDENTITY=D.DATA_IDENTITY

CREATE OR REPLACE VIEW HIERARCHY_BY_ELEMENT_V AS
WITH
    ROOTS(PROJECT_ID, ELEMENT_ID) AS (
     --SELECT PROJECT_ID, DATA_IDENTITY FROM ELEMENTS_BY_PROJECT_V -- Causes crash
     --SELECT PROJECT_ID, DATA_IDENTITY FROM DATA                  -- Works fine
    ),
    HIERARCHY(ROOT_PROJECT_ID, ROOT_ID, ELEMENT_ID, LVL) AS (...),
    ELEMENT_DATA(ELEMENT_ID, NAME, TYPE) AS (...),
    IN_PACKAGES(ROOT_PROJECT_ID, ROOT_ID, PACKAGE_NAMES, PACKAGE_IDS) AS (...)
SELECT * FROM IN_PACKAGES WHERE IN_PACKAGES.PROJECT_ID='123' AND IN_PACKAGES.ROOT_ID='abc'

In the second query:

If I use SELECT PROJECT_ID, DATA_IDENTITY FROM ELEMENTS_BY_PROJECT_V, the session errors as described above.
If I select from the table directly with SELECT PROJECT_ID, DATA_IDENTITY FROM DATA, the CTE executes fine.

This makes me think that there's an issue with calling a CTE from within another CTE. I know you can't do that explicitly, but if it's done through a view I thought that be acceptable. However, I've also tried combining both queries into a single CTE like this and that still has the session crash issue.

CREATE OR REPLACE VIEW COMBINED_V AS
WITH 
    HISTORY(PROJECT_ID, COMMIT_ID, PREVIOUS_ID, LVL) AS (...),
    ELEMENT_DATA(PROJECT_ID, COMMIT_ID, DATA_IDENTITY, E_DATA, LVL) AS (...),
    LATEST_VERSIONS(LVL, DATA_IDENTITY_ID) AS (...)
    ROOTS(PROJECT_ID, ELEMENT_ID) AS (
      SELECT D.PROJECT_ID, D.DATA_IDENTITY FROM LATEST_VERSIONS V, ELEMENT_DATA D WHERE V.LVL=D.LVL AND V.DATA_IDENTITY=D.DATA_IDENTITY
    ),
    HIERARCHY(ROOT_PROJECT_ID, ROOT_ID, ELEMENT_ID, LVL) AS (...),
    ELEMENT_DATA(ELEMENT_ID, NAME, TYPE) AS (...),
    IN_PACKAGES(ROOT_PROJECT_ID, ROOT_ID, PACKAGE_NAMES, PACKAGE_IDS) AS (...)
SELECT * FROM IN_PACKAGES WHERE IN_PACKAGES.PROJECT_ID='123' AND IN_PACKAGES.ROOT_ID='abc'

Any idea why I can't create or execute the second view?

CodePudding user response：

There are plenty records about this 'crash' (you named it perfectly) like:

A problem with networking.
Wrong JDBC driver version.
Some issues with a special data structure
Database bug
and some explanations like:
"This error indicates an abort of a process, and the first step in resolving this error is to look in the alert log and look for a trace file. The "no more data to read from socket" is not a JDBC or an Oracle error. Rather, it is a generic communications error, often as the result of a TNS connectivity issue"

Some people clame they resolved it by increasing initialSize and maxActive size of connection pool.

I experienced it using the view through a double dblink - when it was changed on the other side (or simply mulfunctioning - some strange issue with null value of a number column) instead of message about invalidity got this error. Never found out what exactly happened. Recompiling on all sides made it gone.
Try to check/change a few things:
First check that all the objects involved are valid (I don't know, but maybe you are using some dblinks or packages or some other views or ... ...)

Select OWNER, OBJECT_TYPE, OBJECT_NAME
From   dba_objects
Where  STATUS != 'VALID'
Order By OWNER, OBJECT_TYPE;

Second - move your CTE column aliasing into the CTE Select clause as there are some issues (some would say bugs) when defining aliases outside, especialy if it is done multiple times and with the aliases repeating in different CTEs

-- Instead of
WITH some_cte (A_NMBR, A_LTTR) AS (Select 1, 'A' From Dual)
-- Put it this way
WITH some_cte AS (Select 1 "A_NMBR", 'A' "A_LTTR" From Dual)

I realy do hope that it will help...

CodePudding user response：

Its' likely an ORACLE bug not warning you that your HIERARCHY_BY_ELEMENT_V references 2 different CTE with the same name: ELEMENT_DATA is defined in HIERARCHY_BY_ELEMENT_V itself but also in ELEMENTS_BY_PROJECT_V which is used by HIERARCHY_BY_ELEMENT_V.