Error : A column or character expression is larger than the max size.

Database
Enthusiast

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

7 REPLIES
Enthusiast

Re: Error : A column or character expression is larger than the max size.

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

Enthusiast

Re: Error : A column or character expression is larger than the max size.

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

Enthusiast

Re: Error : A column or character expression is larger than the max size.

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

Enthusiast

Re: Error : A column or character expression is larger than the max size.

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

Enthusiast

Re: Error : A column or character expression is larger than the max size.

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.

Enthusiast

Re: Error : A column or character expression is larger than the max size.

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

Enthusiast

Re: Error : A column or character expression is larger than the max size.

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 :) .