title: Why does Oracle require TO_NCHAR when binding SQL_C_WCHAR text via ODBC
body: <p>I use the following statement prepared and bound in ODBC:</p>
  
  <pre><code>SELECT (CASE profile WHEN ? THEN 1 ELSE 2 END) AS profile_order 
  FROM engine_properties;
  </code></pre>
  
  <p>Executed in an ODBC 3.0 connection to an Oracle 10g database in AL32UTF8 charset, even after binding to a wchar_t string using <code>SQLBindParameter(SQL_C_WCHAR)</code>, it still gives the error ORA-12704: character set mismatch. </p>
  
  <p>Why? I'm binding as wchar. Shouldn't a wchar be considered an NCHAR? </p>
  
  <p>If I change the parameter to wrap it with <code>TO_NCHAR()</code> then the query works without error. However since these queries are used for multiple database backends, I don't want to add TO_NCHAR just on Oracle text bindings. Is there something that I am missing? Another way to solve this without the TO_NCHAR hammer?</p>
  
  <p>I haven't been able to find anything relevant via searches or in the manuals.</p>
  
  <p>More details...</p>
  
  <p>-- error</p>
  
  <pre><code>SELECT (CASE profile WHEN          '_default'  THEN 1 ELSE 2 END) AS profile_order
  FROM engine_properties;
  </code></pre>
  
  <p>-- ok</p>
  
  <pre><code>SELECT (CASE profile WHEN TO_NCHAR('_default') THEN 1 ELSE 2 END) AS profile_order
  FROM engine_properties;
  </code></pre>
  
  <pre>
  SQL> describe engine_properties;
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   EID                                       NOT NULL NVARCHAR2(22)
   LID                                       NOT NULL NUMBER(11)
   PROFILE                                   NOT NULL NVARCHAR2(32)
   PKEY                                      NOT NULL NVARCHAR2(50)
   VALUE                                     NOT NULL NVARCHAR2(64)
   READONLY                                  NOT NULL NUMBER(5)
  </pre>
  
  <p>This version without TO_NCHAR works fine in SQL Server and PostgreSQL (via ODBC) and SQLite (direct). However in Oracle it returns "ORA-12704: character set mismatch".</p>
  
  <pre><code>SQLPrepare(SELECT (CASE profile WHEN ? THEN 1 ELSE 2 END) AS profile_order 
      FROM engine_properties;) = SQL_SUCCESS
  SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, 
      SQL_VARCHAR, 32, 0, "_default", 18, 16) = SQL_SUCCESS
  SQLExecute() = SQL_ERROR
  SQLGetDiagRec(1) = SQL_SUCCESS
  [SQLSTATE: HY000, NATIVE: 12704, MESSAGE: [Oracle][ODBC]
      [Ora]ORA-12704: character set mismatch]
  SQLGetDiagRec(2) = SQL_NO_DATA
  </code></pre>
  
  <p>If I do use TO_NCHAR, it's okay (but won't work in SQL Server, Postgres, SQLite, etc).</p>
  
  <pre><code>SQLPrepare(SELECT (CASE profile WHEN TO_NCHAR(?) THEN 1 ELSE 2 END) AS profile_order
      FROM engine_properties;) = SQL_SUCCESS
  SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, 
      SQL_VARCHAR, 32, 0, "_default", 18, 16) = SQL_SUCCESS
  SQLExecute() = SQL_SUCCESS
  SQLNumResultCols() = SQL_SUCCESS (count = 1)
  SQLFetch() = SQL_SUCCESS
  </code></pre>