-
I have a column in database with datatype = long. In my insert statement, one of the text literal has more than 4400 characters. It gives me this error message:
java.sql.SQLException: ORA-01704: string literal too long
How do I solve this problem?
Will it help if I concate my text literl using
'a'||'b'||'c'
where 'a', 'b', 'c' are text literals of length less than 4000.
-
hey guys,
still nobody knows how to do that? If it is not possible to insert long string literals to the database, why there is a long datatype?
Can anyone suggest a way to store large amount of text(like 10000 characters) in the database? sample code or syntax will be appreciated. Thanks...
-
Sollution !!!
You need to break up the string into 2000 character strings (or whatever the
limit is), then concatenate them in the query (using the concatenating Oracle
symbol "||" ).
ie
string='longString'
string1='long'
string2='String'
INSERT INTO table
(longStringcolumn)
VALUES ( '#string1#'||'#string2#' )
-
A solution with limits, maybe...
Nope, this is NOT a general, for-all solution : starting with, the SQL language have a (documented!) limit of 4000 bytes for a string, so concating works ONLY below this limit :
JLCHIAPPA@dwpan:SQL>create table T_LONG (c1 number, c2 long);
Table created.
==> 500 bytes, OK ...
JLCHIAPPA@dwpan:SQL>insert into T_LONG values (1, lpad('*', 500, '$') || lpad('*', 500, '$') );
1 row created.
==> 2500 bytes for a string, OK ...
JLCHIAPPA@dwpan:SQL>insert into T_LONG values (2, lpad('*', 500, '$') || lpad('*', 500, '$')
2 || lpad('*', 500, '$') || lpad('*', 500, '$') || lpad('*', 500, '$') );
1 row created.
==> BUT see this, concating small pieces above the 4000 bytes limit, no way in SQL :
JLCHIAPPA@dwpan:SQL>insert into T_LONG values (3, lpad('*', 500, '$')
2 || lpad('*', 500, '$')
3 || lpad('*', 500, '$')
4 || lpad('*', 500, '$')
5 || lpad('*', 500, '$')
6 || lpad('*', 500, '$')
7 || lpad('*', 500, '$')
8 || lpad('*', 500, '$')
9 || lpad('*', 500, '$')
10* || lpad('*', 500, '$'))
JLCHIAPPA@dwpan:SQL>/
insert into T_LONG values (2, lpad('*', 500, '$')
*
ERRO na linha 1:
ORA-01489: result of string concatenation is too long
The other language present in any Oracle db is PL/SQL, and for PL/SQL the limit for a string is 32767 bytes (and is documented, too), SO in PL/SQL I can concat too BUT only until this limit, see :
==> directly in SQL (even SQL enacpsuled in PL/SQL, no way) :
JLCHIAPPA@dwpan:SQL>l
1 BEGIN
2 insert into T_LONG values (3, lpad('*', 500, '$')
3 || lpad('*', 500, '$')
4 || lpad('*', 500, '$')
5 || lpad('*', 500, '$')
6 || lpad('*', 500, '$')
7 || lpad('*', 500, '$')
8 || lpad('*', 500, '$')
9 || lpad('*', 500, '$')
10 || lpad('*', 500, '$')
11 || lpad('*', 500, '$'));
12* END;
JLCHIAPPA@dwpan:SQL>/
BEGIN
*
ERROR
ORA-01489: result of string concatenation is too long
ORA-06512: at line 2
==> BUT, using a PL/SQL string variable (respecting the 32767 bytes PL/SQL limit), ok :
JLCHIAPPA@dwpan:SQL>DECLARE
2 v_plsql_string long := '*' || lpad('*', 500, '$')
3 || lpad('*', 500, '$')
4 || lpad('*', 500, '$')
5 || lpad('*', 500, '$')
6 || lpad('*', 500, '$')
7 || lpad('*', 500, '$')
8 || lpad('*', 500, '$')
9 || lpad('*', 500, '$')
10 || lpad('*', 500, '$')
11 || lpad('*', 500, '$');
12 BEGIN
13 insert into T_LONG values (3, v_plsql_string);
14* END;
JLCHIAPPA@dwpan:SQL>/
PL/SQL procedure successfully completed.
JLCHIAPPA@dwpan:SQL>
==> trying something bigger than 32767 bytes, no way in PL/SQL , see :
JLCHIAPPA@dwpan:SQL>l
1 DECLARE
2 v_plsql_string long := '*' || lpad('*', 4000, '$')
3 || lpad('*', 4000, '$')
4 || lpad('*', 4000, '$')
5 || lpad('*', 4000, '$')
6 || lpad('*', 4000, '$')
7 || lpad('*', 4000, '$')
8 || lpad('*', 4000, '$')
9 || lpad('*', 4000, '$')
10 || lpad('*', 4000, '$')
11 || lpad('*', 4000, '$')
12 ;
13 BEGIN
14 insert into T_LONG values (4, v_plsql_string);
15* END;
JLCHIAPPA@dwpan:SQL>/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2
=====>> so, going bigger than this in normally demands ANOTHER tool/language, not PL/SQL nor direct SQL... Granted, the LONG datatype goes until 4 Gb, BUT the SQL and PL/SQL languages have smaller limits , yes - IF you want more than this, forget SQL and PL/SQL, and go for Java and OCI-like tools , as pro*c ...
Knowing this (and a awful lot of OTHER problems with LONGs), Oracle DE-SUPORTED LONGs some years ago, and introduced LOBs, for LOBs yes, we have the WRITE_APPEND routine in the DBMS_LOB package , it permits writing to the full LOB size.
[]s
Chiappa
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|