How do I insert long data to database?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How do I insert long data to database?

  1. #1
    Join Date
    Jan 2001
    Posts
    59

    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.


  2. #2
    Join Date
    Jan 2001
    Posts
    59

    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...

  3. #3
    Join Date
    Jan 2008
    Posts
    1

    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#' )

  4. #4
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350

    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
  •  



Click Here to Expand Forum to Full Width