My Oracle Support Banner

Getting ORA-02016: cannot Subquery In A START WITH On A Remote Database after upgraded To 12C (Doc ID 2261217.1)

Last updated on MARCH 22, 2019

Applies to:

Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

Insert as select query making use of a heirarchical query can fail with ORA-2016 when there is a database link involved.


ORA-02016: cannot use a subquery in a START WITH on a remote database

.


 INSERT INTO <> (col1,col2,col3,col4,col5,col6,col7,col8,col9)
SELECT col1,
NAME,
NULLIF(col1, 0),
NULL AS col2,
'value' AS col3, col4,col5,
CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE),
CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE),
(SELECT ID FROM table-name1 WHERE LOWER(SSO_ID) ='value-name'),
(SELECT ID FROM table-name1 WHERE LOWER(SSO_ID) ='value-name')
FROM <>
WHERE ID NOT IN (SELECT col1 FROM col)
CONNECT BY PRIOR ID = PARENT_ID START WITH PARENT_ID = 0
ORDER BY LEVEL ASC;
INSERT INTO <> (col1,col2,col3,col4,col5,col6,col7,col8,col9)
*
ERROR at line 1:
ORA-02016: cannot use a subquery in a START WITH on a remote database

Changes

 Upgraded the Database from 11G to 12C

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.