-- 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
;