Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Problems with Oracle's LONG datatype on insert (ORA-01461). HELP!

Problems with Oracle's LONG datatype on insert (ORA-01461). HELP!

From: João Silva <penosi_at_teleweb.pt>
Date: Mon, 27 Nov 2000 08:31:15 +0100
Message-ID: <8vtut1$gkr$1@venus.telepac.pt>

Hello all.

I work for a company that recently migrated their VB application from ODBC 1.0 with Oracle 7 to ODBC 3.51 with Oracle 8. The problem occurs during an insert that worked with the previous versions of the database but now coughs up an ORA-01461 message when inserting >2000 characters of text into the LONG column of a table.

Table = MTTXOB

Column Defs:
...

TXOBNPOL       Number(9)
TXOBDATA       Date
TXOBTEXT        LONG
TXOBCDCE       Number(9)

...

Code:

    REM The string var Data_Long contains the large (about 10 K) text to be written to

    REM the database which was retrived from a Text Box control.

    Dim LData as Long
    Dim LDataIn as Long

    Statement = 'insert into mttxob (txobtext) values (?)'

    SQLAllocHandle(SQL_HANDLE_STMT, G_HDBC, HSTMT&)

    SQLPrepare(HSTMT&, Statement, SQL_NTS)     LData = Len(Trim$(Data_Long))
    LDataIn = SQL_NTS

    REM Original statement from ODBC 1 version was:     REM SQLSetParam(HSTMT&, 1, SQL_C_CHAR, SQL_CHAR, LData, 0, Data_Long, LDataIn)

    REM Which our programmer translated to the following, according to the ODBC manuals.

    Retcode% = SQLBindParameter(HSTMT&, 1, SQL_PARAM_INPUT_OUTPUT,

                                    SQL_C_CHAR, SQL_CHAR, LData, 0,
Data_Long,
                                    SQL_SETPARAM_VALUE_MAX, LDataIn)

    Retcode% = SQLExecute(HSTMT&)

This code works fine when the text is < approx. 4000 characters (I know now that this is the literal limit for Oracle). Above this number I receive the ORA-01461 message (can bind a LONG value only for insert into a LONG column). When I first thought I had fixed the problem I simply replaced the SQL_SETPARAM_VALUE_MAX parameter with 0. This eliminated the error but began truncating text at 2000 characters in length!

I've tried various things to try and correct this problem. Are we limited to 4000 characters using the LONG type in Oracle 8? If so then I guess we need to convert the field to a LOB, but if not, could someone give me a code snippet that would correct the problem?

Thanks in advance for all your help. Received on Mon Nov 27 2000 - 01:31:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US