sql - PL/SQL:ORA-12704:将varchar2插入nvarchar2时，字符集不匹配

我尝试在循环中填充表TBL。并在子查询上获取ORA-12704: character set mismatch。
这是我使用的查询：

BEGIN
  FOR i IN (SELECT t.Stuff FROM STUFF_TABLE t ORDER BY t.Name ASC)
  LOOP
  INSERT INTO TBL(StuffId, StuffName)
          VALUES(
            i.Stuff,
            (SELECT TempStuffName FROM
                (SELECT COALESCE(st.StuffName, i.Stuff) as TempStuffName FROM STUFFDEFINITION st WHERE st.Stuff = i.Stuff ORDER BY st.Version DESC)
             WHERE ROWNUM = 1)
          );
  END LOOP;
END;

列类型如下：

STUFF_TABLE.Stuff nvarchar2(30)
TBL.StuffId nvarchar2(30)
TBL.StuffName nvarchar2(50)
STUFFDEFINITION.Stuff varchar2(255)
STUFFDEFINITION.StuffName varchar2(255)

据我了解，问题在于类型转换，即从varchar2（255）到nvarchar2（50）。
我尝试使用CAST，翻译功能，但并没有帮助。 ORA-12704: character set mismatch仍然出现。

我尝试这样做时，是否有办法在循环中填充TBL？

以下是重现我谈论的问题的测试数据：

CREATE TABLE STUFF_TABLE
(
  Stuff nvarchar2(30),
  Name nvarchar2(50)
);

CREATE TABLE TBL
(
  StuffId nvarchar2(30),
  StuffName nvarchar2(50)
);

CREATE TABLE STUFFDEFINITION
(
  Stuff varchar2(255),
  StuffName varchar2(255),
  Version number(19)
)

INSERT INTO STUFF_TABLE(Stuff, Name) VALUES('First', 'Name1');
INSERT INTO STUFF_TABLE(Stuff, Name) VALUES('Second', 'Name2');
INSERT INTO STUFF_TABLE(Stuff, Name) VALUES('Third', 'Name3');

INSERT INTO STUFFDEFINITION(Stuff, StuffName, Version) VALUES('First', 'First Stuff', 1);
INSERT INTO STUFFDEFINITION(Stuff, StuffName, Version) VALUES('First', 'First Stuff', 2);
INSERT INTO STUFFDEFINITION(Stuff, StuffName, Version) VALUES('Second', 'Second Stuff', 1);
INSERT INTO STUFFDEFINITION(Stuff, StuffName, Version) VALUES('Third', 'Third Stuff', 1);

最佳答案

从您的陈述中可以很明显地看出，您正在将varchar2列与nvarchar2联接在一起，并将varchar2列插入到nvarchar2列中。您需要先进行转换。试试这个：

BEGIN
  FOR i IN (SELECT t.Stuff FROM STUFF_TABLE t ORDER BY t.Name ASC)
  LOOP
  INSERT INTO TBL(StuffId, StuffName)
          VALUES(
                 i.Stuff,
                    (SELECT to_nchar(TempStuffName)
                       FROM
                        (SELECT COALESCE(to_nchar(st.StuffName), i.Stuff) as TempStuffName
                         FROM STUFFDEFINITION st
                         WHERE to_nchar(st.Stuff) = i.Stuff
                         ORDER BY st.Version DESC)
                     WHERE ROWNUM = 1)
               );
  END LOOP;
END;