All Forums Database
Karthik_B 10 posts Joined 09/14
24 Oct 2014
Error : A column or character expression is larger than the max size.

Hi,
 
I am running this code, but I keep on getting this error , if I do not cast it to CLOB , I get data row length exceeded, any suggestions ?
 

REPLACE PROCEDURE PROCDB.SWAP_TEST(VW_TYPE VARCHAR(30))
BEGIN
/*  Declaration for the Variables */
                DECLARE v_replace_etl_view CLOB ;
                DECLARE v_ETL_REQUESTTEXT CLOB;
                DECLARE v_ETL_DB_NM VARCHAR(30);
                DECLARE v_ETL_VIEW_NM VARCHAR(30);
                DECLARE v_REP_VIEW_NM VARCHAR(30);
                DECLARE v_Rep_View VARCHAR(61);
                DECLARE v_Etl_View VARCHAR(61);
                DECLARE CNT INTEGER;
                DECLARE CNTETL INTEGER DEFAULT 0;
                DECLARE INCRE INTEGER DEFAULT 0;
                DECLARE  SWAPVW CURSOR FOR
 
SELECT A.Rep_View,A.Etl_View,
TRIM(SUBSTR(A.REP_VIEW,1,POSITION('.' IN A.REP_VIEW) -1 )) AS ETL_DB_NM,
TRIM(SUBSTR(A.ETL_VIEW,POSITION('.' IN A.ETL_VIEW ) + 1 , 61)) AS ETL_VIEW_NM,
CASE WHEN
REQUESTTXTOVERFLOW IS NULL THEN
REGEXP_REPLACE(B.REQUESTTEXT,A.ETL_VIEW,A.REP_VIEW,1,1 ,'i')
ELSE
REGEXP_REPLACE((C.REQUESTTEXT(CLOB)) ,A.ETL_VIEW,A.REP_VIEW,1,1,'i') END AS ETL_REQUESTTEXT
FROM LOOKUPDB.LOOKUP_SWAP_TBL A
INNER JOIN  DBC.TABLES B
ON B.DATABASENAME = ETL_DB_NM
AND ETL_VIEW_NM = B.TABLENAME
LEFT OUTER JOIN DBC.TABLETEXT C
ON C.DATABASENAME = B.DATABASENAME
AND C.TABLENAME = B.TABLENAME
WHERE  ACTIVE_INDICATOR = 'Y' ;
 
IF VW_TYPE='ETL' THEN
SET CNT=0;
SET INCRE=0;
SELECT COUNT(1) + 1 INTO CNT FROM  LOOKUPDB.LOOKUP_SWAP_TBL
WHERE ACTIVE_INDICATOR = 'Y'
 
IF CNT <> 0 THEN
OPEN SWAPVW;
Label1:
LOOP
 
FETCH SWAPVW INTO   v_Rep_View,v_Etl_View,v_ETL_DB_NM,v_ETL_VIEW_NM, v_ETL_REQUESTTEXT;
SET v_replace_etl_view=v_ETL_REQUESTTEXT;
                                                                CALL DBC.SysExecSQL(v_replace_etl_view);
 
/* This  variable is to get the number of times the loop is running.If this value equals to Cursor Count it will stop the swap process */
SET INCRE=INCRE+1;
IF INCRE =CNT THEN
LEAVE Label1;
END IF;
END LOOP;
/* Closing Cursor */
CLOSE SWAPVW;
END IF;
END IF;
END;

Thanks,
Karthik

ravimans 54 posts Joined 02/14
29 Oct 2014

Hi Karthik,
This could be the reason. You have used the table name wrongly for RequestText in the below function. Can you change and try?

REGEXP_REPLACE(B.REQUESTTEXT,A.ETL_VIEW,A.REP_VIEW,1,1 ,'i')

ELSE

REGEXP_REPLACE((C.REQUESTTEXT(CLOB)) ,A.ETL_VIEW,A.REP_VIEW,1,1,'i') END AS ETL_REQUESTTEXT

Karthik_B 10 posts Joined 09/14
01 Nov 2014

Thank you Ravi, Its not specific to this, I tried creating a view and comment at the end of the view which has about 32000 characters. I then tried to user REGEXP_REPLACE & OREPLACE function , but it still shows the same error
 
select REGEXP_REPLACE( REQUESTTEXT,'TEST','HELLO',1,1,I) FROM DBC.TABLETEXT WHERE TABLENAME ='XYZ'
This still gives me the error "the data length size exceeded " or the maximum output argument size exceeded.
I even tried casting it to clob, converting it to Latin, but nothing I have tried works.
 
--Karthik

vikas_yadav 19 posts Joined 09/12
02 Nov 2014
Maximum output length of regexp_replace is exceeding.
select TYPE(REGEXP_REPLACE( 'REQUESTTEXT','TEST','HELLO',1,1,'I') )

 o/P: VARCHAR(8000) CHARACTER SET UNICODE

Karthik_B 10 posts Joined 09/14
02 Nov 2014

Thank you Vikas, that explains why I am getting the error, but what is the alternative If I want to perform string manipulations in columns greater than varchar 8000 ?
--Karthik

Raja_KT 1246 posts Joined 07/09
02 Nov 2014

Maybe you can try with c,c++, java udfs and see. I have never crossed beyond 200 , while writing udfs. You  can share your experience then.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Karthik_B 10 posts Joined 09/14
03 Nov 2014

Ah, I was hoping there was a way to do it in SQL itself, I guess I will probably export the results in BTEQ, replace them using shell, and import them back in . But shouldn't there be a simpler way  ?
 
--Karthik

Raja_KT 1246 posts Joined 07/09
03 Nov 2014

I love Unix/Linux scripting. For big ones, I would love to get to unix scripting. It is more handy in certain cases.  Automation works I prefer  unix scripting :) .

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.