Solving PLS-00553: Character Set Mismatch Between Variable and Database Column

The PLS-00553 error in PL/SQL indicates a character set incompatibility between a variable (typically a VARCHAR2, CHAR, or CLOB) and a column or function result with a different character set. This often arises in databases that support multilingual environments using AL32UTF8, UTF8, WE8MSWIN1252, etc.

Error Message Format

PLS-00553: character set mismatch between variable and column

This error occurs at compile time, not runtime.

1. Why This Happens

The PL/SQL compiler detects that a string variable declared in your code does not match the character set of the target (a column, expression, or result). PL/SQL requires an explicit conversion when character sets don’t match.

2. Real-World Example of the Error

Let’s assume the column user_name in table users is defined as:

user_name NVARCHAR2(100)

And in your PL/SQL code:

DECLARE
   v_user_name VARCHAR2(100);  -- implicit character set: database default
BEGIN
   SELECT user_name INTO v_user_name FROM users WHERE user_id = 1;
   DBMS_OUTPUT.PUT_LINE(v_user_name);
END;
/

Output:

PLS-00553: character set mismatch between variable and column

Why? Because NVARCHAR2 uses the national character set, which is different from the default VARCHAR2 character set.

3. Solutions to Fix PLS-00553

Solution 1: Use Matching Data Type (NVARCHAR2)

The best fix is to declare your variable with the same character set-compatible type as the column.

Corrected Example:

DECLARE
   v_user_name NVARCHAR2(100);  -- Now it matches the column
BEGIN
   SELECT user_name INTO v_user_name FROM users WHERE user_id = 1;
   DBMS_OUTPUT.PUT_LINE(v_user_name);
END;
/

Solution 2: Use %TYPE to Inherit Column Type

Use %TYPE to automatically match the column’s data type and character set.

DECLARE
   v_user_name users.user_name%TYPE;  -- Automatically uses NVARCHAR2(100)
BEGIN
   SELECT user_name INTO v_user_name FROM users WHERE user_id = 1;
   DBMS_OUTPUT.PUT_LINE(v_user_name);
END;
/

This is the safest and most maintainable approach.

Solution 3: Use TO_CHAR or Explicit CAST with Charset Conversion

If for some reason you must use a VARCHAR2 and the source is NVARCHAR2, cast explicitly:

DECLARE
   v_user_name VARCHAR2(100);
BEGIN
   SELECT TO_CHAR(user_name) INTO v_user_name FROM users WHERE user_id = 1;
   DBMS_OUTPUT.PUT_LINE(v_user_name);
END;
/

You can also use CAST(... AS VARCHAR2) if needed:

SELECT CAST(user_name AS VARCHAR2(100)) INTO v_user_name ...

This tells Oracle to convert character sets explicitly, resolving the mismatch.

4. Other Scenarios Where It Happens

Stored Procedure Parameters

If a procedure accepts a string parameter that doesn’t match the charset of a column being queried or updated, you’ll get the same error.

Problem:

CREATE PROCEDURE update_user(p_name IN VARCHAR2) IS
BEGIN
   UPDATE users SET user_name = p_name WHERE user_id = 1;
END;
/

If user_name is NVARCHAR2, the above fails with PLS-00553.

Fix:

Change the parameter type:

CREATE PROCEDURE update_user(p_name IN NVARCHAR2) IS
BEGIN
   UPDATE users SET user_name = p_name WHERE user_id = 1;
END;
/

Or cast p_name in the query:

UPDATE users SET user_name = TO_NCHAR(p_name) WHERE user_id = 1;

Implicit Data Movement in Packages or Triggers

This can also happen in packages and triggers if one layer of your application uses VARCHAR2 and another (like a table or function result) returns NVARCHAR2.

Always use %TYPE or ensure character set alignment across layers.

5. Character Sets Behind the Scenes

Oracle supports:

• Database character set (used for VARCHAR2, CHAR, etc.)
• National character set (used for NVARCHAR2, NCHAR, NCLOB)

Mismatch occurs when you mix these without conversion.

Use this query to check your database character sets:

SELECT * FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

6. Summary Table

Best Practices

• Use %TYPE and %ROWTYPE for variable declarations.
• Be consistent in your use of VARCHAR2 vs NVARCHAR2.
• Avoid unnecessary conversions unless required for application logic.
• Always test character-sensitive code in multilingual setups.

Conclusion

The PLS-00553 error warns you of unsafe or incompatible character set assignments that could result in data corruption or misinterpretation. By aligning variable and column types using either the same data type (NVARCHAR2, VARCHAR2) or leveraging %TYPE, you ensure compatibility and code safety in multilingual Oracle environments.