title: Oracle ASCII Character Set - .Net Extensions
body: <p>I am trying to extract data out an Oracle table. The column is defined as char(40) and contains extended ASCII values. Currently in SQL*Plus, the extended characters are displaying as an "upside-down" question mark. I need to be able to extract the ASCII characters that have a decimal value > 128 and display in the extended or 8-bit encoding schema.  <br></p>
  
  <p>-- Current Output <br></p>
  
  <p>select myfield from Mytable; <br></p>
  
  <p>b@¿pmxJ¿¿¿.¿¿¿P9¿¿¿¿¿+¿¿w)¿@Y@B¿¿¿ <br></p>
  
  <p><br></p>
  
  <p>-- Decimal Values <br></p>
  
  <p>Typ=96 Len=40:  <br>
  98,64,189,112,109,120,74,156,27,231,167,46,224,28,130,183,80,57,202,232,150,239,144,43,169,1,186,119,41,254,64,89,64,12,19,8,66,194,136,210
   <br></p>
  
  <p>-- Database Characterset <br></p>
  
  <p>SQL> select * from NLS_DATABASE_PARAMETERS; <br></p>
  
  <p>PARAMETER                      VALUE <br>
   <br></p>
  
  <p>------------------------------ ---------------------------------------- <br></p>
  
  <p>NLS_CSMIG_SCHEMA_VERSION       5 <br></p>
  
  <p>NLS_LANGUAGE                   AMERICAN <br></p>
  
  <p>NLS_TERRITORY                  AMERICA <br></p>
  
  <p>NLS_CURRENCY                   $ <br></p>
  
  <p>NLS_ISO_CURRENCY               AMERICA <br></p>
  
  <p>NLS_NUMERIC_CHARACTERS         ., <br></p>
  
  <p>NLS_CHARACTERSET               US7ASCII <br></p>
  
  <p>NLS_CALENDAR                   GREGORIAN <br></p>
  
  <p>NLS_DATE_FORMAT                DD-MON-RR <br></p>
  
  <p>NLS_DATE_LANGUAGE              AMERICAN <br></p>
  
  <p>NLS_SORT                       BINARY <br></p>
  
  <p><br></p>
  
  <p>PARAMETER                      VALUE <br></p>
  
  <p>------------------------------ ---------------------------------------- <br></p>
  
  <p>NLS_TIME_FORMAT                HH.MI.SSXFF AM <br></p>
  
  <p>NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM <br></p>
  
  <p>NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR <br></p>
  
  <p>NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR <br></p>
  
  <p>NLS_DUAL_CURRENCY              $ <br></p>
  
  <p>NLS_COMP                       BINARY <br></p>
  
  <p>NLS_LENGTH_SEMANTICS           BYTE <br></p>
  
  <p>NLS_NCHAR_CONV_EXCP            FALSE <br></p>
  
  <p>NLS_NCHAR_CHARACTERSET         UTF8 <br></p>
  
  <p>NLS_RDBMS_VERSION              10.2.0.3.0 <br></p>
  
  <p>I do not have a lot of experience with Oracle. Any help would be greatly appreciated!</p>
  
  <p>Thanks,
  Brennan</p>
  
  <hr>
  
  <p>I was able to extract the data in decimal format and process it in the .Net function without issue. The SQL statement is a bit messing. I am trying to write a function to assist in the query. I am having problems creating the function.</p>
  
  <p>current SQL statement:</p>
  
  <pre><code>select DECRYPTDATA(
      ASCII(SUBSTR(ACTIONCD,1,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,2,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,3,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,4,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,5,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,6,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,7,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,8,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,9,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,10,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,11,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,12,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,13,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,14,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,15,1)) || ',' ||
      ASCII(SUBSTR(ACTIONCD,16,1)), '1'  )
          from ORDERACTIONS where KEYNUM = 1
  </code></pre>
  
  <p>I would like to make a quick function to return this string.</p>
  
  <p>Below is the function that will not compile. Any suggestions?</p>
  
  <pre><code>create or replace function ASCIITODEC(p_actioncd VARCHAR2)
  return VARCHAR2 is
  begin
  
      return ASCII(SUBSTR(p_actioncd,1,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,2,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,3,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,4,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,5,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,6,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,7,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,8,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,9,1))  || ',' ||
             ASCII(SUBSTR(p_actioncd,10,1)) || ',' ||
             ASCII(SUBSTR(p_actioncd,11,1)) || ',' ||
             ASCII(SUBSTR(p_actioncd,12,1)) || ',' ||
             ASCII(SUBSTR(p_actioncd,13,1)) || ',' ||
             ASCII(SUBSTR(p_actioncd,14,1)) || ',' ||
             ASCII(SUBSTR(p_actioncd,15,1)) || ',' ||
             ASCII(SUBSTR(p_actioncd,16,1)) )
  
  end ASCIITODEC;
  /
  </code></pre>
  
  <p>Thanks,
  Brennan</p>