Announcing ORDS 3.0.10 and a new feature – Auto PLSQL

thatjeffsmith ORDS 4 Comments

Tell Others About This Story:

New Oracle REST Data Services (ORDS) drop today.

Per usual, mostly bug fixes

And per usual, sometimes we like to sneak in new features as well, and this release is no different.

You can now have ORDS automatically create POST handlers for executing your PL/SQL.

This is a LOW CODE solution.

You could of course ALWAYS roll your own RESTful Service to run your PLSQL, and in many cases, you’ll continue to want to do this.

But today, you’ll ALSO be able to enable your PL/SQL programs, just like you’ve been able to do so for your TABLEs and VIEWs, a la

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_object => 'FOO_PLUS',
                       p_object_type => 'PROCEDURE',
                       p_object_alias => 'foo_plus',
                       p_auto_rest_auth => FALSE);
 
    COMMIT;
 
END;

What’s this mean, exactly?

It’s basically a remote procedure call (RPC) scheme over HTTP(S) via ORDS. Where this gets INTERESTING is where ORDS has all of JSON to Oracle data types and back down to JSON for when you want to call the PL/SQL and then get the data (if there is any) back out.

Let’s take a look.

First let’s create a simple table.

 
CREATE TABLE "SIMPLE_EMP" 
   (	"ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE  NOT NULL ENABLE, 
	"NAME" VARCHAR2(256 BYTE) NOT NULL ENABLE, 
	"SALARY" NUMBER(*,0) NOT NULL ENABLE, 
	"HIRE_DATE" TIMESTAMP (6), 
	 CONSTRAINT "SIMPLE_EMP_PK" PRIMARY KEY ("ID"));
 
