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))
/* 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
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,
REQUESTTXTOVERFLOW IS NULL THEN
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
SELECT COUNT(1) + 1 INTO CNT FROM LOOKUPDB.LOOKUP_SWAP_TBL
WHERE ACTIVE_INDICATOR = 'Y'
IF CNT <> 0 THEN
FETCH SWAPVW INTO v_Rep_View,v_Etl_View,v_ETL_DB_NM,v_ETL_VIEW_NM, v_ETL_REQUESTTEXT;
/* 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 */
IF INCRE =CNT THEN
/* Closing Cursor */
This could be the reason. You have used the table name wrongly for RequestText in the below function. Can you change and try?
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.
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
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 ?
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.
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 ?
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 :) .