Hi all,
I have a function (inner-function) that returns a table of object (one row or more), this function takes a parameter (id)
and returns a table of one row or more.
I need to use this same inner-function in a loop and fill the final table with the result of every inner-function call.
I created another function that calls the inner-function in a loop to fill the table of object,
still the return is one row!
( I tried reading more about FORALL and different BULK COLLECT techniques but it seems that either
I am missing the hole concept or I am doing a mistake... or both!
I appreciate your usual help or advice regarding the approach in general (re-using the function in a loop)
My example:
CREATE TABLE TEST_CASE
(
ID NUMBER,
NAME1 VARCHAR(10),
VALUE1 NUMBER
);
INSERT ALL
INTO TEST_CASE VALUES (1, 'TTT', 77)
INTO TEST_CASE VALUES (2, 'RRR', 737)
INTO TEST_CASE VALUES (3, 'TT', 477)
INTO TEST_CASE VALUES (4, 'HHH', 775)
INTO TEST_CASE VALUES (5, 'TFFFTT', 66)
INTO TEST_CASE VALUES (6, 'III', 18)
SELECT * FROM DUAL;
CREATE OR REPLACE TYPE OBJ_TEST_CASE AS OBJECT
(
ID NUMBER,
NAME1 VARCHAR(10),
VALUE1 NUMBER
);
CREATE TYPE TBL_TEST_CASE AS
TABLE OF OBJ_TEST_CASE;
-- A function that get the result for a single id as a table
CREATE OR REPLACE FUNCTION F_TEST_CASE
(
I_ID IN NUMBER
)
RETURN TBL_TEST_CASE AS
AGR_ACCOUNT_STATEMENT TBL_TEST_CASE;
BEGIN
SELECT OBJ_TEST_CASE(
ID ,
NAME1,
VALUE1
)
BULK COLLECT INTO AGR_ACCOUNT_STATEMENT
FROM TEST_CASE WHERE id = I_ID or id = i_id+1; --just a condition to make it return more than one row
RETURN (AGR_ACCOUNT_STATEMENT);
END ;
select * from table(F_TEST_CASE(2));
-- an array to hold a list of rows
CREATE OR REPLACE TYPE ARY_AGREEMENT AS VARRAY(200) OF NUMBER;
-- Using the single value function to return a table for many rows
CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
RETURN TBL_TEST_CASE AS
AGR_ACCOUNT_STATEMENT TBL_TEST_CASE;
VARY_AGREEMENT ARY_AGREEMENT:= ARY_AGREEMENT();
BEGIN
SELECT ID BULK COLLECT INTO VARY_AGREEMENT FROM TEST_CASE
WHERE VALUE1 > MYMAXVALUE
;
FOR I IN 1..VARY_AGREEMENT.COUNT LOOP
SELECT OBJ_TEST_CASE(
ID ,
NAME1,
VALUE1
)
BULK COLLECT INTO AGR_ACCOUNT_STATEMENT
FROM TABLE(F_TEST_CASE(VARY_AGREEMENT(I)));
END LOOP;
RETURN AGR_ACCOUNT_STATEMENT;
END;
SELECT * FROM TABLE(F_TEST_CASE_ALL(1));
--still a single row is returned not many! what am I doing wrong?