Hi All,
Orcl Ver:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I have a pipelined table function where I pass an identifier as input to the function and it returns the codes and values as output for that identifier in a collection.
I am trying to store these values in an associative array index by varchar2.
Here is the pipeline tabe function :
FUNCTION (get_fun(p_id IN NUMBER)
RETURN pipe_tab
PIPELINED IS
CURSOR cur
IS
SELECT code,
value
FROM test_tab
WHERE id = p_id;
BEGIN
For rec IN cur(p_id)
LOOP
PIPE ROW(pipe_type(rec.code,rec.value);
END LOOP;
END;
Above function is called from a procedure and below is the procedure
BEGIN
For rec IN (SELECT code,
value
FROM table(get_fun(1))
LOOP
lv_tab(rec.code) := rec.value
END LOOP;
INSERT INTO TABLE1(col1,col2)
SELECT lv_tab('CODE1')
,lv_tab('CODE2')
FROM dual;
END;
When I am trying to insert into a table ,I am getting NO_DATA_FOUND Exception because there is no data for lv_tab(CODE2).
I have hardcoded lv_tab(CODE2) because that CODE is dynamic,it might be present for some ids and not for some ids.
Is there any way to handle it in the select sql itself.
Thx,
Shan