I used a stored function which has a varchar2 parameter. The parameter is a id list, the format is like follows:
It's means there is a 4 id in this string,there is no delimiter before the first item and there is the delimiter after the last item. If there is only one item in this list, the famat is like follows:
If there is no item in the list, the list is ''
I want split the string, if the string is '12|23|45|67', I want to get an array variable which have 4 elements. The elements' type is varchar2, the value is '12','23','45' and '67' respectively.
U have 2 ways to solve ur problem:
list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT UNCL_ARRAY);
2. white ur own small program:
type tb_numbers is table of varchar2(100) index by binary integer;
nmt tb_numbers ;l
i := 0;
j := 1;
while insrt(str_temp.'|',i,1) > 0 loop
i := i +1;
nmt(i) := substr(tmp_str,j, insrt(str_temp.'|',i,1)-1);
j := insrt(str_temp.'|',i,1) + 1;
Since all of your data is not available to me, it is enough to say that you should use the functions SUBSTR and INSTR. If you have a copy of the Oracle Complete Reference, it will be very clear.
OCP DBA 8i, 9i, 10g
thanks, a another question
shestakov: thanks ,but I want to know how is the binary_integer? can you tell me the DBMS_UTILITY.COMMA_TO_TABLE details? I want to know the parameters' means.
dknight: I have no oracle complete reference ,can you tell me a url? in oracle , the document is online, I want to a zip package so I can download it on my local computer. By the way, I have no oracle CD
Binary_integer is a special numeric data type in PL/SQL.
U can use this type for indexing "in memory" tables (type tabe inPL/SQL)
list IN VARCHAR2, -- source string
tablen OUT BINARY_INTEGER, -- (return value) number of elements in table
tab OUT UNCL_ARRAY); -- result table(list) of varchar2 values.
comma_to_table use ',' as delimiter, u should replace ur'|' to ','
source_str varchar2(100) := '12|23|56|78';
dbms_utility.comma_to_table(replace(source_str,'|',','), ret_num, ret_tbl);
[Edited by Shestakov on 03-05-2002 at 05:17 PM]
thanks but still have anothor question
in comma_to_table, must the delimiter be the ","? If the there is a "," in the string? How I can do?
the sourse string: '12,34|23,34|34,23|'
if I replace "|" to ",", the string will convert to '12,34,23,34,34,23'.
The reason why I use the '|' as the delimiter is the '|' can not in the item.
dbms_utility.comma_to_table(replace(replace(source_str,',','@'),'|',','), ret_num, ret_tbl);
for i in ret_tbl.first..ret_tbl.last loop
ret_tbl(i) := replace(ret_tbl(i),'@',');
thank you but the excute efficiency?
of course I know this method, but I'm afraid the split time is too long. Because in the same time, the server will receive many requests. but I think I must do this used this method.
Click Here to Expand Forum to Full Width