1

Topic: ORA-01489 At the recursive request by means of WITH expressions

Kind time of days.
Help to understand with an error (or as it to bypass) ORA-01489 at performance of following request:

 SQL> with temp (lvl, str) as (
2 select 1 as lvl, ' a ' from dual
3 union all
4 select s.lvl + 1, s.str || ' a ' from dual join temp s on (s.lvl <4))
5 select * from temp;
ERROR:
ORA-01489: result of string concatenation is too long 

And if to make s.lvl <3 that works normally:

 SQL> with temp (lvl, str) as (
2 select 1 as lvl, ' a ' from dual
3 union all
4 select s.lvl + 1, s.str || ' a ' from dual join temp s on (s.lvl <3))
5 select * from temp;
1 a
2 aa
3 aaa 

If to make coercion to CLOB it is even better":

 SQL> with temp (lvl, str) as (
2 select 1 as lvl, to_clob (' a ') from dual
3 union all
4 select s.lvl + 1, s.str || to_clob (' a ') from dual
5 join temp s on (s.lvl <4))
6 select * from temp;
select * from temp
*
ERROR at line 6:
ORA-00600: internal error code, arguments: [qctfrc: bfc], [4000], [0], [1],
[171], [1], [2], [394], [], [], [], [] 

Actually - such problem. It is necessary to make the recursive request by means of WITH so connect by - not output since for the decision of the task necessary for me in WITH I can adjust better a condition, limiting sampling and accordingly to accelerate request.
The basis version:

 SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production 

2

Re: ORA-01489 At the recursive request by means of WITH expressions

Add explicit cast to VARCHAR2 (N). Why does not work without it - not clearly.
+[spoiler]

 SQL> ed
Wrote file afiedt.buf
1 with temp (lvl, str) as (
2 select 1 as lvl, cast (' a ' as varchar2 (1)) from dual
3 union all
4 select s.lvl + 1, s.str || ' a ' from dual join temp s on (s.lvl <4))
5* select * from temp
SQL> /
ERROR:
ORA-01489: result of string concatenation is too long
no rows selected 
 PARSING IN CURSOR #182935700784 len=154 dep=0 uid=62 oct=3 lid=62 tim=1297064034054094 hv=587913525 ad = ' 15d5972b8 ' sqlid ='fkp6fdnjhpq9p'
with temp (lvl, str) as (
select 1 as lvl, ' a ' from dual
union all
select s.lvl + 1, s.str || ' a ' from dual join temp s on (s.lvl <4))
select * from temp
END OF STMT
PARSE #182935700784:c=2000,e=18591,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1909448105,tim=1297064034054090
EXEC #182935700784:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1909448105,tim=1297064034054325
WAIT #182935700784: nam ='SQL*Net message to client ' ela = 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1297064034054376
FETCH #182935700784:c=1000,e=92,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1909448105,tim=1297064034054511
WAIT #182935700784: nam ='SQL*Net message from client ' ela = 137821 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1297064034192392
WAIT #182935700784: nam ='SQL*Net message to client ' ela = 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1297064034192561
FETCH #182935700784:c=0,e=272,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=1909448105,tim=1297064034192733
STAT #182935700784 id=1 cnt=3 pid=0 pos=1 obj=0 op ='VIEW (cr=0 pr=0 pw=0 time=92 us cost=6 size=32 card=2)'
STAT #182935700784 id=2 cnt=3 pid=1 pos=1 obj=0 op ='UNION ALL (RECURSIVE WITH) BREADTH FIRST (cr=0 pr=0 pw=0 time=81 us)'
STAT #182935700784 id=3 cnt=1 pid=2 pos=1 obj=0 op ='FAST DUAL (cr=0 pr=0 pw=0 time=7 us cost=2 size=0 card=1)'
STAT #182935700784 id=4 cnt=3 pid=2 pos=2 obj=0 op ='NESTED LOOPS (cr=0 pr=0 pw=0 time=110 us cost=4 size=17 card=1)'
STAT #182935700784 id=5 cnt=3 pid=4 pos=1 obj=0 op ='FAST DUAL (cr=0 pr=0 pw=0 time=25 us cost=2 size=0 card=1)'
STAT #182935700784 id=6 cnt=3 pid=4 pos=2 obj=0 op ='RECURSIVE WITH PUMP (cr=0 pr=0 pw=0 time=32 us)'
WAIT #182935700784: nam ='SQL*Net break/reset to client ' ela = 6 driver id=1413697536 break? =1 p3=0 obj#=-1 tim=1297064034192971
WAIT #182935700784: nam ='SQL*Net break/reset to client ' ela = 135721 driver id=1413697536 break? =0 p3=0 obj#=-1 tim=1297064034328710
 SQL> ed
Wrote file afiedt.buf
1 with temp (lvl, str) as (
2 select 1 as lvl, cast (' a ' as varchar2 (4000)) from dual
3 union all
4 select s.lvl + 1, cast (s.str || ' a ' as varchar2 (4000)) from dual join temp s on (s.lvl <4))
5* select * from temp
SQL> /
LVL STR
-------------------- ------------------------------
1 a
2 aa
3 aaa
4 aaaa 

[/spoiler]

3

Re: ORA-01489 At the recursive request by means of WITH expressions

Timur Akhmadeev;
Thanks big. The problem is solved.

4

Re: ORA-01489 At the recursive request by means of WITH expressions

Timur Akhmadeev wrote:

Add explicit cast to VARCHAR2 (N). Why does not work without it - not clearly.

, because was (and remained)    terribly far from the people smile))
From this that enough the first cast it is possible to guess a place where that bug grows
+[spoiler]

 It is connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Real Application Testing options
SQL> with temp (lvl, str) as (
2 select 1 as lvl, cast (' a ' as varchar2 (1)) from dual
3 union all
4 select s.lvl + 1, s.str || ' a'
5 from dual join temp s on (s.lvl <4))
6 select * from temp;
ERROR:
ORA-01489: the result of string concatenation is too great
Lines are not selected
SQL>
SQL> with temp (lvl, str) as (
2 select 1 as lvl, cast (' a ' as varchar2 (10)) from dual
3 union all
4 select s.lvl + 1, s.str || ' a'
5 from dual join temp s on (s.lvl <4))
6 select * from temp;
LVL STR
---------- ------------
1 a
2 aa
3 aaa
4 aaaa 

[/spoiler]
And, generally, the test case is similar

 SQL> with temp (lvl, str) as (
2 select level as lvl, lpad (' a ', level, ' a ') from dual
3 connect by level <= 4)
4 select * from temp; 

To within digit capacity of a wooden column
(Well and with that difference that the old stylish piece of wood does not fall)