hotpelmen has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

I am hoping to get some input from those who have encountered this situation and know the solution.

When inserting/updating a varchar2 field in an Oracle 9i table in a Perl application, we occasionally observe the following error:

ORA-01461: can bind a LONG value only for insert into a LONG column

Same error was observed in a J2EE application that uses same database but a different table and column, also varchar2. The database is 9i but that application uses 10g JDBC driver.

Our DBA found an easy way to fix it in the Java application: setting in oracle-ds.xml file the following property:

<connection-property name="oracle.jdbc.RetainV9LongBindBehavior">true</connection-property>

If someone has seen it before, do you know of any similar approach in Perl DBI usage? We'd like to avoid changing NLS* environment variables or any other parameters in the database since this may adversely affect multiple applications.

Thanks!

Replies are listed 'Best First'.
Re: DBI ORA-01461
by cleverett (Friar) on May 11, 2010 at 16:47 UTC
    Oracle throws this error when you are trying to insert >= 4000 chars into a VARCHAR2 column. When the database is set to UTF8, but your string isn't UTF8, Oracle does the conversion internally, which can expand the string severalfold, which causes it to throw this error.

    Several ideas:

    1. Load your data using the same character set as the database, or a subset thereof. That may mean you need to convert the string inside the application. If you are getting UTF8 data from a file, then use the IO layers to read the string as UTF8. Or you may need to set your Perl locale correctly.
    2. migrate the VARCHAR2 columns to CLOBs if you need to.
    3. Truncate the strings before loading them.
    4. Alter the character set for the database handle's session.
[reply]
      Cleverett, thank you so much for the advice. I will add another post here whenever we implement a solution.
[reply]

Back to Seekers of Perl Wisdom