Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Problems with Oracle's LONG datatype on insert (ORA-01461). HELP!
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
![]() |
![]() |