Hi All,I got a problem with oracle database ,i created a stored procedure and i wanted to pass an array of items' ids to this procedure to select the data according to array of items using "in" clause,the available solution to this as i found was to create a function and pass a string value with all item's ids seperated by a comma ,and this function will return a datatble with a row for each item id.this approach works fine when i try it in toad in a select statement,,but when i use it in the stored procedure i get a strange error
"ORA-12714: invalid national character set specified"
after searching about the reason of that error i found that it is a bug in that version of oracle according to this page and it was fixed in a 10.2.0.4 oracle patch and the exact reason is to declare a cursor for the function that returns a data table
As it is impossible to me to let the users who work on a live production environment to stop the servers to apply the update patch ,I was wondering if any Oracle expert can help me to declare a cursor and return that cursor instead of returning the table.
my Oracle function,Thanks in Advance
create or replace
FUNCTION SplitIDs(
v_List IN VARCHAR2)
RETURN RtnValue_Set PIPELINED
AS
SWV_List VARCHAR2(2000);
v_RtnValue Dt_RtnValue := Dt_RtnValue(NULL);
BEGIN
SWV_List := v_List;
WHILE (instr(SWV_List,',') > 0)
LOOP
FOR RetRow IN
(SELECT ltrim(rtrim(SUBSTR(SWV_List,1,instr(SWV_List,',') -1))) SelectedValue
FROM dual
)
LOOP
v_RtnValue.SelectedValue := RetRow.SelectedValue;
PIPE ROW(v_RtnValue);
END LOOP;
SWV_List := SUBSTR(SWV_List,instr(SWV_List,',')+LENGTH(','),LENGTH(SWV_List));
END LOOP;
FOR RetRow IN
(SELECT ltrim(rtrim(SWV_List)) SelectedValue FROM dual
)
LOOP
v_RtnValue.SelectedValue := RetRow.SelectedValue;
PIPE ROW(v_RtnValue);
END LOOP;
RETURN;
END;