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

4
votes

I have a perl script that is inserting records from a text file into our database. Whenever the record has a multibyte character like "RODR_Í_GUEZ". I receive the error ORA-01461, however i'm nowhere near the 4000 characters to switch from varchar2 to long

setting: 

$ENV{NLS_CHARACTERSET} = 'AL32UTF8';

before connecting doesn't seem to help.

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

Any thoughts?

1
perloraclemultibyteora-01461

1 Answers

5
votes

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 output a reasonable value for your system, based on the LANG environment variable.

e.g. for my system (LANG=en_GB.UTF-8) the equivalent Oracle setting is 

NLS_LANG=ENGLISH_UNITED KINGDOM.AL32UTF8

More info: http://forums.oracle.com/forums/thread.jspa?threadID=381531

Sergiusz's post there says practically all you need to know: I'll just add that the Perl DBD::Oracle driver is OCI-based, and the pure-Java JDBC driver isn't, hence they work differently in the same environment.