使用SQL Plus执行INSERT语句时遇ORA-01741错误求助

错误原因 #

ORA-01741错误触发的核心是SQL语句中存在无效的零长度标识符，结合你的INSERT语句，问题出在两处：

• 单引号未正确转义：REQ_COMPLETE字段的嵌套SELECT语句中，外层单引号与内层单引号直接冲突，导致Oracle解析时识别出无效的空标识符边界。
• 值的引号误用：语句中b.actif="O"（实际应为b.actif='O'）用双引号包裹值，Oracle会将"O"当作标识符而非字符串值，进而触发错误。

修正后的SQL语句 #

以下是修正后的完整语句（保留所有原有逗号，仅修正引号问题）：

INSERT INTO FCCR_DATA_MAPPING(ID_API,DATA,COLUMN_NAME,TABLE_NAME,CONDITION,REQ_COMPLETE,REQ_COMPLETE_EXT) 
VALUES(
    'compute',
    'customer-rating-rawData-ultimateBeneficialOwners',
    null,
    null,
    null,
    'SELECT ''{ "firstName": "XXXX"'' ||'', "lastName": "XXXX"'' ||'', "birthDate": "'' || CASE b.typ 
        WHEN ''C'' THEN (SELECT to_char(c3.dna,''yyyy-mm-dd'') FROM bkcli c3 WHERE c3.cli=b.beneff) 
        WHEN ''T'' THEN (SELECT to_char(t3.dna,''yyyy-mm-dd'') FROM bktier t3 WHERE t3.ctie=b.beneff) 
    END || ''"'' ||'', "residenceCountryCode": "'' || CASE b.typ 
        WHEN ''C'' THEN (SELECT trim(nc4.lib2) FROM bkcli c4, bknom nc4 WHERE c4.cli=b.beneff AND nc4.cacc=c4.res AND nc4.ctab=''040'') 
        WHEN ''T'' THEN (SELECT trim(nt4.lib2) FROM bktier t4, bknom nt4 WHERE t4.ctie=b.beneff AND nt4.cacc=t4.res AND nt4.ctab=''040'') 
    END || ''"'' ||'', "localResidenceCountryCode": ""'' ||'', "birthCountryCode": "'' || CASE b.typ 
        WHEN ''C'' THEN (SELECT trim(nc6.lib2) FROM bkcli c6, bknom nc6 WHERE c6.cli=b.beneff AND nc6.cacc=c6.payn AND nc6.ctab=''040'') 
        WHEN ''T'' THEN (SELECT trim(nt6.lib2) FROM bktier t6, bknom nt6 WHERE t6.ctie=b.beneff AND nt6.cacc=t6.payn AND nt6.ctab=''040'') 
    END || ''"'' ||'', "gender": "'' || CASE b.typ 
        WHEN ''C'' THEN (CASE (SELECT trim(c7.sext) FROM bkcli c7 WHERE c7.cli=b.beneff) WHEN ''M'' THEN ''MALE'' WHEN ''F'' THEN ''FEMALE'' ELSE ''INDEFINITE'' END) 
        WHEN ''T'' THEN (CASE (SELECT trim(t7.sext) FROM bktier t7 WHERE t7.ctie=b.beneff) WHEN ''M'' THEN ''MALE'' WHEN ''F'' THEN ''FEMALE'' ELSE ''INDEFINITE'' END) 
    END || ''"'' ||'', "localBirthCountryCode": ""'' ||'' }'' FROM abbeneff b WHERE b.actif=''O'' and b.cli=?',
    null
);
COMMIT;

关键修正说明 #

1. 单引号转义：所有嵌套在字符串内的单引号替换为两个连续单引号（''），这是Oracle中表示单个单引号的标准方式，确保解析器能正确识别字符串边界。
2. 值引号修正：将条件中的b.actif="O"改为b.actif=''O''，用单引号包裹字符串值，避免Oracle将其误判为标识符。

内容的提问来源于stack exchange，提问作者user26656363