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

Last updated on AUGUST 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 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 PERMISSION (ID, NAME, PARENT_PERMISSION_ID, APPLICATION_ID, PERMISSION_TYPE_ID, DESCRIPTION, EXPLICIT, CREATED_DATE, MODIFIED_DATE, CREATED_BY, MODIFIED_BY)
SELECT ID,
NAME,
NULLIF(PARENT_ID, 0),
NULL AS APPLICATION_ID,
'APPL' AS PERMISSION_TYPE_ID, DESCRIPTION, EXPLICIT,
CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE),
CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE),
(SELECT ID FROM CEA_USER WHERE LOWER(SSO_ID) ='gfitsynchroniser'),
(SELECT ID FROM CEA_USER WHERE LOWER(SSO_ID) ='gfitsynchroniser')
FROM GFIT_CFG_PERM
WHERE ID NOT IN (SELECT ID FROM PERMISSION)
CONNECT BY PRIOR ID = PARENT_ID START WITH PARENT_ID = 0
ORDER BY LEVEL ASC;
INSERT INTO PERMISSION (ID, NAME, PARENT_PERMISSION_ID, APPLICATION_ID, PERMISSION_TYPE_ID, DESCRIPTION, EXPLICIT, CREATED_DATE, MODIFIED_DATE, CREATED_BY, MODIFIED_BY)
*
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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms