ORA-12704 character set mismatch hatası hakkında

ORA-12704 character set mismatch hatası hakkında:

Karaketer uyuşmazlığı için çözümler:

 

select cast('aa' as nvarchar2(100)) from dual

 

select to_nchar('aa') from dual

 

select translate('aa' using nchar_cs) from dual

 

select csconvert('aa','NCHAR_CS') from dual

 

 

Kaynak:

http://www.di.unipi.it/~ghelli/didattica/bdldoc/B19306_01/server.102/b14200/functions187.htm

http://www.di.unipi.it/~ghelli/didattica/bdldoc/B19306_01/server.102/b14200/functions197.htm#i79617

http://psoug.org/reference/undocumented.html

http://forums.oracle.com/forums/thread.jspa?threadID=1029116

http://www.experts-exchange.com/Database/Software/Q_24922708.html

 

Karakter seti uyuşmazlığına neden olan hal:

 

SELECT DISTINCT

       VNC.USER_ID, NN.NICE_SWITCHNAME switch, UDI.LOGINCODE agent_id

  FROM CORP.T_USERDATASOURCEITEM udi,

       corp.VW_NICE_USERS vnc,

       CORP.VW_USERDETAILS uu,

       CORP.T_NICE_SWITCH nn

 WHERE     VNC.USER_ID = UU.USERCODE

       AND UU.USERID = UDI.USERID

       AND (TRUNC (SYSDATE) BETWEEN UDI.STARTDATE AND UDI.ENDDATE)

       AND UDI.DATASOURCEITEMID IN (3, 4, 5)

       AND UDI.DATASOURCEITEMID = NN.BIRLINK_SWITCHID

       AND NN.ISACTIVE = 1

UNION

SELECT DISTINCT

       VNC.USER_ID,

       CASE

          WHEN Al.Location = 'HALKALI'

          THEN

             'HALPDS'

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 1)

          THEN

             'IZMPDSHAL'

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 16)

          THEN

             'IZMPDSHAL'

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 17)

          THEN

             'IZMPDSHAL'

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 18)

          THEN

             'IZMPDSHAL'

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 9)

          THEN

             'IZMPDSHAL'

          WHEN     (Al.Location = 'İZMİR')

               AND (LL.LOCATIONCODE = 3)

               AND (UU.ORGANIZATIONCODE = '50022296')

          THEN

             'IZMPDSHAL'

          WHEN (LL.LOCATIONCODE = 14)

          THEN

             'IZMPDSHAL'

          ELSE

             'IZMPDS'

       END

          AS switch,

       TO_CHAR (al.loginid) agent_id

  FROM CORP.T_USERDATASOURCEITEM udi,

       corp.VW_NICE_USERS vnc,

       CORP.VW_USERDETAILS uu,

       CORP.T_PDS_AGENT_LOGINID AL,

       CORP.CORE_LOCATION LL

 WHERE     VNC.USER_ID = UU.USERCODE

       AND UU.USERID = UDI.USERID

       AND UU.LOCATIONID = LL.LOCATIONID

       AND (TRUNC (SYSDATE) BETWEEN UDI.STARTDATE AND UDI.ENDDATE)

       AND UDI.DATASOURCEITEMID IN (2)

       AND AL.USERNAME = UDI.LOGINCODE

Karakter seti uyuşmazlığı çözülmüş hali:

 

SELECT DISTINCT

       VNC.USER_ID, NN.NICE_SWITCHNAME switch, UDI.LOGINCODE agent_id

  FROM CORP.T_USERDATASOURCEITEM udi,

       corp.VW_NICE_USERS vnc,

       CORP.VW_USERDETAILS uu,

       CORP.T_NICE_SWITCH nn

 WHERE     VNC.USER_ID = UU.USERCODE

       AND UU.USERID = UDI.USERID

       AND (TRUNC (SYSDATE) BETWEEN UDI.STARTDATE AND UDI.ENDDATE)

       AND UDI.DATASOURCEITEMID IN (3, 4, 5)

       AND UDI.DATASOURCEITEMID = NN.BIRLINK_SWITCHID

       AND NN.ISACTIVE = 1

UNION

SELECT DISTINCT

       VNC.USER_ID,

       CASE

          WHEN Al.Location = 'HALKALI'

          THEN

             TO_NCHAR('HALPDS')

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 1)

          THEN

             TO_NCHAR('IZMPDSHAL')

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 16)

          THEN

             TO_NCHAR('IZMPDSHAL')

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 17)

          THEN

             TO_NCHAR('IZMPDSHAL')

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 18)

          THEN

             TO_NCHAR('IZMPDSHAL')

          WHEN (Al.Location = 'İZMİR') AND (LL.LOCATIONCODE = 9)

          THEN

             TO_NCHAR('IZMPDSHAL')

          WHEN     (Al.Location = 'İZMİR')

               AND (LL.LOCATIONCODE = 3)

               AND (UU.ORGANIZATIONCODE = '50022296')

          THEN

             TO_NCHAR('IZMPDSHAL')

          WHEN (LL.LOCATIONCODE = 14)

          THEN

             TO_NCHAR('IZMPDSHAL')

          ELSE

             TO_NCHAR('IZMPDS')

       END

          AS switch,

       TO_NCHAR(al.loginid) AS agent_id

  FROM CORP.T_USERDATASOURCEITEM udi,

       corp.VW_NICE_USERS vnc,

       CORP.VW_USERDETAILS uu,

       CORP.T_PDS_AGENT_LOGINID AL,

       CORP.CORE_LOCATION LL

 WHERE     VNC.USER_ID = UU.USERCODE

       AND UU.USERID = UDI.USERID

       AND UU.LOCATIONID = LL.LOCATIONID

       AND (TRUNC (SYSDATE) BETWEEN UDI.STARTDATE AND UDI.ENDDATE)

       AND UDI.DATASOURCEITEMID IN (2)     

       AND AL.USERNAME = UDI.LOGINCODE