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