title: ORA-12703: this character set conversion is not supported in UNION
body: <p>I want to create a view from a table in Oracle 12c. Here is the table definition:</p>
  
  <pre><code>desc DABIR_ASSIGN;
  
  Name             Null     Type          
  ---------------- -------- ------------- 
  IDASSIGN         NOT NULL NUMBER(20)    
  IDLETTER                  NUMBER(20)    
  DATE_ASSIGN               VARCHAR2(10)  
  TIME_ASSIGN               DATE          
  PRIORITY                  VARCHAR2(100) 
  STATUS_ASSIGN             VARCHAR2(3)   
  KIND_ASSIGN               VARCHAR2(30)  
  PARAPH                    VARCHAR2(500) 
  PARAPHS                   BLOB          
  PARAPH_FLAG               NUMBER(1)     
  FROM_CODE                 VARCHAR2(20)  
  FROM_POSSITION            VARCHAR2(100) 
  FROM_SAZMAN_NAME          VARCHAR2(100) 
  FROM_INFO                 VARCHAR2(300) 
  TO_CODE                   VARCHAR2(20)  
  TO_POSSITION              VARCHAR2(100) 
  TO_SAZMAN_NAME            VARCHAR2(100) 
  TO_INFO                   VARCHAR2(300) 
  SAVE_YESNO                NUMBER(1)     
  ASSIGN_YESNO              NUMBER(1)     
  ACCEPT_TIME               NUMBER(2)     
  READ_IS                   NUMBER(1)     
  SAVE_IS                   NUMBER(1)     
  ERJA_IS                   NUMBER(1)     
  DABIRASSIGN_IS            NUMBER(1)     
  FOLDER                    VARCHAR2(400) 
  HAFMANCODE                VARCHAR2(200) 
  TREE                      VARCHAR2(150) 
  TASK_IS                   NUMBER(15)    
  RADIF                     NUMBER(3)     
  DATELETTER2               VARCHAR2(10)  
  IDASSIGN2                 NUMBER(10)    
  SENDDATE                  VARCHAR2(10)  
  SENDTIME                  VARCHAR2(5)   
  READ_DATETIME             VARCHAR2(16)  
  MEDIA_YESNO               NUMBER(2)     
  ECE_IS                    NUMBER(1)     
  REMINDER_DATE             VARCHAR2(10)  
  REMINDER_OK               NUMBER(1) 
  </code></pre>
  
  <p>The select query of the view is as follows:</p>
  
  <pre><code>select T.IDASSIGN as "RowID",
         T.IDLETTER as "DocID",
         P.IDASSIGN as "ParentID",
         CASE WHEN T.FROM_CODE = '1' THEN '-1' WHEN odr."RoleID" is null THEN '0' ELSE odr."RoleID" END as "FromRoleID",
         T.FROM_CODE as "FromUserID",
         0 as "IsAssist",
         CASE WHEN T.TO_CODE = '1' THEN '-1' WHEN ODR2."RoleID" is null THEN '0' ELSE ODR2."RoleID" END as "ToRoleID",
         T.TO_CODE as "ToUserID",
         T.SENDDATE as "ReferDate",
         null as "TimeoutDate",
         0 as "HasTrack",
         CONVERT(utl_raw.cast_to_varchar2(dbms_lob.substr(T.paraphs,2000,1)) || utl_raw.cast_to_varchar2(dbms_lob.substr(T.paraphs,2000,2001)),
  'ar8mswin1256','al32utf8')   as "NoteDesc"
  
  FROM DABIR_ASSIGN T inner join DABIR_ASSIGN P ON (T.FROM_CODE=P.TO_CODE AND T.IDLETTER=P.IDLETTER AND T.FROM_CODE &lt;&gt; T.TO_CODE and P.TO_CODE &lt;&gt; P.FROM_CODE)
  LEFT JOIN (select ltrim(min("RoleID"),0) as "RoleID",ltrim(min("UserID"),0) as "UserID" from PEYVAND_OA_DEPT_ROLE group by "UserID") ODR ON (ODR."UserID"=ltrim(T.FROM_CODE,0))
  LEFT JOIN (select ltrim(min("RoleID"),0) as "RoleID",ltrim(min("UserID"),0) as "UserID" from PEYVAND_OA_DEPT_ROLE group by "UserID") ODR2 ON (ODR2."UserID"=ltrim(T.TO_CODE,0))
  
  UNION
  
  select T.IDASSIGN as "RowID",
         T.IDLETTER as "DocID",
         T.IDASSIGN + 1000000 as "ParentID",
         '-1' as "FromRoleID",
         '-1' as "FromUserID",
         0 as "IsAssist",
         CASE WHEN ODR."RoleID" is null THEN '0' ELSE ODR."RoleID" END as "ToRoleID",
         T.TO_CODE as "ToUserID",
         T.SENDDATE as "ReferDate",
         null as "TimeoutDate",
         0 as "HasTrack",
         ''  as "NoteDesc"
  FROM DABIR_ASSIGN T LEFT JOIN (select ltrim(min("RoleID"),0) as "RoleID",ltrim(min("UserID"),0) as "UserID" from PEYVAND_OA_DEPT_ROLE group by "UserID") ODR ON (ODR."UserID"=ltrim(T.TO_CODE,0))
  
  WHERE T.FROM_CODE=1;
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>ORA-12703: this character set conversion is not supported
  12703. 00000 -  "this character set conversion is not supported"
  *Cause:    The requested conversion between two character sets in the CONVERT
             function is not implemented
  *Action:
  </code></pre>
  
  <p>It is obvious the error is because of CONVERT command. If I remove the "Notedesc" everything is OK. I need this conversion because I need the content of 'PARAPHS' column in Persian language. I also used CAST function for both SELECT sections, but nothing's changed:</p>
  
  <pre><code>CAST(
     CONVERT((utl_raw.cast_to_varchar2(dbms_lob.substr(paraphs,2000,1)) || utl_raw.cast_to_varchar2(dbms_lob.substr(paraphs,2000,2001))),
  'ar8mswin1256','al32utf8') as VARCHAR2(4000)) as "NoteDesc"
  </code></pre>
  
  <p>If I run the first SELECT or the 2nd one solely, there is no error. But the error appears when I run SELECTS with UNION!!
  Any help is appreciated.</p>