ORA-02016: Cannot use a sub-query in START WITH on a remote database after database upgraded To 12c (Doc ID 2266473.1)

Last updated on MAY 17, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

After  upgrading the database from 11g to 12c, following fails:
 
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 sub-query in a START WITH on a remote database

Changes

 Upgraded database from 11g to 12.1.0.2

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