933663 wrote:SQL is limited to 4000 bytes for varchar2 strings.
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;
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.You could put you string into an editor and manually edit it to seperate data.
933663 wrote:And where is that string now? In a file? Being input through a user interface? In notepad? Where exactly... it has to be somewhere.
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.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).
how to convert it to pl/sql format
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;
933663 wrote:So, that interface is using what datatype for the string? What language is the interface written in?
The string is through a user interface.
insert into tableDo 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.
select * from table(fn_split('2,4,2,5,7'));