sql - ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4448, maximum: 4000)

Question

I am trying to Migrate client's data and this error happened during the migration procedure. I have searched around and the possible solution is to trim or use DBMS_LOB.SUBSTR, but i do not know where to put them in. It could be this part (TO_CHAR(AIMSCMDDL_AT.BIOS_SEQ.nextval) but i am still new to SQL and not sure what could be the cause of the problem. Can anyone guide me on how to solve this problem? Thank you!

Update
       AIMSCMDDL_AT.AI_OPENNET_SVC_RPT
SET
       CUST_AGREEMENT_SIGNATURE = 
           CASE
                  WHEN TO_CHAR(CUST_AGREEMENT_SIGNATURE) = null
                        THEN null
                        ELSE 'NULL'
           END
     , CUST_DECLARATION_AUTH_ID = 
           CASE
                  WHEN CUST_DECLARATION_AUTH_ID = null
                        THEN null
                        ELSE 
                            CASE WHEN LENGTH(CUST_DECLARATION_AUTH_ID)<>9
                                THEN TO_CHAR(AIMSCMDDL_AT.BIOS_SEQ.nextval)
                                ELSE 
                                    CASE WHEN LENGTH(CUST_DECLARATION_AUTH_ID)=9
                                                and REGEXP_LIKE(substr(CUST_DECLARATION_AUTH_ID,1,1),'[A-Za-z]') 
                                                and REGEXP_LIKE(substr(CUST_DECLARATION_AUTH_ID,-1,1), '[A-Za-z]') 
                                                and  AIMSCMDDL_AT.is_number(substr(CUST_DECLARATION_AUTH_ID,2,7)) = 1                                       
                                         THEN substr(CUST_DECLARATION_AUTH_ID,1,1)||LPAD(TO_CHAR(AIMSCMDDL_AT.BIOS_SEQ.nextval),7,'0')||substr(CUST_DECLARATION_AUTH_ID,-1,1)
                                         ELSE TO_CHAR(AIMSCMDDL_AT.BIOS_SEQ.nextval)
                                    END
                            END         
            END
     , CUST_DECLARATION_AUTH_NM = 
           CASE
                  WHEN CUST_DECLARATION_AUTH_NM = null
                        THEN null
                        ELSE REGEXP_REPLACE(CUST_DECLARATION_AUTH_NM,'[[:alpha:]^[:digit:]^[:punct:]^]','*')
           END
     , CUSTOMER_SIGNATURE = 
           CASE
                  WHEN TO_CHAR(CUSTOMER_SIGNATURE) = null
                        THEN null
                        ELSE 'NULL'
           END
     , INSTALLER_NM = 
           CASE
                  WHEN INSTALLER_NM = null
                        THEN null
                        ELSE REGEXP_REPLACE(INSTALLER_NM,'[[:alpha:]^[:digit:]^[:punct:]^]','*')
           END
     , INSTALLER_SIGNATURE = 
           CASE
                  WHEN TO_CHAR(INSTALLER_SIGNATURE) = null
                        THEN null
                        ELSE 'NULL'
           END
     , REJ_CUSTOMER_ID = 
           CASE
                  WHEN REJ_CUSTOMER_ID = null
                        THEN null
                        ELSE 
                            CASE WHEN LENGTH(REJ_CUSTOMER_ID)<>9
                                THEN TO_CHAR(AIMSCMDDL_AT.BIOS_SEQ.nextval)
                                ELSE 
                                    CASE WHEN LENGTH(REJ_CUSTOMER_ID)=9
                                                and REGEXP_LIKE(substr(REJ_CUSTOMER_ID,1,1),'[A-Za-z]') 
                                                and REGEXP_LIKE(substr(REJ_CUSTOMER_ID,-1,1), '[A-Za-z]') 
                                                and  AIMSCMDDL_AT.is_number(substr(REJ_CUSTOMER_ID,2,7)) = 1                                        
                                         THEN substr(REJ_CUSTOMER_ID,1,1)||LPAD(TO_CHAR(AIMSCMDDL_AT.BIOS_SEQ.nextval),7,'0')||substr(REJ_CUSTOMER_ID,-1,1)
                                         ELSE TO_CHAR(AIMSCMDDL_AT.BIOS_SEQ.nextval)
                                    END
                            END         
            END
     , REJ_CUSTOMER_NM = 
           CASE
                  WHEN REJ_CUSTOMER_NM = null
                        THEN null
                        ELSE REGEXP_REPLACE(REJ_CUSTOMER_NM,'[[:alpha:]^[:digit:]^[:punct:]^]','*')
           END
     , REJ_CUSTOMER_SIGNATURE = 
           CASE
                  WHEN TO_CHAR(REJ_CUSTOMER_SIGNATURE) = null
                        THEN null
                        ELSE 'NULL'
           END
     , REJ_INSTALLER_NM = 
           CASE
                  WHEN REJ_INSTALLER_NM = null
                        THEN null
                        ELSE REGEXP_REPLACE(REJ_INSTALLER_NM,'[[:alpha:]^[:digit:]^[:punct:]^]','*')
           END
     , REJ_INSTALLER_SIGNATURE = 
           CASE
                  WHEN TO_CHAR(REJ_INSTALLER_SIGNATURE) = null
                        THEN null
                        ELSE 'NULL'
           END             
         ;

标签： sqloraclestored-procedures

Answer 1

它很可能来自以下表达式之一：

CASE
    WHEN TO_CHAR(INSTALLER_SIGNATURE) = null
    THEN null
    ELSE 'NULL'
END

首先，它们不起作用。... = NULL 永远不会产生 TRUE。而是IS NULL。无需使用TO_CHAR(),IS NULL适用于任何数据类型。

你可以把它写得更短

INSTALLER_SIGNATURE = NVL2(INSTALLER_SIGNATURE, 'NULL', NULL)

注意，

REGEXP_LIKE(substr(CUST_DECLARATION_AUTH_ID,1,1),'[A-Za-z]') 
and REGEXP_LIKE(substr(CUST_DECLARATION_AUTH_ID,-1,1), '[A-Za-z]') 

可以写成

REGEXP_LIKE(CUST_DECLARATION_AUTH_ID,'^[A-Za-z].*[A-Za-z]$')