Oracle 7.3 LONG Varchar limitation to 2000 characters (ORA-01704); string literal too long

From: Kevin <kevinwc_at_tidecom.com>
Date: 1998/11/18
Message-ID: <72vj2q$5uq$1@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.

From what I have read this is because quoted strings may not contain more than 2000 characters. However a Long Varchar datatype can support upto 2GB worth of text. I have taken the SQL statement out of our application and tried to run it SQL Plus 3.3 and the same error is happening. At this point changing the database structure is out of the question (maintenance nightmare).

I have tried the following thinking that perhaps this would work

UPDATE ls_table SET ls_column = ls_column || ' ' || ls_RetVal;

But this also did not end in a desireable result.

Has anyone found a way around this either with a PowerBuilder 5.0.3 or an Oracle 7.3 solution?

Any help would be greatly appreciated.

Thank you in advance.

Kevin Chamberlain
kevinwc_at_tidecom.com Received on Wed Nov 18 1998 - 00:00:00 CST