REM INSERTING INTO SIMPLE_EMP
SET DEFINE OFF;
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Mike',188,to_timestamp('21-JAN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Joel',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Shaq',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Bob',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Bart',0,to_timestamp('20-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('David',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Rene',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Andres',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Andre',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Bobby',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
COMMIT;

Here’s the code.

CREATE OR REPLACE PROCEDURE give_raises (
    how_much        IN NUMBER DEFAULT 0.50
   ,total_payroll   OUT NUMBER
   ,peeps_numbers   OUT SYS_REFCURSOR
)
    AS
BEGIN
    UPDATE simple_emp
        SET
            salary = salary * how_much
    WHERE
        UPPER(name) NOT LIKE '%JEFF%';
 
    COMMIT;
    SELECT SUM(salary)
    INTO
        total_payroll
    FROM
        simple_emp;
 
    OPEN peeps_numbers FOR
        SELECT *
        FROM
            simple_emp
        ORDER BY HIRE_DATE DESC;
 
END give_raises;

So let’s register the program with ORDS – I’ll use SQL Developer.

This generates the PL/SQL block to register the program in the ORDS metadata schema – make sure to ALIAS and SECURE your code!

Now let’s run it.

To run a stored procedure via the Auto PL/SQL ORDS POST handler, you’ll need to send any input parameters via JSON in the request header. It’s a very simple format, just send up the input parameter names and values.

Any output will come back.

I’m expecting to send up a number, and get back another number and a list of my employees and how much money they’re making now via a REFCURSOR.

Remember to set your mime type to application/json

Now I’m returning a refcursor, and IN that refcursor is a TIMESTAMP. Working with dates and timestamps in Oracle and then down to JSON isn’t fun. But that’s OK, ORDS is handling all of that FOR YOU. Nice? NICE.

Here’s the pretty JSON from my OUT parameters….my output will be a bit longer than yours, I didn’t share all my records.

{
  "total_payroll": 631230,
  "peeps_numbers": [
    {
      "id": 81,
      "name": "Dummy4",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 65,
      "name": "Bart",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 79,
      "name": "Dummy2",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 78,
      "name": "Dumm2",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 77,
      "name": "Dumm2",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 76,
      "name": "Dumm2y",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 75,
      "name": "Dummy",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 113,
      "name": "RUSTY5",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 112,
      "name": "RUSTY4",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 111,
      "name": "RUSTY3",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 110,
      "name": "RUSTY2",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 109,
      "name": "RUSTY",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 143,
      "name": "baby",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 82,
      "name": "Dummy5",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 80,
      "name": "Dummy3",
      "salary": 0,
      "hire_date": "2017-06-20T13:29:00Z"
    },
    {
      "id": 23,
      "name": "Barry",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 41,
      "name": "Joyce",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 62,
      "name": "Joel",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 63,
      "name": "Shakeebl",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 64,
      "name": "Shakeeb",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 145,
      "name": "WTFF",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 108,
      "name": "johnny",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 114,
      "name": "johnny",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 144,
      "name": "peter",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 116,
      "name": "daniel",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 117,
      "name": "wtf",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 118,
      "name": "wtff",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 119,
      "name": "wtfff",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 120,
      "name": "wtfffff",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 42,
      "name": "Dermot",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 24,
      "name": "John",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 146,
      "name": "WTFF",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 74,
      "name": "Bobby",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 73,
      "name": "Bob",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 72,
      "name": "Bob",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 71,
      "name": "Bob",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 70,
      "name": "Bob",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 69,
      "name": "Andres",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 68,
      "name": "Andres",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 67,
      "name": "Rene",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 66,
      "name": "David",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 122,
      "name": "wtfffffffff",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 141,
      "name": "wtfffffffffff",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 121,
      "name": "wtfffffff",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 142,
      "name": "wtffffffffffffff",
      "salary": 1,
      "hire_date": "2017-06-19T13:29:00Z"
    },
    {
      "id": 104,
      "name": "BrerRabbit",
      "salary": 3,
      "hire_date": "2017-06-18T13:29:00Z"
    },
    {
      "id": 103,
      "name": "BillyJimBob",
      "salary": 3,
      "hire_date": "2017-06-18T13:29:00Z"
    },
    {
      "id": 151,
      "name": "ThisIsATest",
      "salary": 3,
      "hire_date": "2017-06-18T13:29:00Z"
    },
    {
      "id": 101,
      "name": "BillyBob",
      "salary": 3,
      "hire_date": "2017-06-18T13:29:00Z"
    },
    {
      "id": 102,
      "name": "JimBob",
      "salary": 3,
      "hire_date": "2017-06-18T13:29:00Z"
    },
    {
      "id": 150,
      "name": "ThisIsATest",
      "salary": 3,
      "hire_date": "2017-06-18T13:29:00Z"
    },
    {
      "id": 147,
      "name": "ThisIsATest",
      "salary": 3,
      "hire_date": "2017-06-18T13:29:00Z"
    },
    {
      "id": 107,
      "name": "RESTY",
      "salary": 6,
      "hire_date": "2017-06-15T13:29:00Z"
    },
    {
      "id": 1,
      "name": "JEFF",
      "salary": 10,
      "hire_date": "2017-06-10T13:29:00Z"
    },
    {
      "id": 115,
      "name": "jeffrey",
      "salary": 12,
      "hire_date": "2017-06-08T13:29:00Z"
    },
    {
      "id": 164,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 162,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 152,
      "name": "JeffreyDSmith",
      "salary": 25,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 153,
      "name": "JeffreyDSmith",
      "salary": 25,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 154,
      "name": "JeffreyDSmith",
      "salary": 25,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 161,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 156,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 157,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 158,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 159,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 160,
      "name": "Kristopher",
      "salary": 31,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 155,
      "name": "JeffreyDSmith",
      "salary": 25,
      "hire_date": "2017-05-26T13:29:00Z"
    },
    {
      "id": 169,
      "name": "Topher2",
      "salary": 38,
      "hire_date": "2017-05-21T13:29:00Z"
    },
    {
      "id": 170,
      "name": "Topher3",
      "salary": 38,
      "hire_date": "2017-05-21T13:29:00Z"
    },
    {
      "id": 167,
      "name": "Topher",
      "salary": 38,
      "hire_date": "2017-05-21T13:29:00Z"
    },
    {
      "id": 21,
      "name": "Kris",
      "salary": 63,
      "hire_date": "2017-05-01T13:29:00Z"
    },
    {
      "id": 106,
      "name": "RESTPM",
      "salary": 63,
      "hire_date": "2017-05-01T13:29:00Z"
    },
    {
      "id": 61,
      "name": "Mike",
      "salary": 188,
      "hire_date": "2017-01-21T14:29:00Z"
    },
    {
      "id": 83,
      "name": "Ric",
      "salary": 625,
      "hire_date": "2016-02-06T14:29:00Z"
    },
    {
      "id": 201,
      "name": "Topher3",
      "salary": 750,
      "hire_date": "2015-10-29T13:29:00Z"
    },
    {
      "id": 221,
      "name": "Topher",
      "salary": 750,
      "hire_date": "2015-10-29T13:29:00Z"
    },
    {
      "id": 241,
      "name": "Topher3",
      "salary": 750,
      "hire_date": "2015-10-29T13:29:00Z"
    },
    {
      "id": 181,
      "name": "Jeffrey Dan",
      "salary": 2500,
      "hire_date": "2010-08-16T13:29:00Z"
    },
    {
      "id": 105,
      "name": "RESTPM",
      "salary": 625000,
      "hire_date": "0648-07-04T14:29:00Z"
    }
  ]
}

Actually, there’s only really one ‘gotcha.’

We don’t support overloaded PL/SQL package functions or procedures. You can still use those, but you’ll need to roll your own RESTful Services if you want to call them. If you try them with the Auto PLSQL feature, you’ll get a 404.

Tell Others About This Story:

Comments 4

  1. ​Good Morning,

    ​I am new to ORDS and using as standlone mode using sqldeveloper 17.2 and ORDS 3.0.11 versions.

    ​Please guide me to enable the rest data service for package and packaged procedure function.

    ​I am able to auto enbale the package and not able to use the packaged procedure. I have refered all relevant document available on ORACLE.

    ​I would highliy thankfull for your help.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *