-- The maximum string length in standard Oracle installation is 4000 characters. -- When procedures have VARCHAR2 as input datatype, to avoid failure typically -- SUBSTR(p_string,1,4000) will be used. This can result in broken string. -- When used as parameter in dynamic sql, it will result in sql error. The sql -- below can avoid the error with loss of one value (last) and can be error -- message can be output saying that the input parameter has been trimmed to -- be less than are equal to 4000. -- Sample sql shows how to trim the string -- SUBSTR('''123'',''345'',''567'',''789''',1,15) -- results in -- '123','345','56 -- The resulting string has to be revised using SUBSTR and INSTR functions -- to make it '123','345' -- for dynamic sql query to work -- Using DBMS_ASSERT.ENQUOTE_NAME(str) can be used, but will make it -- '123','345','56' -- If the string length is already 4000, DBMS_ASSERT.ENQUOTE_NAME(str) -- will make it 4001 chanracters and will result in error. SELECT qtab.str, qtab.bad_str, INSTR(qtab.bad_str,',''',-1) error_loc, SUBSTR(qtab.bad_str,1,INSTR(qtab.bad_str,',''',-1)-1) trimmed_val FROM (SELECT '''123'',''345'',''567'',''789''' str, SUBSTR('''123'',''345'',''567'',''789''',1,15) bad_str FROM DUAL ) qtab ;
57563