This discussion is archived
9 Replies Latest reply: Jan 7, 2013 11:15 PM by 936666 RSS

SQL Error: ORA-01704: string literal too long

936666 Newbie
Currently Being Moderated
select * from table(fn_split('some 10000 characters with comma separation .........................'))

Error report:
SQL Error: ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.

how to pass my 10k record string
  • 1. Re: SQL Error: ORA-01704: string literal too long
    Rafu Journeyer
    Currently Being Moderated
    Change your comma separation to a table of structure
    http://www.oracle-developer.net/display.php?id=301
  • 2. Re: SQL Error: ORA-01704: string literal too long
    936666 Newbie
    Currently Being Moderated
    Hot to split a sting more than 4000 characters

    even i cant able to find the length of that string

    Select Length('some 5000 characters') from dual;

    Error:

    01704. 00000 - "string literal too long"
    *Cause:    The string literal is longer than 4000 characters.
    *Action:   Use a string literal of at most 4000 characters.
    Longer values may only be entered using bind variables.

    how to break my string which will fit 4000 characters alone.

    Thanks!
  • 3. Re: SQL Error: ORA-01704: string literal too long
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    Hot to split a sting more than 4000 characters

    even i cant able to find the length of that string

    Select Length('some 5000 characters') from dual;

    Error:

    01704. 00000 - "string literal too long"
    *Cause:    The string literal is longer than 4000 characters.
    *Action:   Use a string literal of at most 4000 characters.
    Longer values may only be entered using bind variables.
    SQL is limited to 4000 bytes for varchar2 strings.
    how to break my string which will fit 4000 characters alone.
    You could put you string into an editor and manually edit it to seperate data.
    The question is though, why do you have a string of comma seperated values that you need to have as a literal string? Why isn't this data residing in a database table or a CSV file (for which you could join to it as an external table)?

    You could also use CLOB datatype to store lots of data, but that itself can introduce further issues and are not so easily used as VARCHAR2.

    Please explain the issue you are trying to resolve and not the problem you are trying to fix.
  • 4. Re: SQL Error: ORA-01704: string literal too long
    971895 Journeyer
    Currently Being Moderated
    Can you post your exact query...
  • 5. Re: SQL Error: ORA-01704: string literal too long
    936666 Newbie
    Currently Being Moderated
    I'm having a string of length or nearly 10k i need to split that string and store those data into my table so i have called a function which will split the string and store the data in the table.

    Thanks!
  • 6. Re: SQL Error: ORA-01704: string literal too long
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    I'm having a string of length or nearly 10k
    And where is that string now? In a file? Being input through a user interface? In notepad? Where exactly... it has to be somewhere.

    The best place for it will be a file, so that you can use an external table to read the data easily.
    i need to split that string and store those data into my table so i have called a function which will split the string and store the data in the table.
    Yes, but your function only deals with VARCHAR2 and that is limited to 4000 bytes when used through SQL (if it were just within PL/SQL you could have up to 32767 bytes).
  • 7. Re: SQL Error: ORA-01704: string literal too long
    936666 Newbie
    Currently Being Moderated
    The string is through a user interface.
    create or replace TYPE Obj_split_Table_column AS OBJECT (Column_name varchar2(20000));
    
    create or replace type  split_table_column IS TABLE OF Obj_split_Table_column;
    
    create or replace function fn_split
    (
        p_list varchar2,
        p_del varchar2 := ','
    ) return split_table_column pipelined
    is
    --- select * from table(fn_split('2,4,2,5,7'));
        l_idx    pls_integer;
        l_list    varchar2(32767) := p_list;
        l_value    varchar2(32767);
    begin
        loop
            l_idx := instr(l_list,p_del);
            if l_idx > 0 then
                PIPE ROW(Obj_split_Table_column(substr(l_list,1,l_idx-1)));
                l_list := substr(l_list,l_idx+length(p_del));
            else
            PIPE ROW(Obj_split_Table_column(l_list));
                exit;
            end if;
        end loop;
        return;
    end fn_split;
    how to convert it to pl/sql format

    insert query will look some thing like.

    insert into table
    select * from table(fn_split('2,4,2,5,7'));


    Thanks!
  • 8. Re: SQL Error: ORA-01704: string literal too long
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    The string is through a user interface.
    So, that interface is using what datatype for the string? What language is the interface written in?
    insert into table
    select * from table(fn_split('2,4,2,5,7'));
    Do you understand what a string literal is? You cannot provide a varchar2 string that exceeds 4000 bytes from within SQL. Fact. It just cannot be done.

    If you are passing the string from a user interface using a datatype that supports more than 4000 bytes, and you pass it directly to PL/SQL code by calling the function or procedure directly (not using SQL) then you can use up to 32767 bytes for your VARCHAR2.

    The code you've posted (which happens to be some of my own code posted years ago on these forums) takes a VARCHAR2 as an input. You would have to change that to accept a CLOB datatype and work on the CLOB instead. However, you still wouldn't be able to pass in a string literal of more than 4000 bytes from SQL for it.

    Looking at your other thread: Seperate the string value

    ... it looks like the 'user' is trying to pass in a table definition. What is it your application is trying to do? Surely you are not trying to create a table at run time?

    So explain, what is the business issue you are trying to solve? We may be able to provide a better way of doing it.
  • 9. Re: SQL Error: ORA-01704: string literal too long
    936666 Newbie
    Currently Being Moderated
    Thanks I removed the function and written a pl/sql stored procedure which solves my problem,in the stored procedure i implemented same logic which has been implemented in function and inserted into my table.

    Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points