Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01489: result of string concatenation is too long (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit, Microsoft Windows x86 64-bit)
| ORA-01489: result of string concatenation is too long [message #518961] |
Mon, 08 August 2011 09:27  |
kytemanaic
Messages: 55
Registered: February 2009
|
Member |
|
|
Hi
my rdbms, os version
SYS@prod> select PLATFORM_ID, PLATFORM_NAME from v$database;
PLATFORM_ID
-----------
PLATFORM_NAME
-------------------------------------------------------------------------
12
Microsoft Windows x86 64-bit
SYS@prod> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
create script for first view
CREATE OR REPLACE FORCE VIEW "BMOBILE"."FIBRE_CABLE_LINK" ("G3E_ID", "G3E_FNO", "G3E_FID",
"G3E_CNO", "G3E_CID", "ALPHA_CODE", "ALTERNATE_CABLE_NAME", "ARMOUR", "BEGINNING_TYPE",
"BLOWN_FIBRE", "CABLE_LENGTH", "CABLE_NO", "CABLE_TYPE", "COPPER_SIZE", "CREATED_BY",
"CREATED_DATE", "DESIGN_TYPE", "DIAMETER", "FIBER_MODE", "FIBER_SIZE", "FIBRE_SIZE",
"FIBRE_TYPE", "FTTH_PERIOD", "GAUGE", "GROUP_ID", "HI_PR", "ISDATACLEANING", "LENGTH", "LO_PR",
"MIDSPAN", "MLS_TYPE", "MODIFIED_BY", "MODIFIED_DATE", "NRS_FLAG", "ON_WALL_LENGTH", "OWNERSHIP", "PARENT_ID", "PRIMARY_TUBE"
, "REMARKS", "ROUTE_DETAIL", "SECTION", "SHEATH", "SOURCE", "SOURCE_ID", "TAG_ID",
"TERMINATING_ID", "TERMINATION_TYPE", "TOLERANCE_END", "TOLERANCE_START", "TOTAL_LENGTH", "TOTAL_SIZE", "USAGE", "WALL_PIPE") AS
(select /*+ INDEX(A) */ A.G3E_ID,A.G3E_FNO,A.G3E_FID,A.G3E_CNO,A.G3E_CID,A.ALPHA_CODE,
A.ALTERNATE_CABLE_NAME,A.ARMOUR,A.BEGINNING_TYPE,A.BLOWN_FIBRE,A.CABLE_LENGTH,
A.CABLE_NO,A.CABLE_TYPE,A.COPPER_SIZE,A.CREATED_BY,A.CREATED_DATE,A.DESIGN_TYPE,
A.DIAMETER,A.FIBER_MODE,A.FIBER_SIZE,A.FIBRE_SIZE,A.FIBRE_TYPE,A.FTTH_PERIOD,
A.GAUGE,A.GROUP_ID,A.HI_PR,A.ISDATACLEANING,A.LENGTH,A.LO_PR,A.MIDSPAN,A.MLS_TYPE,
A.MODIFIED_BY,A.MODIFIED_DATE,A.NRS_FLAG,A.ON_WALL_LENGTH,A.OWNERSHIP,A.PARENT_ID,
A.PRIMARY_TUBE,A.REMARKS,A.ROUTE_DETAIL,A.SECTION,A.SHEATH,A.SOURCE,A.SOURCE_ID,
A.TAG_ID,A.TERMINATING_ID,A.TERMINATION_TYPE,A.TOLERANCE_END,A.TOLERANCE_START,
A.TOTAL_LENGTH,A.TOTAL_SIZE,A.USAGE,A.WALL_PIPE FROM B$FIBRE_CABLE_LINK A WHERE (
(A.LTT_STATUS is NULL) or (A.LTT_STATUS in ('COPIED', 'LOCKED') AND NOT EXISTS (
select * from B$FIBRE_CABLE_LINK B, table(cast(LTT_USER.GETLTTMAP() as tLTTID)) X wher
e B.g3e_id=A.g3e_id and B.ltt_id = x.column_value))) or (a.LTT_ID IN (select /
*+ NO_MERGE */ COLUMN_VALUE from table(cast(LTT_USER.GETLTTMAP() as tLTTID))) AN
D A.LTT_STATUS IN ( 'ADD','EDIT')));
create script for 2nd view
CREATE OR REPLACE FORCE VIEW "BMOBILE"."FIBRE_CABLE_LINK_DETAIL" ("G3E_ID", "G3E_FNO",
"G3E_FID", "G3E_CNO", "G3E_CID", "ROUTE_DETAIL", "FNO", "FID") AS
SELECT G3E_ID
, G3E_FNO
, G3E_FID
, G3E_CNO
, ROW_NUMBER () OVER (PARTITION BY g3e_fid ORDER BY g3e_fid) g3e_cid
, ROUTE_DETAIL
, SUBSTR(ROUTE_DETAIL, 1, INSTR(ROUTE_DETAIL,'-')-1) FNO
, SUBSTR(ROUTE_DETAIL, INSTR(ROUTE_DETAIL,'-')+1 ) FID
FROM (
SELECT G3E_ID, G3E_FNO , G3E_FID, G3E_CNO, G3E_CID, TRIM (EXTRACT (VALUE (d), '//row/text()').getStringVal ()) AS ROUTE_DETAIL
FROM (SELECT G3E_ID, G3E_FNO , G3E_FID, G3E_CNO, G3E_CID
, XMLTYPE('<rows><row>'
|| REPLACE (ROUTE_DETAIL
, ','
, '</row><row>')
|| '</row></rows>')
AS xmlval
FROM FIBRE_CABLE_LINK) x
, TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d );
BMOBILE@PROD> select count(0) from (select G3E_ID, count(0) from GC_FCBL_ROUTE_DETAIL group by G3E_ID having count(0)>
1);
select count(0) from (select G3E_ID, count(0) from FIBRE_CABLE_LINK_DETAIL group by G3E_ID having count(0)>1)
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
BMOBILE@PROD> select count(0) from (select G3E_ID, count(0) from FIBRE_CABLE_LINK group by G3E_ID having count(0)>1);
492
here's the describe statements
BMOBILE@PROD> desc FIBRE_CABLE_LINK_DETAIL
Name Null? Type
----------------------------------------- -------- --------------
G3E_ID NOT NULL NUMBER(10)
G3E_FNO NOT NULL NUMBER(5)
G3E_FID NOT NULL NUMBER(10)
G3E_CNO NOT NULL NUMBER(5)
G3E_CID NUMBER
ROUTE_DETAIL VARCHAR2(4000)
FNO VARCHAR2(4000)
FID VARCHAR2(4000)
BMOBILE@PROD> desc FIBRE_CABLE_LINK
Name Null? Type
----------------------------------------- -------- -----------------------
G3E_ID NOT NULL NUMBER(10)
G3E_FNO NOT NULL NUMBER(5)
G3E_FID NOT NULL NUMBER(10)
G3E_CNO NOT NULL NUMBER(5)
G3E_CID NOT NULL NUMBER(10)
ALPHA_CODE VARCHAR2(20)
ALTERNATE_CABLE_NAME VARCHAR2(30)
ARMOUR VARCHAR2(20)
BEGINNING_TYPE VARCHAR2(40)
BLOWN_FIBRE VARCHAR2(1)
CABLE_LENGTH NUMBER(22,2)
CABLE_NO VARCHAR2(30)
CABLE_TYPE VARCHAR2(40)
COPPER_SIZE NUMBER(10)
CREATED_BY VARCHAR2(30)
CREATED_DATE DATE
DESIGN_TYPE VARCHAR2(16)
DIAMETER NUMBER(10,2)
FIBER_MODE VARCHAR2(8)
FIBER_SIZE NUMBER(10)
FIBRE_SIZE NUMBER(22)
FIBRE_TYPE VARCHAR2(40)
FTTH_PERIOD CHAR(12)
GAUGE NUMBER(4)
GROUP_ID VARCHAR2(15)
HI_PR NUMBER
ISDATACLEANING NUMBER
LENGTH NUMBER(22,2)
LO_PR NUMBER
MIDSPAN VARCHAR2(1)
MLS_TYPE VARCHAR2(40)
MODIFIED_BY VARCHAR2(30)
MODIFIED_DATE DATE
NRS_FLAG VARCHAR2(1)
ON_WALL_LENGTH NUMBER(22)
OWNERSHIP VARCHAR2(255)
PARENT_ID VARCHAR2(15)
PRIMARY_TUBE NUMBER
REMARKS VARCHAR2(4000)
ROUTE_DETAIL VARCHAR2(4000)
SECTION VARCHAR2(30)
SHEATH VARCHAR2(35)
SOURCE NUMBER(22)
SOURCE_ID VARCHAR2(15)
TAG_ID VARCHAR2(24)
TERMINATING_ID VARCHAR2(15)
TERMINATION_TYPE VARCHAR2(40)
TOLERANCE_END NUMBER(22,2)
TOLERANCE_START NUMBER(22,2)
TOTAL_LENGTH NUMBER(22,2)
TOTAL_SIZE NUMBER(38)
USAGE VARCHAR2(8)
WALL_PIPE VARCHAR2(40)
as I googled the solution does not seems to apply to my case.
is there any solution to the above? it very puzzling that such a short query can produce
ORA-01489: result of string concatenation is too long
thanks a lot!
|
|
|
|
|
|
| Re: ORA-01489: result of string concatenation is too long [message #519268 is a reply to message #518966] |
Wed, 10 August 2011 11:23  |
kytemanaic
Messages: 55
Registered: February 2009
|
Member |
|
|
sorry problem statement should be
select count(0) from (select G3E_ID, count(0) from FIBRE_CABLE_LINK_DETAIL group by G3E_ID having count(0)>1)
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
problem statement should be FIBRE_CABLE_LINK_DETAIL and not GC_FCBL_ROUTE_DETAIL.
apology for the errors.
thanks
|
|
|
|
| Re: ORA-01489: result of string concatenation is too long [message #519270 is a reply to message #519268] |
Wed, 10 August 2011 11:37 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you've got a view that calls a view, that calls a function.
You need to break this down to work out which is giving the error.
Query the 1st view, see if it gives the error.
Query the 2nd view directly see if it gives the error.
Once you know which view you can start taking it apart to find the culprit.
It might be this, since it's the only obvious bit of concatenation:
, XMLTYPE('<rows><row>'
|| REPLACE (ROUTE_DETAIL
, ','
, '</row><row>')
|| '</row></rows>')
AS xmlval
Route_detail is varchar2(4000). You're making it longer with concatenation.
If the result of the concatenation exceeds 4000 characters, then you will get that error.
|
|
|
|
Goto Forum:
Current Time: Wed Jul 22 09:07:49 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
