Home » Applications » Other Application Suites » SAP xMII with string more than 4000 char
SAP xMII with string more than 4000 char [message #512640] Tue, 21 June 2011 05:18 Go to next message
Anishkrithik
Messages: 23
Registered: June 2011
Location: TN
Junior Member
Hi,

We are using SAP xMII application which connects Oracle DataBase 10g using 'oracle.jdbc.driver.OracleDriver'. We are getting "ORA-01704: string literal too long" error while passing string with more than 4000 char from Application to Oracle DataBase. Is there any application which has Text box which can accept more than 4000 char and pass the same to Oracle DB.


[MERGED by LF]

[Updated on: Tue, 21 June 2011 08:32] by Moderator

Report message to a moderator

Re: Oracle.JDBC with 4000++ char string [message #512680 is a reply to message #512640] Tue, 21 June 2011 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CLOB datatype parameter not VARCHAR2.

Regards
Michel
Re: SAP xMII with string more than 4000 char [message #512683 is a reply to message #512640] Tue, 21 June 2011 08:28 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that SAP "text box" is large enough (actually, too large) as it is trying to store a string larger than 4000 characters into a VARCHAR2(4000) column. So it seems that you should either restrict input (which is, most probably, sub-optimal - why losing any information?) or enlarge the storage. However, as VARCHAR2(4000) is the largest value possible for that data type, you should use a CLOB. Unfortunately, you can't simply alter a table and modify VARCHAR2 to a CLOB.

It means that you should ADD another column, copy old ones contents into the new one and drop the old column; finally, you'd rename the new column to a new name. Something like this:
SQL> create table test (col varchar2(4000));

Table created.

SQL> insert into test values ('abc');

1 row created.

SQL> alter table test modify col clob;
alter table test modify col clob
                        *
ERROR at line 1:
ORA-22858: invalid alteration of datatype


SQL> alter table test add col_clob clob;

Table altered.

SQL> update test set col_clob = col;

1 row updated.

SQL> alter table test drop column col;

Table altered.

SQL> alter table test rename column col_clob to col;

Table altered.

SQL> select * from test;

COL
-------------------------------------------------------------
abc

Another approach, which allows change of the data type, requires the column to be empty. It would then be VARCHAR2 -> LONG -> CLOB. I'm not sure this is an option for you as I suppose that there already are records in that table whose VARCHAR2 column is full of data.
SQL> create table test (col varchar2(4000));

Table created.

SQL> insert into test values ('abc');

1 row created.

SQL> alter table test modify col long;
alter table test modify col long
                        *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


SQL> delete from test;

1 row deleted.

SQL> alter table test modify col long;

Table altered.

SQL> alter table test modify col clob;

Table altered.

SQL>


Now, I have no idea whether SAP application supports such a database manipulations. Most probably you should also change the application which doesn't have to be a simple task.

So, maybe restricting an input to 4000 characters is the simplest way to solve the issue ...

On the other hand, keeping on mind that I don't know SAP, perhaps you should wait for someone who knows what he/she is talking about.
Previous Topic: prompt in BusinessObjects
Next Topic: Oracle Financial Analyzer
Goto Forum:
  


Current Time: Thu Mar 28 08:15:16 CDT 2024