9 Replies Latest reply: Jan 8, 2013 1:15 AM by 936666 RSS

    SQL Error: ORA-01704: string literal too long

    936666
      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
          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
            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
              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
                Can you post your exact query...
                • 5. Re: SQL Error: ORA-01704: string literal too long
                  936666
                  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
                    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
                      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
                        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
                          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!