PL\SQL function return table

I tried today to return a table from a PL\SQL function and than use the result in Java, using JDBC.

First, create a temporary table:

CREATE GLOBAL TEMPORARY TABLE CLIENTS
(
    FILE_LINE_NUMBER INTEGER, 
    CIF INTEGER
) ON COMMIT DELETE ROWS ;

Than we have to define a type, using the created table columns and a table, for the defined type:

CREATE TYPE CLIENTS_TYPE AS OBJECT (FILE_LINE_NUMBER INTEGER, CIF INTEGER);
CREATE TYPE CLIENTS_TABLE AS TABLE OF CLIENTS_TYPE;

And of course, define the function:

CREATE OR REPLACE FUNCTION GET_CIFS RETURN CLIENTS_TABLE PIPELINED AS 
TYPE T_REF_CURSOR IS REF CURSOR;    
LC_CIFS        T_REF_CURSOR;
LR_OUT_REC             CLIENTS_TYPE := CLIENTS_TYPE(NULL, NULL);
    
BEGIN
    OPEN LC_CIFS FOR 
        SELECT 
            FILE_LINE_NUMBER,
            CIF 
        FROM CLIENTS;
        
        LOOP
            FETCH LC_CIFS 
            INTO 
                LR_OUT_REC.FILE_LINE_NUMBER,    
                LR_OUT_REC.CIF;
            EXIT WHEN LC_CIFS%NOTFOUND;
            PIPE ROW(LR_OUT_REC);
        END LOOP;
    CLOSE LC_CIFS;
    RETURN;
END;

For testing the function from PL\SQL we may use:

INSERT INTO CLIENT_FOR_PROMO VALUES(10, 10);
INSERT INTO CLIENT_FOR_PROMO VALUES(100, 100);
INSERT INTO CLIENT_FOR_PROMO VALUES(43, 32);
SELECT * FROM TABLE(GET_CIFS());

Now, in Java we just have to create a JDBC statement containing the SELECT * FROM TABLE(GET_CIFS()); code, and iterate through the ResultSet.

Advertisements