Oracle: Resolve “ORA-01704: string literal too long” and/or Non-ANSI Language Character Display Issues

You may experience issues when it comes to special characters. It could be DML (Insert/Update/Delete) where the data is not stored properly (or) display related (SELECT) where the characters do not display correctly as they are stored in the database. You could resolve both. Please continue reading on how to.

Straight-forward case:

The error “ORA-01704: string literal too long” is pretty descriptive. You are trying to work with a string that will not fit and there are some some workarounds for that:

The “not so Straight-forward” case:

In my case (that I helped with) however, it was not due to the obvious reasons. In fact, it was due to character set on an UPDATE whose error was as below with Japanese text:

|| TO_CLOB('SSL暗号化 (HTTPS プロトコル ) され、保護されます。')
*
ERROR at line 9:
ORA-01704: string literal too long

The actual statement was something like this:

update TLanguage set CGU_HP=TO_CLOB('
<span style="font-family:arial;"> <strong>
...
6.10-法に基づく介入または当局の情報開示命令を除いて、COMPANY は、契約の守秘義務を厳守します。クライアントより得たいかなる情報も第三者に開示しません。
')
|| TO_CLOB('6.11- インターネットを通じて受け渡しされる情報は、')
|| TO_CLOB('SSL暗号化 (HTTPS プロトコル ) され、保護されます。')
...
where id=11;
commit;

Match SQL*Plus NLS_LANG to that of the database

After doing a little research I found that the NLS_LANG of the client (command prompt where SQL*Plus is run or another tool) has to match that of the database.

So, when I checked SQL*Plus first, it gave me WE8MSWIN1252 which did not match that of the database

--So, when I checked SQL*Plus first, it gave me WE8MSWIN1252 which did not match that of the database
C:\Users\MyID> sqlplus "/ as sysdba"
-- Check the current NLS_LANG of the command prompt after logging into SQL*Plus
SQL> @.[%NLS_LANG%]
SP2-0310: unable to open file ".[.WE8MSWIN1252]"

--Get the NLS_LANG setting of the database

SELECT * FROM NLS_DATABASE_PARAMETERS  WHERE PARAMETER LIKE '%CHARACTERSET%'
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_CHARACTERSET	AL32UTF8

--In the command prompt (before logging into SQL*Plus),
--    match the character set to that in the database
C:\Users\MyID>set NLS_LANG=.AL32UTF8

--At this point, the characters should be correctly processed from the scripts.

C:\Users\MyID>echo exit | SQLPLUS -S -L "dbuser/*****@dbname" @"\\mysharename\myscript.sql"

PL/SQL procedure successfully completed.

Matching NLS_LANG at the environment level on the Host machine

Here is documentation straight from Oracle on how to match your OS environment NLS_LANG settings with that of the database

Setting the NLS_LANG Environment Variable for Oracle Databases


Follow this procedure to set the NLS_LANG environment variable for Oracle databases.

To set the NLS_LANG environment variable for Oracle databases

  1. Determine the NLS_LANG value.
    1. In the data warehouse database, run the command

    SELECT * FROM V$NLS_PARAMETERS

    1. Make a note of the NLS_LANG value, which is in the format [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].

      For example: American_America.UTF8

  2. For Windows:
    1. Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.
    2. In System variables section, click New.
    3. In the Variable Name field, enter NLS_LANG.
    4. In the Variable Value field, enter the NLS_LANG value that was returned in Step 1.

      The format for the NLS_LANG value should be [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].

      For example: American_America.UTF8.

  3. For UNIX, set the variable as shown below:

    setenv NLS_LANG

    For example: setenv NLS_LANG American_America.UTF8.

    If your data is 7-bit or 8-bit ASCII and the Informatica Server is running on UNIX, then set

    NLS_LANG _.WE8ISO8859P1

    CAUTION:  Make sure you set the NLS_LANG variable correctly, as stated in this procedure, or your data will not display correctly.

  4. Reboot the machine after creating the variable.

PowerShell and SQL*Plus

For reference, I am going to include just the snippet required to get this working in PowerShell. It is not the complete code but just the portion that shows setting codepage and NLS_LANG.

$command = 'chcp 65001; $env:NLS_LANG=".AL32UTF8";'

#The file has to be stored in 

#Is it user/pass based on "/ as sysdba" based invocation?
$command += switch ($PsCmdlet.ParameterSetName)
			{
			“UserPass”  {"SQLPLUS -S -L " + '"$UserName/$Password@' + $SID + '" ' + "``@`"" + $startFile + '"'; break}
			"AsSysDBA"  {"SQLPLUS -S -L " + '"/@' + $SID + ' as sysdba" ' + "``@`"" + $startFile + '"'; break}
			}

#Invoke sql*plus and get the exit code for success/failure<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>
Invoke-Expression $command > $null
$exitCode = $LASTEXITCODE

References:

https://stackoverflow.com/questions/17064314/setting-nls-lang-only-for-sqlplus
https://www.oracle.com/partners/campaign/newsletters/nls-lang-099431.html
https://www.codeproject.com/Tips/1068282/Setting-NLS-LANG-Value-for-Oracle
https://docs.oracle.com/cd/E12102_01/books/AnyInstAdm784/AnyInstAdmPreInstall18.html

This excellent reference helped me get PowerShell working with SQL*Plus for multi-byte characters
http://www.sqlsnippets.com/en/topic-13434.html

Conclusion

Hope this is helpful to someone experiencing DML (or) display issues with special characters!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s