SQL bulk collect and fetch

Question

I am writing a PLSQL function and I am not getting a return data from my nested table. Here is my code and it is returning zero data.

set SERVEROUTPUT ON; 

CREATE OR REPLACE FUNCTION GETSummary(startDate DATE, endDate DATE)
RETURN SUM_OBJ_TABLE AS   
                    CURSOR getId   IS
                           SELECT SUM_OBJ (EMP_DATE,
                                  WEEK_ID,
                                  MONTH_ID,
                                  YEAR_ID)

                           FROM ( SELECT 
                                  EMP_DATE,
                                  WEEK_ID,
                                  MONTH_ID,
                                  YEAR_ID

                           FROM EMPLOYEE);
                           WHERE EMP_DATE BETWEEN startDate AND endDate;

           result  SUM_OBJ_TABLE; 

BEGIN     
     OPEN getId;   
  LOOP 
      FETCH getId BULK COLLECT INTO result;

      EXIT WHEN result.COUNT = 0;
      END LOOP;   
     CLOSE getId;   

 RETURN result;    
END;

I have already created SUM_OBJ AND SUM_OBJECT_TABLE on the same database.

The function runs and compiles fine but when i do

SELECT * FROM TABLE(GETSUMMARY('2017-06-10','2017-06-16'))

then i get an empty row of the object table.

it is not a filter issue because data exist between the two parameters.

Pls check if the function is compiled properly. I cannot find EMP_DATE in the select from employee yet EMP_DATE is in the where clause. — jose_bacoy, Feb 18 '18 at 15:52
Is the syntax correct? am I transferring data correctly? is SELECT * FROM TABLE(GETSUMMARY('2017-06-10','2017-06-16')) a correct way to fetch the data? — tita, Feb 18 '18 at 15:58
`'2017-06-10'` and `'2017-06-16'` are not `DATE`s, but strings. `date '2017-06-10'` is a `DATE`. — Aleksej, Feb 18 '18 at 16:17
even if i take out the date criteria, lets assume there is no date filter and even if i remove the where clause, still wont return data. — tita, Feb 18 '18 at 16:56

score 1 · Answer 1 · answered Feb 18 '18 at 18:02

You have an error in your application logic. Your code keeps looping until the FETCH doesn't find anything. Then it exits, which means the returned value is an empty set (assuming the inconsistency between result and v_result is a typo).

You're not using the LIMIT clause so there is no need to have that loop. Given that your cursor actually finds records, this will return them.

CREATE OR REPLACE FUNCTION GETSummary
    (startDate DATE, endDate DATE)
    RETURN SUM_OBJ_TABLE 
AS   
            CURSOR getId   IS
                   SELECT SUM_OBJ (EMP_DATE,
                          WEEK_ID,
                          MONTH_ID,
                          YEAR_ID)
                   FROM ( SELECT 
                          EMP_DATE,
                          WEEK_ID,
                          MONTH_ID,
                          YEAR_ID
                   FROM EMPLOYEE);
                   WHERE EMP_DATE BETWEEN startDate AND endDate;
      v_result  SUM_OBJ_TABLE; 
BEGIN     
     OPEN getId;   
     FETCH getId BULK COLLECT INTO v_result;
     CLOSE getId;   

   RETURN v_result;    
END;

SQL bulk collect and fetch

1 Answers1