More on ORDS 3.0.10 and Auto PLSQL – PACKAGES

We released a new version of ORDS this week, and I blogged about a new feature in that release – Auto PLSQL.

In that post, I briefly mentioned packages, but I wanted to take a quick moment to show that ‘live.’

So assuming you have HR installed somewhere and HR is REST enabled, let’s build this package.

CREATE OR REPLACE PACKAGE RESTY AS
    PROCEDURE give_raises (
        increase      IN NUMBER
       ,highest_emp   OUT SYS_REFCURSOR
    );
 
    FUNCTION managers ( dept   IN INTEGER ) RETURN SYS_REFCURSOR;
 
    PROCEDURE nothing;
 
    PROCEDURE overloaded ( x   IN DATE );
 
    PROCEDURE overloaded ( x   IN NUMBER );
 
END RESTY;
/
 
CREATE OR REPLACE PACKAGE BODY RESTY AS
 
    PROCEDURE give_raises (
        increase      IN NUMBER
       ,highest_emp   OUT SYS_REFCURSOR
    ) AS
        max_sal   NUMBER;
    BEGIN
        UPDATE hr.employees
            SET
                salary = salary * increase;
 
        COMMIT;
        SELECT MAX(salary)
        INTO
            max_sal
        FROM hr.employees;
 
        OPEN highest_emp FOR
            SELECT first_name
               ,   last_name
               ,   salary
               ,   COMMISSION_PCT
            FROM hr.employees
            WHERE salary   = max_sal;
 
    END give_raises;
 
    FUNCTION managers ( dept   IN INTEGER ) RETURN SYS_REFCURSOR AS
        d   SYS_REFCURSOR;
    BEGIN
        OPEN d FOR
            SELECT *
            FROM hr.employees
            WHERE employee_id IN (
                        SELECT DISTINCT
                               manager_id
                        FROM hr.employees
                    ) AND
                    department_id   = dept;
 
        RETURN d;
    END managers;
 
    PROCEDURE private_proc
        AS
    BEGIN
        NULL;
    END private_proc;
 
    PROCEDURE nothing
        AS
    BEGIN
        NULL;
    END nothing;
 
    PROCEDURE overloaded ( x   IN DATE )
        AS
    BEGIN
        NULL;
    END overloaded;
 
    PROCEDURE overloaded ( x   IN NUMBER )
        AS
    BEGIN
        NULL;
    END overloaded;
 
END RESTY;

And let’s REST enable it…here’s the code, but of course we could also use SQL Developer’s context menu to ‘Enable REST Service.’

how do we call these programs?

BEGIN

ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => ‘HR’,
p_object => ‘RESTY’,
p_object_type => ‘PACKAGE’,
p_object_alias => ‘resty’,
p_auto_rest_auth => FALSE);
commit;
END;

And now we have access to these POST Handlers.

Special Note: You’ll notice the patterns are UPPERCASE. If you try /resty/give_raises, you’ll get a 404. This is wrong in the DOCS – my fault, not theirs, so bear this in mind when you start trying out the feature. Apologies and I’ll get this fixed ASAP.

Special Note #2: You’ll notice our overloaded procedure isn’t listed. That’s because they’re not supported.

Special Note #3: You’ll finally notice that the procedure PRIVATE_PROC is also not listed..because, it’s not available outside the package body.

You can still use overloaded PACKAGE members, you’ll just need to roll your own RESTful Service and use the Pl/SQL Source type and supply your own anonymous block to kick them off.

So let’s run one, NOTHING.

PROCEDURE nothing
        AS
    BEGIN
        NULL;
    END nothing;

Pretty simple, right?

POST /ords/peeps/resty/NOTHING HTTP/1.1
Host: localhost:8888
Content-Type: application/json
Cache-Control: no-cache
Postman-Token: 1ddfe469-22ba-2383-1c1b-a75f092d2ea6

Wait, why am I getting a 400?

Even though your package.procedure doesn’t have any parameters, we still need to include a POST BODY.

POST /ords/peeps/resty/NOTHING HTTP/1.1
Host: localhost:8888
Content-Type: application/json
Cache-Control: no-cache
Postman-Token: 1ddfe469-22ba-2383-1c1b-a75f092d2ea6

{
	
}

Note the ‘{ }’ on the POST body!

Let’s just run a procedure now.

Send in a number, get back a collection of records of the highest paid employee(s).

I let another incorrect bit of information into the docs, the URI’s for Auto PLSQL do NOT have trailing ‘/’s.

Apparently I need to go back to reading school.

I hope all this helps as we get the DOCS where they need to be.

Didn’t believe me on the overloaded procedures?

One Final Word

This feature is here to help you get your PL/SQL available via HTTP. It’s raison d’etre is

The sole intent for Auto Pl/SQL is for translating plsql in/out into binds.

This means if you’re doing something outside of passing data to PL/SQL and getting it back, or sending it on somewhere else…like say printing via HTP.PRN(), then you need to use a custom RESTful Service.