A query can be returned in a "collection query" mode as a JSON, simply as SELECT * FROM SOMETABLE.

In PL/SQL however, this is not possible. How would the equivalent be implemented in this mode?

CodePudding user response：

Easiest way is to return a refcursor from your procedure, as a RESULTSET parameter type.

For example:

DECLARE
    cur SYS_REFCURSOR;
BEGIN
    OPEN cur FOR
        SELECT * FROM myTable ORDER BY id;
    :result := cur;
END;

With your OUT parameter set to bind result.

Example sample schema, data, and REST Code here - For a longer full example.