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.
Why do you run the query for each value? You can do it much more efficient like this:
CREATE OR REPLACE TYPE configcategoryarr IS TABLE OF VARCHAR2(256);
CREATE OR REPLACE PROCEDURE get_configurations (
t_configcategory IN configcategoryarr,
c_configurations OUT SYS_REFCURSOR) IS
BEGIN
OPEN c_configurations FOR
SELECT
configcategory,
configid,
configlabel,
configvalue,
configenabled
FROM configurations
WHERE configcategory MEMBER OF t_configcategory;
END get_configurations;