| SAP xMII with string more than 4000 char [message #512640] |
Tue, 21 June 2011 05:18  |
 |
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: SAP xMII with string more than 4000 char [message #512683 is a reply to message #512640] |
Tue, 21 June 2011 08:28  |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
