How to output array data from a function in Oralce PL/SQL-CodePudding

I have a PL/SQL package that contains a procedure that I need to use to validate my result.

An example of this can look like this:

create or replace PACKAGE    MY_TEST
IS
   PROCEDURE validate_my_value (p_input_value      INT,
                                p_valid        OUT INT);
END;

create or replace PACKAGE BODY    MY_TEST
IS
   PROCEDURE validate_my_value (p_input_value       INT,
                                p_valid         OUT INT)
   IS
   BEGIN
       p_valid := 0;
       IF MOD(p_input_value, 2) = 0 THEN
          p_valid := 1;
       END IF;
  END;  
END;

I would like to call this procedure a number of times, and return the valid results in a sys_refcursor from a function on another PL/SQL package.

I have an example where I collect the valid results in an array, but in the end I cannot seem to get the output format quite right.

Example:

create or replace PACKAGE    MY_TEST_CLIENT
    IS
       FUNCTION get_validated_values (p_input_begin  INT,
                                      p_input_end    INT)
                                    RETURN sys_refcursor;
    
    END;
    
    
create or replace PACKAGE BODY    MY_TEST_CLIENT
IS
    TYPE t_num_array IS TABLE OF INTEGER;

    FUNCTION get_validated_values (p_input_begin  INT,
                                   p_input_end    INT)
                                   RETURN sys_refcursor
    IS
        l_current_value  INT;
        l_valid          INT;
        l_result_cursor  sys_refcursor;
        l_result         t_num_array := new t_num_array();
        l_count          INT := 1;
    BEGIN
        l_valid := 0;
        l_current_value := p_input_begin;

        LOOP
            MY_TEST.VALIDATE_MY_VALUE(l_current_value, l_valid);
            IF l_valid = 1 THEN
                l_result.extend();
                l_result(l_result.count) := l_current_value;
            END IF;
            EXIT WHEN l_current_value >= p_input_end;
            l_current_value := l_current_value   1;
        END LOOP;

        IF l_result.count() = 0 THEN
            return l_result_cursor;
        END IF;

       -- TODO convert l_result into l_result_cursor
       open l_result_cursor for
        -- SELECT * FROM TABLE(l_result); -- This does not work. ORA-22905 and ORA-00642
        select 1 from dual; -- Dummy value
        return l_result_cursor;
    END;
END;

And finally I would like to be able to call the method with something like this:

SELECT MY_TEST_CLIENT.get_validated_values(1,10) from DUAL;

So, based on the example here, the validator should only result in equal numbers. E.g.: 2, 4, 6, 8 and 10.

I tried something similar to Oracle: How to populate/insert row to a Ref Cursor? but cannot get the output cursor to accept a result.

I also tried to loop through the result and create a single SQL statement (select value from dual union ...). However, I had no luck executing the generated SQL statement in a way that left me with a result that could be returned by the sys_refcursor.

Any pointers on how to outputting the result would be appreciated.

CodePudding user response：

On order to use SELECT * FROM TABLE(l_result) you must create the type as database object, i.e.

CREATE TYPE t_num_array IS TABLE OF INTEGER;

instead of defining it inside the PL/SQL package.

NB, you can get the same result with this "one-liner":

open l_result_cursor for
with t as
   (select rownum   p_input_begin as r
   from dual
   connect by rownum <= p_input_end 1)
select r
from t
where mod(r,2)= 0;

CodePudding user response：

@Wernfried gave the clue to why I was having issues with my initial question, and adding an type to the database helped being able to make the SELET * FROM TABLE(l_result); work.

create or replace TYPE t_num_array AS TABLE OF INTEGER;

However, the pipeline suggestion by @Justin actually made more sense in this case. So, with pipelined output, the solution may looks like this:

create or replace PACKAGE BODY    MY_TEST_CLIENT2
IS
    FUNCTION get_validated_values (p_input_begin  INT,
                                   p_input_end    INT)
                                    return t_num_array pipelined 
    IS
        l_current_value  INT;
        l_valid          INT;
    BEGIN
        l_valid := 0;
        l_current_value := p_input_begin;

        LOOP
            MY_TEST.VALIDATE_MY_VALUE(l_current_value, l_valid);
            IF l_valid = 1 THEN
                pipe row (l_current_value);
            END IF;
            EXIT WHEN l_current_value >= p_input_end;
            l_current_value := l_current_value   1;
        END LOOP;
        RETURN;
    END;
END;

And calling it will do this:

SELECT MY_TEST_CLIENT2.get_validated_values(1,10) from DUAL;
-- TT.T_INTEGER_ARRAY(2, 4, 6, 8, 10)

Or using the suggesting by @Justing

SELECT * from table(MY_TEST_CLIENT2.get_validated_values(1,10));
/*
2
4
6
8
10
*/