CODEWITHSUNDEEP
oracle-databaseoracle-ords
David Min
David Min

Reputation: 1454

Oracle ORDS REST - how to return collection query for a handler in PL/SQL mode?

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?

Upvotes: 2

Views: 1317

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22457

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.

enter image description here

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

Upvotes: 5

Related Questions