Kick ORA-01461 when inserting multibyte characters from perl into oracle

Question

I have a perl script that inserts entries from a text file into our database. Whenever a record has a multibyte character, such as "RODR_Í_GUEZ". I get ORA-01461 error message, however I am not getting close to 4000 characters anywhere to switch from varchar2 to long

:

$ENV{NLS_CHARACTERSET} = 'AL32UTF8'; 

before connecting does not seem to help.

Using the java client (SQuirreL SQL) and manually writing the INSERT INTO statement inserts the record just fine, so I'm sure this is not how the database is configured.

Any thoughts?

Answer 1

You probably want to set the NLS_LANG environment variable. For Unix-ish systems, there is a script supplied in $ ORACLE_HOME / server / bin called nls_lang.sh to display a reasonable value for your system based on the LANG environment variable.

eg. for my system (LANG = en_GB.UTF-8) equivalent Oracle setup

 NLS_LANG=ENGLISH_UNITED KINGDOM.AL32UTF8 

Additional information: http://forums.oracle.com/forums/thread.jspa?threadID=381531

The Sergiusz post says almost everything you need to know: I’ll just add that the Perl DBD :: Oracle driver is based on OCI, and the pure Java Java JDBC driver is not, therefore, they work differently in the same environment .