1

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.

2
  • What do you want your query to return? The SELECT ... FROM dual you posted, assuming everything works, would return a single row. That row would have a single column of data type sys_refcursor. Your client application could then fetch data from that sys_refcursor but that wouldn't be particularly "normal". My guess is that you really want a pipelined table function where you'd run select ... from table( my_table_function(1,10) and get multiple rows of numeric output. But I don't know whether you strictly need the results in a sys_refcursor. Commented Dec 5, 2021 at 22:10
  • Thanks for pointing out the pipelined table. I think that was exactly what I wanted. Commented Dec 6, 2021 at 11:08

2 Answers 2

1

@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 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
*/
Sign up to request clarification or add additional context in comments.

Comments

0

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;

1 Comment

The "type as database object" was what I was missing. With a "TABLE OF INTEGER" type I was able to get the correct result. SELECT MY_TEST_CLIENT.get_validated_values(1,10) from DUAL; -- {<COLUMN_VALUE=2>,<COLUMN_VALUE=4>,<COLUMN_VALUE=6>,<COLUMN_VALUE=8>,<COLUMN_VALUE=10>,}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.