Home » SQL & PL/SQL » SQL & PL/SQL » Filling a table of object in function loop (11.0.2.10)
Filling a table of object in function loop [message #657523] Sun, 13 November 2016 03:49 Go to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Hi all,

I have a function that returns a table of object, this function takes a parameter (id)
and returns a table of a single row.
I need to use this same function to return the same table for more than one row.
I created another function that calls the sigle-row 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;

    RETURN (AGR_ACCOUNT_STATEMENT);
   END ;



-- 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(18));

--still a single row (the last row) is returned not many! what am I doing wrong? 


Many Thanks,
Ferro
Re: Filling a table of object in function loop [message #657524 is a reply to message #657523] Sun, 13 November 2016 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have a single row because you return a single object.

Your approach is wrong, you should return a REF CURSOR not a collection.
SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
  2      RETURN sys_refcursor AS
  3    c sys_refcursor;
  4  BEGIN
  5    open c for select id, name1, value1 from TEST_CASE where VALUE1 > MYMAXVALUE;
  6    return c;
  7  END;
  8  /

Function created.

SQL> var c refcursor
SQL> exec :c := F_TEST_CASE_ALL(18);

PL/SQL procedure successfully completed.

SQL> print c
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.
or (more difficult to handle):
SQL> select F_TEST_CASE_ALL(18) from dual;
F_TEST_CASE_ALL(18)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.

1 row selected.

The alternative is to use a pipelined function.
SQL> CREATE OR REPLACE TYPE OBJ_TEST_CASE AS OBJECT
  2  (
  3      ID NUMBER,
  4      NAME1 VARCHAR(10),
  5      VALUE1 NUMBER
  6  );
  7  /

Type created.

SQL> CREATE TYPE TBL_TEST_CASE AS
  2      TABLE OF OBJ_TEST_CASE;
  3  /

Type created.

SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
  2      RETURN TBL_TEST_CASE pipelined AS
  3  BEGIN
  4    for rec in (
  5      select id, name1, value1 from TEST_CASE where VALUE1 > MYMAXVALUE
  6    ) loop
  7      pipe row (OBJ_TEST_CASE(rec.id, rec.name1, rec.value1));
  8    end loop;
  9  END;
 10  /

Function created.

SQL> select * from TABLE(F_TEST_CASE_ALL(18));
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.
Re: Filling a table of object in function loop [message #657525 is a reply to message #657524] Sun, 13 November 2016 04:29 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Thanks a lot Michel, now I understand.
But which approach do you recommend?


Ferro
Re: Filling a table of object in function loop [message #657526 is a reply to message #657525] Sun, 13 November 2016 04:39 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Sorry Michel, but I still have an issue.
My objective is calling the single-row function inside the general function (never mind the example, the inner function does many calculations
and is tested and I dont want to change it).

So I need to work something like:

CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
    RETURN TBL_TEST_CASE pipelined AS
BEGIN
  for rec in (
    select id from TEST_CASE where VALUE1 > MYMAXVALUE
  ) loop
    pipe row  table(F_TEST_CASE(id));  -- <------ the call to the single-row function
  end loop;
END;

Thanks,
Ferro
Re: Filling a table of object in function loop [message #657528 is a reply to message #657526] Sun, 13 November 2016 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know why you want to loop on function calls instead of doing it the final function itself which would be more efficient.
Currently your F_TEST_CASE does not return the correct type and your "pipe row" is not correct:
SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE
  2      (
  3        I_ID IN NUMBER
  4      )
  5    RETURN OBJ_TEST_CASE AS
  6      AGR_ACCOUNT_STATEMENT OBJ_TEST_CASE;
  7    BEGIN
  8      SELECT OBJ_TEST_CASE(
  9                            ID ,
 10                            NAME1,
 11                            VALUE1
 12                           )
 13        INTO AGR_ACCOUNT_STATEMENT
 14        FROM TEST_CASE WHERE ID = I_ID;
 15
 16      RETURN (AGR_ACCOUNT_STATEMENT);
 17     END ;
 18  /

Function created.

SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
  2      RETURN TBL_TEST_CASE pipelined AS
  3  BEGIN
  4    for rec in (
  5      select id from TEST_CASE where VALUE1 > MYMAXVALUE
  6    ) loop
  7      pipe row  (F_TEST_CASE(rec.id));
  8    end loop;
  9  END;
 10  /

Function created.

SQL> select * from TABLE(F_TEST_CASE_ALL(18));
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.
Re: Filling a table of object in function loop [message #657548 is a reply to message #657528] Sun, 13 November 2016 23:11 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Hi Michel,

I would like to hear your technical advice regarding why I chose to call the single-row function.

I have an accounting statement that has 3 main parts (header row, body rows, and virtual balance rows) and a final query that combines all rows and does some summary and calculations.

I developed a single function to cover each of the 3 parts. For example the function that covers the header row, depends on several queries, each of them can take either an id of a single account or use IN CLAUSE for a list of accounts. In order not to repeat this clause more than once and affect query readability, I chose to write the function for a single row, and then call it in a loop to get rows for all needed accounts.

I hope I was clear, and appreciate you technical advice as always.

Thanks,
Ferro
Re: Filling a table of object in function loop [message #657560 is a reply to message #657548] Mon, 14 November 2016 05:02 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Hi All,

One more question as I have the same inquiry about an inner function that returns a table of more than one row.
in this case, the function (just as an example) should look like:

CREATE OR REPLACE FUNCTION F_TEST_CASE
    (
      I_ID IN NUMBER
    )
  RETURN TBL_TEST_CASE AS    <---- I have to use Table not object type
    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 ;


I have to use Table not object type, but when I call this inner function in the main function using a loop, still a single row returns.

Thanks,
Ferro
Re: Filling a table of object in function loop [message #657564 is a reply to message #657560] Mon, 14 November 2016 06:31 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Same reason, see the first line of my first post.
Same solution: use a pipelined function or return a REF CURSOR.

Previous Topic: Need to match combination string at database
Next Topic: merge statement problem
Goto Forum:
  


Current Time: Thu Jan 18 07:12:01 CST 2018

Total time taken to generate the page: 0.03625 seconds