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.
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;
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 ','
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.