PL/SQL: ORA-12704:创建函数时字符集不匹配

Question

当试图在函数脚本下面运行时，我会出错。我曾试图在字符串之前添加N，但它不起作用。也许我做错了。

我正在尝试创建下面的函数，但是在运行它时会出错。

create or replace FUNCTION Get_Per_Address_For_Register(PERSON_CODE_PARAM IN NUMBER,
  ADDR_TYPE_PARAM IN NUMBER,
  lang IN NUMBER) RETURN VARCHAR2 
   as
   addressval  VARCHAR2(1000);
    BEGIN
    begin
      SELECT * INTO addressval FROM(SELECT  
         case when tai.ADDRESS_LINE_1 IS NULL
         or tai.ADDRESS_LINE_1 = ' '
         then ''
         -- Error block Start ------------ 
         else CONCAT(tai.ADDRESS_LINE_1,',')  end || case when
         tai.ADDRESS_LINE_2 = ' '
         or tai.ADDRESS_LINE_2 = ' '
         then ''
         else CONCAT(tai.ADDRESS_LINE_2,',')  end  || case when 
         tai.ADDRESS_LINE_3 IS NULL
         or tai.ADDRESS_LINE_3 = ' '
         then ''
         else CONCAT(tai.ADDRESS_LINE_3,',')  end  || case when tai.VILLAGE 
         IS NULL or tai.VILLAGE = ' '
         then ''
         else CONCAT(tai.VILLAGE,',')  end  || case when mps.ps IS NULL
         or mps.ps = ' '
         then ''
         else CONCAT(mps.ps,',')  end || case when md.district IS NULL
         or md.district = ' '
         then ''
         else CONCAT(md.district,',')  end  || case when ms.state IS NULL
         or ms.state = ' '
         then ''
         else CONCAT(ms.state,',')  end  || case when mn.nationality IS NULL
         or mn.nationality = ' '
         then ''
         else CONCAT(mn.nationality,',')  end  || case when tai.pincode IS 
         NULL or tai.pincode = '0'
         then ''
         -- Error Block End---------------
         else ',' || tai.pincode || '.'  end  FROM
         t_person_address tai
         LEFT JOIN m_nationality mn
         ON mn.NATIONALITY_CD = tai.COUNTRY_CD
         and mn.LANG_CD = tai.LANG_CD
         LEFT JOIN m_state ms
         ON ms.STATE_CD = tai.STATE_CD
         and ms.LANG_CD = tai.LANG_CD
         LEFT JOIN m_district md
         ON md.DISTRICT_CD = tai.DISTRICT_CD
         and md.LANG_CD = tai.LANG_CD
         LEFT JOIN m_police_station mps
         ON mps.PS_CD = tai.PS_CD
         and mps.LANG_CD = tai.LANG_CD WHERE tai.lang_cd = lang
         and tai.PERSON_CODE = PERSON_CODE_PARAM
         and tai.ADDR_TYPE = ADDR_TYPE_PARAM    ORDER BY tai.ADDRESS_CD 
         DESC) WHERE  ROWNUM <= 1;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   end;
   RETURN addressval;
END;

当试图运行get时，我得到以下错误：

PL/SQL: ORA-12704:字符集不匹配

Answer 1

它看起来像nVarchar2的n个问题。你能换一下吗

CONCAT(tai.ADDRESS_LINE_1,'N'',')

至

CONCAT(tai.ADDRESS_LINE_1,n'N'',')

还有其他的。

Answer 2

我认为,必须以NVARCHAR2的形式给出。您可以使详细的CASE THEN ELSE表达式更加可读性：

NVL2(NULLIF(tai.ADDRESS_LINE_1, ' '), tai.ADDRESS_LINE_1||n',', NULL)
||NVL2(NULLIF(tai.ADDRESS_LINE_2, ' '), tai.ADDRESS_LINE_2||n',', NULL)
||NVL2(NULLIF(tai.ADDRESS_LINE_3, ' '), tai.ADDRESS_LINE_3||n',', NULL)
...
||NVL2(NULLIF(tai.pincode, ' '), tai.pincode||n'.', NULL)