ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?
Can anyone tell me if the "ORA-01436: CONNECT BY loop in user data" error messages have been fixed in version 7.1. I undertand that in 6.2.3 we needed to upgradet he package called 'TRIMPACKAGE', manually by deploying a script. Do we need to apply the same fix in 7.1?
Re: ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?
I've been looking through the system and can't find a definitive answer. In December 2010 they identified a missing "NOCYCLE" clause in the TRIM package that could allow this error to occur. The tech note also needed updating, but I can't find a record of that happening.
It seems the problem can be caused by illegal values in TSLOCREL. These should be cleaned up by using a query to identify offending cells, such as:
SELECT lrLocUri2 , CONNECT_BY_ISCYCLE FROM TSLocRel WHERE lrType = lr_type START WITH lrLocUri1 = uri0 CONNECT BY NOCYCLE PRIOR lrLocUri2 = lrLocUri1;
The recursive Stored Procedure statement is missing a NOCYCLE clause that prevents cycling errors
I'd like to say that because 7.1 wasn't released until Feb 2011, that this correction should have also been put into 7.1, however I can't find any record of that.
The guilty section of the TRIM package is the one below, so if you are an Oracle site getting this error in TRIM you could re-apply the TRIM package after changing the corresponding section to match what's below:
-- ConstructLocationsRecursive Definition
PROCEDURE ConstructLocationsRecursive (
lr_type IN int,
uri0 IN int,
Locs IN OUT tslocrel_recurslocs )
OPEN Locs FOR SELECT DISTINCT lrLocUri2 FROM TSLocRel WHERE
lrType = lr_type START WITH lrLocUri1 = uri0 CONNECT BY NOCYCLE PRIOR
lrLocUri2 = lrLocUri1;
Note: Any posts I make on this forum are my own personal opinion and (unless explicitly stated) do not constitute a formal commitment on behalf of HPE.
(Please state the version of CM you're using in all posts.
HPE Software Support Online (SSO): https://softwaresupport.hpe.com/