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.
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.
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"
}
]
}
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.
4 Comments
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.
POST /ords/schema/package/PROC
what are you using, and what are you getting when you try?
Very cool feature! Thanks for sharing.
You’re very welcome!