I am creating an oracle stored procedure to fetch records from ‘configurations’ table.
This stored procedure accepts an input parameter of type ‘varray’ because we want to fetch records for multiple ‘configcategories’ at once.
Also, there is an out parameter of type ‘SYS_REFCURSOR’ as well.

With below test script I am able to pass inputs to stored procedure but not able to print the output.

Can you please help to extract values from ‘SYS_REFCURSOR’ and print them on sqldeveloper console.

Finally, this stored procedure will be invoked by application written in NodeJS.

Stored Procedure:

CREATE OR REPLACE TYPE configcategoryarr IS
    VARRAY(256) OF VARCHAR2(256);
/

CREATE OR REPLACE PROCEDURE get_configurations (
    t_configcategory  IN   configcategoryarr,
    c_configurations  OUT  SYS_REFCURSOR
) IS
BEGIN
    IF t_configcategory.count > 0 THEN
        FOR i IN t_configcategory.first..t_configcategory.last LOOP
            dbms_output.put_line(t_configcategory(i));
            OPEN c_configurations FOR SELECT
                                          configcategory,
                                          configid,
                                          configlabel,
                                          configvalue,
                                          configenabled
                                      FROM
                                          configurations
                                      WHERE
                                          configcategory = t_configcategory(i);

        END LOOP;

    END IF;
END get_configurations;
/

Stored Procedure Test Script:

SET SERVEROUTPUT ON;

DECLARE
    t_cca             configcategoryarr;
    l_cursor          SYS_REFCURSOR;
    l_configcategory  configurations.configcategory%TYPE;
    l_configid        configurations.configid%TYPE;
    l_configlabel     configurations.configlabel%TYPE;
    l_configvalue     configurations.configvalue%TYPE;
    l_configenabled   configurations.configenabled%TYPE;
BEGIN
    t_cca := configcategoryarr();
    t_cca.extend(10);
    t_cca(1) := 'Department';
    t_cca(2) := 'OU';
    get_configurations(t_configcategory => t_cca, c_configurations => l_cursor);
    LOOP
        FETCH l_cursor INTO
            l_configcategory,
            l_configid,
            l_configlabel,
            l_configvalue,
            l_configenabled;
        EXIT WHEN l_cursor%notfound;
        dbms_output.put_line(l_configcategory
                             || '_'
                             || l_configid
                             || '_'
                             || l_configlabel
                             || '_'
                             || l_configvalue
                             || '_'
                             || l_configenabled);

    END LOOP;

    CLOSE l_cursor;
END;
/

Output:

Department
OU


PL/SQL procedure successfully completed.