CODEWITHSUNDEEP
sqloracle-databaseplsql
Dragon
Dragon

Reputation: 2481

PL/SQL: ORA-12704: character set mismatch when insert varchar2 to nvarchar2

I try to populate a table TBL in a loop. And get ORA-12704: character set mismatch on a subquery. Here is a query I use:

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;

The columns types are the following:

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

The issue, as I understand it, is in type casting namely from varchar2(255) to nvarchar2(50). I tried to use CAST, Translate functions, but it didn't help. The ORA-12704: character set mismatch still occurs.

Is there a way to populate TBL in a loop as I try it to do?

Below is the test data to reproduce issue I talk about:

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

Upvotes: 3

Views: 2004

Answers (3)

MT0
MT0

Reputation: 168588

You shouldn't need to use PL/SQL:

INSERT INTO TBL ( StuffId, StuffName )
SELECT TO_NCHAR( t.stuff ),
       TO_NCHAR(
         COALESCE(
           MAX( d.StuffName ) KEEP ( DENSE_RANK LAST ORDER BY d.version ),
           t.stuff
         )
       )
FROM   StuffTable t
       LEFT OUTER JOIN StuffDefinition d
       ON ( t.stuff = TO_NCHAR( d.stuff ) )
GROUP BY t.stuff;

Upvotes: 1

XING
XING

Reputation: 9886

From your statement its clear that you are joining a varchar2 column with nvarchar2 and inserting a varchar2 column to a nvarchar2 column. You need to do a conversion first. Try this:

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;

Upvotes: 2

sandman
sandman

Reputation: 2118

Oracle says this about that error: 'A string expression in the VALUES clause of an INSERT statement does not have the same character set as the column into which the value would be inserted.'

Please select from nls_database_parameters and tell me what the values of these are: NLS_CHARACTERSET NLS_NCHAR_CHARACTERSET NLS_RDBMS_VERSION NLS_LENGTH_SEMANTICS

Upvotes: 0

Related Questions