Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01486 (Oracle 9i)
ORA-01486 [message #330645] Mon, 30 June 2008 13:03
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm trying to use the SYS_CONNECT_BY_PATH function and keep hitting the 4000 byte limit with the "ORA-01489 result of string concatenation is too long" error.

Is it possible to divide the below query into multiple queries limiting what each query concatenates through the WHERE clause and the CNT & RN variables, then concatenating them together?

I'm not sure if this is possible, and haven't been able to come up with logic that will accomplish this.

Any help would be greatly appreciated.

(SELECT
      patseq,
      LTRIM(SYS_CONNECT_BY_PATH(BRIC,' 
'),' 
') BRIC
   FROM
      (SELECT DISTINCT
         p.patseq,
         DDT.TOCHAR(perform,'MM/DD HH24:MI') || ' - ' || pcql.label || '-' || FULLRES(pr.resultV) BRIC,
         ROW_NUMBER() OVER (PARTITION BY p.patseq ORDER BY pr.perform) RN,
         COUNT(*) OVER (PARTITION BY p.patseq) AS CNT
      FROM
         pat p,
         patr pr,
         pcql
      WHERE
         p.patseq = pr.patseq  AND
         pr.label = pcql.label AND
         pr.app = 'L'  AND
         pr.labelseq IN (511,512,513,514,516,517) AND
         pr.perform BETWEEN DDT.FROMDATE(SYSDATE-1) AND DDT.FROMDATE(SYSDATE) AND
         p.patseq IN (4905,7742)
      )
   WHERE
      LEVEL = CNT START WITH RN = 1 CONNECT BY PRIOR patseq = patseq AND PRIOR RN = RN-1)


Thanks,
Stan
Previous Topic: Select statement to return values within range
Next Topic: Scheduling stored procedure (merged)
Goto Forum:
  


Current Time: Sat Dec 03 09:54:18 CST 2016

Total time taken to generate the page: 0.10493 seconds