Oracle 7.3 LONG Varchar limitation to 2000 characters (ORA-01704); string literal too long
Date: 1998/11/18
Message-ID: <72vj2q$5uq$1_at_news-2.news.gte.net>#1/1
Let me start by stating what the program does that we are working on. The program at hand is a simple data loading program for converting old DATA from a DBF (created in Powerbuilder 5.0.3). We read in a single line of text from a fixed length file that contains the primary key values, and text for a LONG VARCHAR field. The text file only contains 72 characters for the memo field per line. If the original memo has more than 72 characters then another row is created in the text file that is used for the load.
The way that the application works is that it reads the primary key values from the first row, grabs the value in the memo field for that record, adds the new text to the end of the value that was retrieved from the memo field, and then updates the table and places the new text into the memo field. An example of the code is show below:
ls_column: this is the column name for the memo that is to be updated
ls_table: this is the table that contains the memo field to update
ls_primary: this is a clause that states the primary fields and the values that are used for this one record that we want to update the memo field on.
string ls_syntax, ls_retval
ls_syntax = 'Select ' + lower(ls_column) + ' from ' + &
lower(ls_table) + ' where ' + ls_primary
DECLARE cursor_append_text DYNAMIC CURSOR FOR sqlsa;
PREPARE SQLSA FROM :ls_syntax USING sqlca;
OPEN DYNAMIC cursor_append_text;
FETCH cursor_append_text into :ls_RetVal;
If (not isnull(ls_RetVal)) Then
ls_RetVal = "'" + ls_RetVal + " " + ls_value + "'" Else
ls_RetVal = Trim(ls_Value)
End IF
Return ls_RetVal
This part appears to be working fine. The string variable ls_RetVal does contain all the text it should (even when there are more than 2000 charactes). Shortly after this function completes we end up with a statement that looks like the following:
ls_sqlsyntax = "UPDATE <ls_table> SET <ls_column> = <ls_RetVal> WHERE ls_primary"
Up until this point things are still looking fine. We then execute the SQL statement from within our application with the Powerbuilder statement
EXECUTE IMMEDIATE :ls_SqlSyntax USING sqlca;
At this point we get a SQL error code ORA-01704: String Literal Too Long.