Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle Error ORA-12704: character set mismatch

833676Jan 24 2011 — edited Jan 24 2011
iam having this function in SQL Server 2005 it is forking fine in SQL Server 2005 but running this on oracle 11g gives error this function is returning the name of employee if his user_id is passed

create or replace
FUNCTION fn_UserEmployeeFullName----------- this name of function
(
v_UserID IN NUMBER---------------this value iam passing to a function
)
RETURN NVARCHAR2
AS
v_EmpName NVARCHAR2(150);--------------this user Declared local variable
BEGIN
SELECT CASE NVL(Employee_Mst.FirstName, '')--------------firstname which is a column in table
WHEN ''
THEN ''
ELSE Employee_Mst.FirstName || ' '
END || CASE NVL(Employee_Mst.MiddleName, '')--------------MiddleName which is a column in table
WHEN ''
THEN ''
ELSE Employee_Mst.MiddleName || ' '
END || CASE NVL(Employee_Mst.LastName, '')--------------.LastName which is a column in table
WHEN ''
THEN ''
ELSE Employee_Mst.LastName || ' '
END
INTO v_EmpName
FROM User_Mst
JOIN Employee_Mst
ON User_Mst.EmpId = Employee_Mst.EmpID
WHERE User_Mst.UserID = v_UserID;

RETURN v_EmpName;---------------------- now returning this value

END;

this gives me compilation error

Error(11,19): PL/SQL: ORA-12704: character set mismatch

iam not understanding plz tell me what is this : character set mismatch

Comments

789895 Jan 24 2011 — edited on Jan 24 2011
Hi,

Probably the issue is that your columns are VARCHAR2 and you want to store that into NVARCHAR2. try to add a cast function around the selected value as

cast(the computed value as NVARCHAR2) into v_EmpName from User_mst;

cheers

VT
833676 Jan 24 2011
my Column datatype is NVARCHAR2
in table
Peter Gjelstrup Jan 24 2011 — edited on Jan 24 2011
Side note:

What are these for?
NVL(Employee_Mst.LastName, '')
Empty string in Oracle is null.

NULL iis never equal to NULL, so this will never be true:
WHEN '' 
In short, this
SELECT CASE NVL(Employee_Mst.FirstName, '')--------------firstname which is a column in table
WHEN ''
THEN ''
ELSE Employee_Mst.FirstName || ' '
Is just the same as
SELECT Employee_Mst.FirstName
EDIT: Maybe I misunderstood your spacing, you actually have
SELECT NVL2(Employee_Mst.FirstName, Employee_Mst.FirstName || ' ', NULL)
Regards
Peter

Edited by: Peter on Jan 24, 2011 5:02 AM
789895 Jan 24 2011 — edited on Jan 24 2011
Hi,

Change all the blanks '' with cast('' as nvarchar2(1))

Try this code.
create or replace FUNCTION fn_UserEmployeeFullName(v_firstname IN NVARCHAR2) RETURN NVARCHAR2
AS
v_EmpName NVARCHAR2(150);
BEGIN
SELECT CASE NVL(Employee_Mst.FirstName, cast('' as nvarchar2(1))) WHEN cast('' as nvarchar2(1)) THEN cast('' as nvarchar2(1))
ELSE Employee_Mst.FirstName || cast(' ' as nvarchar2(1))
END || CASE NVL(Employee_Mst.MiddleName, cast('' as nvarchar2(1)))--------------MiddleName which is a column in table
WHEN cast('' as nvarchar2(1))
THEN cast('' as nvarchar2(1))
ELSE Employee_Mst.MiddleName || cast(' ' as nvarchar2(1))
END || CASE NVL(Employee_Mst.LastName, cast('' as nvarchar2(1)))--------------.LastName which is a column in table
WHEN cast('' as nvarchar2(1))
THEN cast('' as nvarchar2(1))
ELSE Employee_Mst.LastName || cast(' ' as nvarchar2(1))
END
INTO v_EmpName
FROM User_Mst
JOIN Employee_Mst
ON User_Mst.EmpId = Employee_Mst.EmpID
WHERE User_Mst.UserID = v_UserID;


RETURN v_EmpName;

end fn_UserEmployeeFullName;
/
cheers

VT

Edited by: VT on Jan 24, 2011 7:08 PM

Edited by: VT on Jan 24, 2011 7:08 PM
Peter Gjelstrup Jan 24 2011 — edited on Jan 24 2011
Could be VT. Good idea.



Maybe even use NCHR function:
CREATE OR REPLACE FUNCTION fn_useremployeefullname (p_userid  IN  NUMBER)
RETURN NVARCHAR2
AS
  v_empname NVARCHAR2(150);
BEGIN
  SELECT NVL2(employee_mst.firstname,  employee_mst.firstname  || NCHR(32), NULL) ||
         NVL2(employee_mst.middlename, employee_mst.middlename || NCHR(32), NULL) || 
         NVL2(employee_mst.lastname,   employee_mst.lastname   || NCHR(32), NULL)
    INTO v_empname
    FROM user_mst
       JOIN employee_mst
         ON user_mst.empid = employee_mst.empid
      WHERE user_mst.userid = p_userid;        

  RETURN v_empname;
END fn_useremployeefullname;
       
Regards
Peter
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 21 2011
Added on Jan 24 2011
5 comments
4,667 views