Client Support
Showing results for 
Search instead for 
Do you mean 

ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?

SOLVED
Go to Solution
Valued Contributor

ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?

Hi All,

 

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?

 

Your assistance is much appreciated.

 

Cheers!

 

4 REPLIES
Honored Contributor

Re: ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?

That was fixed a long time ago, no need to manually alter the TRIMPACKAGE any more.



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

Kapish.com.au
HPE Expert

Re: ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?

Hi Yuri,

 

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 )

AS

BEGIN

OPEN Locs FOR SELECT DISTINCT lrLocUri2 FROM TSLocRel WHERE

lrType = lr_type START WITH lrLocUri1 = uri0 CONNECT BY NOCYCLE PRIOR

lrLocUri2 = lrLocUri1;

END ConstructLocationsRecursive;


Neil

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 TRIM/RM/CM you're using in all posts. At any given time, HPE are supporting approx. 30+ released versions)

HPE Software Support Online (SSO): https://softwaresupport.hpe.com/
Valued Contributor

Re: ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?

Thanks Grundy. One more reason why I should upgrade to 7.1. Cheers.
Valued Contributor

Re: ORA-01436: CONNECT BY loop in user data - Fixed in 7.1?

Thanks Neil. Grundy confirmed that this bug has been fixed. Cheers.