title: Importing Latin-1 (ISO 8859-1) text into UTF-8 Oracle Database varchar2 field
body: <p>We have an Oracle database, which Oracle E-Business Suite (R12.1.3) runs against.</p>
  
  <p>I checked how to find the character set of the database, via this SO answer: <a href="https://stackoverflow.com/questions/34890980/how-to-view-oracle-table-column-character-set">How to view Oracle table column character set?</a></p>
  
  <p>This SQL:</p>
  
  <pre><code>SELECT *
    FROM v$nls_parameters
   WHERE parameter LIKE '%CHARACTERSET'
  </code></pre>
  
  <p>Returned:</p>
  
  <pre><code>PARAMETER           VALUE
  ----------------------------------------
  NLS_CHARACTERSET    UTF8
  NLS_N_CHARACTERSET  UTF8
  </code></pre>
  
  <p>There's an interface from an external system, which sends text files containing AP invoice headers and lines.</p>
  
  <p>The data sent from the external party is also UTF8.</p>
  
  <p>However, the Description field of the AP_INVOICE_LINES_ALL table in Oracle E-Business Suite is a VARCHAR2 (240 bytes).</p>
  
  <p>Since that's the case, the <code>£</code> sign takes up 2 bytes, and we are having problems where sometimes the description from the external file is > 240 bytes, and when that happens, <s>the file is rejected</s> problems are caused with the invoice import process. It's not that the file is rejected, but due to the design of the interface, because the description exceeds 240 bytes, some of the invoice line data is not loaded.</p>
  
  <p>The developers of the external system have confirmed that they cannot truncate their Invoice Long Description to a specific byte count because of the risk of splitting a character like the £ sign, thus resulting in <s>an invalid file</s> problems with the import process.</p>
  
  <p>The external developer are proposing to move to a single byte per character encoding format such as Latin-1 (ISO 8859-1).</p>
  
  <p>I realise the simple way to test this would be for them to send us a sample text file with this type of encoding, and for us to import it into the system to check what the contents look like.</p>
  
  <p>That would take us some time to test.</p>
  
  <p>As a quick test, I checked something in Notepad++ (I realise that's nothing to do with an Oracle database).</p>
  
  <p>I saved a file in Notepad++, where the file was encoded as Latin-1 (ISO 8859-1), and typed in a £ sign:</p>
  
  <p><a href="https://i.stack.imgur.com/88pTX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/88pTX.png" alt="Start with Latin-1 (ISO 8859-1)"></a></p>
  
  <p>Then I changed the encoding to UTF-8:</p>
  
  <p><a href="https://i.stack.imgur.com/6gv6Q.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6gv6Q.png" alt="Change encoding to UTF-8"></a></p>
  
  <p>On doing so, the £ sign becomes unreadable.</p>
  
  <p>I wondered if this is a reliable test, as in, would the same thing likely happen in Oracle?</p>
  
  <p>I can't test in Oracle as I don't have access to create databases etc. I thought I could try some basic test with an installation of Oracle Database 11g Express Edition, but have seen that it's not possible to create more than the default XE database on such an installation.</p>
  
  <p><strong>We don't have the option to change any properties of the AP Invoice Lines All table, since it's a core table used in Oracle E-Business Suite. The only option we have is to make changes at the source of the process, from the external systems.</strong></p>