The Automatic REST features in ORDS are very handy for getting started quickly with providing REST APIs to your data and stored procedures.
Give me the data for an employee.
Easy.
REST enable the schema.
REST enable the table.
DO a GET on /ords/schema/table/id.
But…I don’t want a ‘null’ in my JSON collection for value pair “commission_pct.” What’s an Oracle developer to do?
Code/Roll your own REST Service.
The ‘bespoke’ solution took me about 3 minutes to implement using SQL Developer.
I’m not a big fan of SELECT * FROMs…even when I need all the columns. Also, I might need to change the order of the data being presented, or change how the data is being presented, like..with a NVL().
And the results.
Missing the {links}? We’re in bespoke mode, so you just got to code ’em yourself.
But I don’t want to see COMM_PCT AT ALL if it’s NULL, and I don’t want to ‘Fake It’ With a 0
Then we can write some pl/sql.
But it’s Sunday and I need to take the kids out for a bike ride, so I’ll get back to you on that.
OK, bike ride over. Now let’s look at ONE way to do this.
A stored procedure that returns 2 SYS_REFCURSORs.
CREATE OR REPLACE PROCEDURE getemployee_nulls ( emps OUT SYS_REFCURSOR, empsnocomms OUT SYS_REFCURSOR ) AS BEGIN OPEN emps FOR SELECT * FROM employees WHERE commission_pct IS NOT NULL; OPEN empsnocomms FOR SELECT employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, manager_id, department_id FROM employees WHERE commission_pct IS NULL; END getemployee_nulls;
Now, while this might be somewhat bespoke, I’m also going to take advantage of the AUTO feature we have for PL/SQL. While it’s not technically REST, being able to affect a RPC using HTTP and a POST without having to write any code is quite nice.
So, REST enable the stored procedure.
BEGIN ORDS.ENABLE_OBJECT(p_enabled => TRUE, p_schema => 'HR', p_object => 'GETEMPLOYEE_NULLS', p_object_type => 'PROCEDURE', p_object_alias => 'getemployee_nulls', p_auto_rest_auth => FALSE); COMMIT; END;
Now, we can POST to /getemployee_nulls/ – ORDS will auto-grab the 2 REFCURSORs, and auto-format them back to {json} for us.
Here’s the call:
curl -X POST \ http://localhost:8080/ords/hr/getemployee_nulls/ \ -H 'cache-control: no-cache' \ -H 'content-type: application/json' \ -H 'postman-token: 497c499a-2bf8-39fc-d261-ab9f9391a2f0' \ -d ' { }'
And here’s the output:
Well, here’s a preview of the output, and the full response is below that.
{ "empsnocomms": [ { "employee_id": 100, "first_name": "50", "last_name": "King", "email": "SKING", "phone_number": "515.123.4567", "hire_date": "1987-06-17T04:00:00Z", "job_id": "AD_PRES", "salary": 24000, "manager_id": null, "department_id": 90 }, { "employee_id": 101, "first_name": "Neena", "last_name": "Kochhar", "email": "NKOCHHAR", "phone_number": "515.123.4568", "hire_date": "1989-09-21T04:00:00Z", "job_id": "AD_VP", "salary": 17000, "manager_id": 100, "department_id": 90 }, { "employee_id": 102, "first_name": "Lex", "last_name": "De Haan", "email": "LDEHAAN", "phone_number": "515.123.4569", "hire_date": "1993-01-13T05:00:00Z", "job_id": "AD_VP", "salary": 17000, "manager_id": 100, "department_id": 90 }, { "employee_id": 103, "first_name": "Alexander", "last_name": "Hunold", "email": "AHUNOLD", "phone_number": "590.423.4567", "hire_date": "1990-01-03T05:00:00Z", "job_id": "IT_PROG", "salary": 9000, "manager_id": 102, "department_id": 60 }, { "employee_id": 104, "first_name": "Bruce", "last_name": "Ernst", "email": "BERNST", "phone_number": "590.423.4568", "hire_date": "1991-05-21T04:00:00Z", "job_id": "IT_PROG", "salary": 6000, "manager_id": 103, "department_id": 60 }, { "employee_id": 105, "first_name": "David", "last_name": "Austin", "email": "DAUSTIN", "phone_number": "590.423.4569", "hire_date": "1997-06-25T04:00:00Z", "job_id": "IT_PROG", "salary": 4800, "manager_id": 103, "department_id": 60 }, { "employee_id": 106, "first_name": "Valli", "last_name": "Pataballa", "email": "VPATABAL", "phone_number": "590.423.4560", "hire_date": "1998-02-05T05:00:00Z", "job_id": "IT_PROG", "salary": 4800, "manager_id": 103, "department_id": 60 }, { "employee_id": 107, "first_name": "Diana", "last_name": "Lorentz", "email": "DLORENTZ", "phone_number": "590.423.5567", "hire_date": "1999-02-07T05:00:00Z", "job_id": "IT_PROG", "salary": 4200, "manager_id": 103, "department_id": 60 }, { "employee_id": 108, "first_name": "Nancy", "last_name": "Greenberg", "email": "NGREENBE", "phone_number": "515.124.4569", "hire_date": "1994-08-17T04:00:00Z", "job_id": "FI_MGR", "salary": 12000, "manager_id": 101, "department_id": 100 }, { "employee_id": 109, "first_name": "Daniel", "last_name": "Faviet", "email": "DFAVIET", "phone_number": "515.124.4169", "hire_date": "1994-08-16T04:00:00Z", "job_id": "FI_ACCOUNT", "salary": 9000, "manager_id": 108, "department_id": 100 }, { "employee_id": 110, "first_name": "John", "last_name": "Chen", "email": "JCHEN", "phone_number": "515.124.4269", "hire_date": "1997-09-28T04:00:00Z", "job_id": "FI_ACCOUNT", "salary": 8200, "manager_id": 108, "department_id": 100 }, { "employee_id": 111, "first_name": "Ismael", "last_name": "Sciarra", "email": "ISCIARRA", "phone_number": "515.124.4369", "hire_date": "1997-09-30T04:00:00Z", "job_id": "FI_ACCOUNT", "salary": 7700, "manager_id": 108, "department_id": 100 }, { "employee_id": 112, "first_name": "Jose Manuel", "last_name": "Urman", "email": "JMURMAN", "phone_number": "515.124.4469", "hire_date": "1998-03-07T05:00:00Z", "job_id": "FI_ACCOUNT", "salary": 7800, "manager_id": 108, "department_id": 100 }, { "employee_id": 113, "first_name": "Luis", "last_name": "Popp", "email": "LPOPP", "phone_number": "515.124.4567", "hire_date": "1999-12-07T05:00:00Z", "job_id": "FI_ACCOUNT", "salary": 6900, "manager_id": 108, "department_id": 100 }, { "employee_id": 114, "first_name": "Den", "last_name": "Raphaely", "email": "DRAPHEAL", "phone_number": "515.127.4561", "hire_date": "1994-12-07T05:00:00Z", "job_id": "PU_MAN", "salary": 11000, "manager_id": 100, "department_id": 30 }, { "employee_id": 115, "first_name": "Alexander", "last_name": "Khoo", "email": "AKHOO", "phone_number": "515.127.4562", "hire_date": "1995-05-18T04:00:00Z", "job_id": "PU_CLERK", "salary": 3100, "manager_id": 114, "department_id": 30 }, { "employee_id": 116, "first_name": "Shelli", "last_name": "Baida", "email": "SBAIDA", "phone_number": "515.127.4563", "hire_date": "1997-12-24T05:00:00Z", "job_id": "PU_CLERK", "salary": 2900, "manager_id": 114, "department_id": 30 }, { "employee_id": 117, "first_name": "Sigal", "last_name": "Tobias", "email": "STOBIAS", "phone_number": "515.127.4564", "hire_date": "1997-07-24T04:00:00Z", "job_id": "PU_CLERK", "salary": 2800, "manager_id": 114, "department_id": 30 }, { "employee_id": 118, "first_name": "Guy", "last_name": "Himuro", "email": "GHIMURO", "phone_number": "515.127.4565", "hire_date": "1998-11-15T05:00:00Z", "job_id": "PU_CLERK", "salary": 2600, "manager_id": 114, "department_id": 30 }, { "employee_id": 119, "first_name": "Karen", "last_name": "Colmenares", "email": "KCOLMENA", "phone_number": "515.127.4566", "hire_date": "1999-08-10T04:00:00Z", "job_id": "PU_CLERK", "salary": 2500, "manager_id": 114, "department_id": 30 }, { "employee_id": 120, "first_name": "Matthew", "last_name": "Weiss", "email": "MWEISS", "phone_number": "650.123.1234", "hire_date": "1996-07-18T04:00:00Z", "job_id": "ST_MAN", "salary": 8000, "manager_id": 100, "department_id": 50 }, { "employee_id": 121, "first_name": "50", "last_name": "Fripp", "email": "AFRIPP", "phone_number": "650.123.2234", "hire_date": "1997-04-10T04:00:00Z", "job_id": "ST_MAN", "salary": 8200, "manager_id": 100, "department_id": 50 }, { "employee_id": 122, "first_name": "Payam", "last_name": "Kaufling", "email": "PKAUFLIN", "phone_number": "650.123.3234", "hire_date": "1995-05-01T04:00:00Z", "job_id": "ST_MAN", "salary": 7900, "manager_id": 100, "department_id": 50 }, { "employee_id": 123, "first_name": "Shanta", "last_name": "Vollman", "email": "SVOLLMAN", "phone_number": "650.123.4234", "hire_date": "1997-10-10T04:00:00Z", "job_id": "ST_MAN", "salary": 6500, "manager_id": 100, "department_id": 50 }, { "employee_id": 124, "first_name": "Kevin", "last_name": "Mourgos", "email": "KMOURGOS", "phone_number": "650.123.5234", "hire_date": "1999-11-16T05:00:00Z", "job_id": "ST_MAN", "salary": 5800, "manager_id": 100, "department_id": 50 }, { "employee_id": 125, "first_name": "Julia", "last_name": "Nayer", "email": "JNAYER", "phone_number": "650.124.1214", "hire_date": "1997-07-16T04:00:00Z", "job_id": "ST_CLERK", "salary": 3200, "manager_id": 120, "department_id": 50 }, { "employee_id": 126, "first_name": "Irene", "last_name": "Mikkilineni", "email": "IMIKKILI", "phone_number": "650.124.1224", "hire_date": "1998-09-28T04:00:00Z", "job_id": "ST_CLERK", "salary": 2700, "manager_id": 120, "department_id": 50 }, { "employee_id": 127, "first_name": "James", "last_name": "Landry", "email": "JLANDRY", "phone_number": "650.124.1334", "hire_date": "1999-01-14T05:00:00Z", "job_id": "ST_CLERK", "salary": 2400, "manager_id": 120, "department_id": 50 }, { "employee_id": 128, "first_name": "Steven", "last_name": "Markle", "email": "SMARKLE", "phone_number": "650.124.1434", "hire_date": "2000-03-08T05:00:00Z", "job_id": "ST_CLERK", "salary": 2200, "manager_id": 120, "department_id": 50 }, { "employee_id": 129, "first_name": "Laura", "last_name": "Bissot", "email": "LBISSOT", "phone_number": "650.124.5234", "hire_date": "1997-08-20T04:00:00Z", "job_id": "ST_CLERK", "salary": 3300, "manager_id": 121, "department_id": 50 }, { "employee_id": 130, "first_name": "Mozhe", "last_name": "Atkinson", "email": "MATKINSO", "phone_number": "650.124.6234", "hire_date": "1997-10-30T05:00:00Z", "job_id": "ST_CLERK", "salary": 2800, "manager_id": 121, "department_id": 50 }, { "employee_id": 131, "first_name": "James", "last_name": "Marlow", "email": "JAMRLOW", "phone_number": "650.124.7234", "hire_date": "1997-02-16T05:00:00Z", "job_id": "ST_CLERK", "salary": 2500, "manager_id": 121, "department_id": 50 }, { "employee_id": 132, "first_name": "TJ", "last_name": "Olson", "email": "TJOLSON", "phone_number": "650.124.8234", "hire_date": "1999-04-10T04:00:00Z", "job_id": "ST_CLERK", "salary": 2100, "manager_id": 121, "department_id": 50 }, { "employee_id": 133, "first_name": "Jason", "last_name": "Mallin", "email": "JMALLIN", "phone_number": "650.127.1934", "hire_date": "1996-06-14T04:00:00Z", "job_id": "ST_CLERK", "salary": 3300, "manager_id": 122, "department_id": 50 }, { "employee_id": 134, "first_name": "Michael", "last_name": "Rogers", "email": "MROGERS", "phone_number": "650.127.1834", "hire_date": "1998-08-26T04:00:00Z", "job_id": "ST_CLERK", "salary": 2900, "manager_id": 122, "department_id": 50 }, { "employee_id": 135, "first_name": "Ki", "last_name": "Gee", "email": "KGEE", "phone_number": "650.127.1734", "hire_date": "1999-12-12T05:00:00Z", "job_id": "ST_CLERK", "salary": 2400, "manager_id": 122, "department_id": 50 }, { "employee_id": 136, "first_name": "Hazel", "last_name": "Philtanker", "email": "HPHILTAN", "phone_number": "650.127.1634", "hire_date": "2000-02-06T05:00:00Z", "job_id": "ST_CLERK", "salary": 2200, "manager_id": 122, "department_id": 50 }, { "employee_id": 137, "first_name": "Renske", "last_name": "Ladwig", "email": "RLADWIG", "phone_number": "650.121.1234", "hire_date": "1995-07-14T04:00:00Z", "job_id": "ST_CLERK", "salary": 3600, "manager_id": 123, "department_id": 50 }, { "employee_id": 138, "first_name": "Stephen", "last_name": "Stiles", "email": "SSTILES", "phone_number": "650.121.2034", "hire_date": "1997-10-26T04:00:00Z", "job_id": "ST_CLERK", "salary": 3200, "manager_id": 123, "department_id": 50 }, { "employee_id": 139, "first_name": "John", "last_name": "Seo", "email": "JSEO", "phone_number": "650.121.2019", "hire_date": "1998-02-12T05:00:00Z", "job_id": "ST_CLERK", "salary": 2700, "manager_id": 123, "department_id": 50 }, { "employee_id": 140, "first_name": "Joshua", "last_name": "Patel", "email": "JPATEL", "phone_number": "650.121.1834", "hire_date": "1998-04-06T04:00:00Z", "job_id": "ST_CLERK", "salary": 2500, "manager_id": 123, "department_id": 50 }, { "employee_id": 141, "first_name": "Trenna", "last_name": "Rajs", "email": "TRAJS", "phone_number": "650.121.8009", "hire_date": "1995-10-17T04:00:00Z", "job_id": "ST_CLERK", "salary": 3500, "manager_id": 124, "department_id": 50 }, { "employee_id": 142, "first_name": "Curtis", "last_name": "Davies", "email": "CDAVIES", "phone_number": "650.121.2994", "hire_date": "1997-01-29T05:00:00Z", "job_id": "ST_CLERK", "salary": 3100, "manager_id": 124, "department_id": 50 }, { "employee_id": 143, "first_name": "Randall", "last_name": "Matos", "email": "RMATOS", "phone_number": "650.121.2874", "hire_date": "1998-03-15T05:00:00Z", "job_id": "ST_CLERK", "salary": 2600, "manager_id": 124, "department_id": 50 }, { "employee_id": 144, "first_name": "Peter", "last_name": "Vargas", "email": "PVARGAS", "phone_number": "650.121.2004", "hire_date": "1998-07-09T04:00:00Z", "job_id": "ST_CLERK", "salary": 2500, "manager_id": 124, "department_id": 50 }, { "employee_id": 180, "first_name": "Winston", "last_name": "Taylor", "email": "WTAYLOR", "phone_number": "650.507.9876", "hire_date": "1998-01-24T05:00:00Z", "job_id": "SH_CLERK", "salary": 3200, "manager_id": 120, "department_id": 50 }, { "employee_id": 181, "first_name": "Jean", "last_name": "Fleaur", "email": "JFLEAUR", "phone_number": "650.507.9877", "hire_date": "1998-02-23T05:00:00Z", "job_id": "SH_CLERK", "salary": 3100, "manager_id": 120, "department_id": 50 }, { "employee_id": 182, "first_name": "Martha", "last_name": "Sullivan", "email": "MSULLIVA", "phone_number": "650.507.9878", "hire_date": "1999-06-21T04:00:00Z", "job_id": "SH_CLERK", "salary": 2500, "manager_id": 120, "department_id": 50 }, { "employee_id": 183, "first_name": "Girard", "last_name": "Geoni", "email": "GGEONI", "phone_number": "650.507.9879", "hire_date": "2000-02-03T05:00:00Z", "job_id": "SH_CLERK", "salary": 2800, "manager_id": 120, "department_id": 50 }, { "employee_id": 184, "first_name": "Nandita", "last_name": "Sarchand", "email": "NSARCHAN", "phone_number": "650.509.1876", "hire_date": "1996-01-27T05:00:00Z", "job_id": "SH_CLERK", "salary": 4200, "manager_id": 121, "department_id": 50 }, { "employee_id": 185, "first_name": "Alexis", "last_name": "Bull", "email": "ABULL", "phone_number": "650.509.2876", "hire_date": "1997-02-20T05:00:00Z", "job_id": "SH_CLERK", "salary": 4100, "manager_id": 121, "department_id": 50 }, { "employee_id": 186, "first_name": "Julia", "last_name": "Dellinger", "email": "JDELLING", "phone_number": "650.509.3876", "hire_date": "1998-06-24T04:00:00Z", "job_id": "SH_CLERK", "salary": 3400, "manager_id": 121, "department_id": 50 }, { "employee_id": 187, "first_name": "Anthony", "last_name": "Cabrio", "email": "ACABRIO", "phone_number": "650.509.4876", "hire_date": "1999-02-07T05:00:00Z", "job_id": "SH_CLERK", "salary": 3000, "manager_id": 121, "department_id": 50 }, { "employee_id": 188, "first_name": "Kelly", "last_name": "Chung", "email": "KCHUNG", "phone_number": "650.505.1876", "hire_date": "1997-06-14T04:00:00Z", "job_id": "SH_CLERK", "salary": 3800, "manager_id": 122, "department_id": 50 }, { "employee_id": 189, "first_name": "Jennifer", "last_name": "Dilly", "email": "JDILLY", "phone_number": "650.505.2876", "hire_date": "1997-08-13T04:00:00Z", "job_id": "SH_CLERK", "salary": 3600, "manager_id": 122, "department_id": 50 }, { "employee_id": 190, "first_name": "Timothy", "last_name": "Gates", "email": "TGATES", "phone_number": "650.505.3876", "hire_date": "1998-07-11T04:00:00Z", "job_id": "SH_CLERK", "salary": 2900, "manager_id": 122, "department_id": 50 }, { "employee_id": 191, "first_name": "Randall", "last_name": "Perkins", "email": "RPERKINS", "phone_number": "650.505.4876", "hire_date": "1999-12-19T05:00:00Z", "job_id": "SH_CLERK", "salary": 2500, "manager_id": 122, "department_id": 50 }, { "employee_id": 192, "first_name": "Sarah", "last_name": "Bell", "email": "SBELL", "phone_number": "650.501.1876", "hire_date": "1996-02-04T05:00:00Z", "job_id": "SH_CLERK", "salary": 4000, "manager_id": 123, "department_id": 50 }, { "employee_id": 193, "first_name": "Britney", "last_name": "Everett", "email": "BEVERETT", "phone_number": "650.501.2876", "hire_date": "1997-03-03T05:00:00Z", "job_id": "SH_CLERK", "salary": 3900, "manager_id": 123, "department_id": 50 }, { "employee_id": 194, "first_name": "Samuel", "last_name": "McCain", "email": "SMCCAIN", "phone_number": "650.501.3876", "hire_date": "1998-07-01T04:00:00Z", "job_id": "SH_CLERK", "salary": 3200, "manager_id": 123, "department_id": 50 }, { "employee_id": 195, "first_name": "Vance", "last_name": "Jones", "email": "VJONES", "phone_number": "650.501.4876", "hire_date": "1999-03-17T05:00:00Z", "job_id": "SH_CLERK", "salary": 2800, "manager_id": 123, "department_id": 50 }, { "employee_id": 196, "first_name": "Alana", "last_name": "Walsh", "email": "AWALSH", "phone_number": "650.507.9811", "hire_date": "1998-04-24T04:00:00Z", "job_id": "SH_CLERK", "salary": 3100, "manager_id": 124, "department_id": 50 }, { "employee_id": 197, "first_name": "Kevin", "last_name": "Feeney", "email": "KFEENEY", "phone_number": "650.507.9822", "hire_date": "1998-05-23T04:00:00Z", "job_id": "SH_CLERK", "salary": 3000, "manager_id": 124, "department_id": 50 }, { "employee_id": 198, "first_name": "Donald", "last_name": "OConnell", "email": "DOCONNEL", "phone_number": "650.507.9833", "hire_date": "1999-06-21T04:00:00Z", "job_id": "SH_CLERK", "salary": 2600, "manager_id": 124, "department_id": 50 }, { "employee_id": 199, "first_name": "Douglas", "last_name": "Grant", "email": "DGRANT", "phone_number": "650.507.9844", "hire_date": "2000-01-13T05:00:00Z", "job_id": "SH_CLERK", "salary": 2600, "manager_id": 124, "department_id": 50 }, { "employee_id": 200, "first_name": "Jennifer", "last_name": "Whalen", "email": "JWHALEN", "phone_number": "515.123.4444", "hire_date": "1987-09-17T04:00:00Z", "job_id": "AD_ASST", "salary": 4000, "manager_id": 101, "department_id": 10 }, { "employee_id": 201, "first_name": "Michael", "last_name": "Hartstein", "email": "MHARTSTE", "phone_number": "515.123.5555", "hire_date": "1996-02-17T05:00:00Z", "job_id": "MK_MAN", "salary": 13000, "manager_id": 100, "department_id": 20 }, { "employee_id": 202, "first_name": "Pat", "last_name": "Fay", "email": "PFAY", "phone_number": "603.123.6666", "hire_date": "1997-08-17T04:00:00Z", "job_id": "MK_REP", "salary": 6000, "manager_id": 201, "department_id": 20 }, { "employee_id": 203, "first_name": "Susan", "last_name": "Mavris", "email": "SMAVRIS", "phone_number": "515.123.7777", "hire_date": "1994-06-07T04:00:00Z", "job_id": "HR_REP", "salary": 6500, "manager_id": 101, "department_id": 40 }, { "employee_id": 204, "first_name": "Hermann", "last_name": "Baer", "email": "HBAER", "phone_number": "515.123.8888", "hire_date": "1994-06-07T04:00:00Z", "job_id": "PR_REP", "salary": 10000, "manager_id": 101, "department_id": 70 }, { "employee_id": 205, "first_name": "Shelley", "last_name": "Higgins", "email": "SHIGGINS", "phone_number": "515.123.8080", "hire_date": "1994-06-07T04:00:00Z", "job_id": "AC_MGR", "salary": 12000, "manager_id": 101, "department_id": 110 }, { "employee_id": 206, "first_name": "William", "last_name": "Gietz", "email": "WGIETZ", "phone_number": "515.123.8181", "hire_date": "1994-06-07T04:00:00Z", "job_id": "AC_ACCOUNT", "salary": 8300, "manager_id": 205, "department_id": 110 } ], "emps": [ { "employee_id": 145, "first_name": "John", "last_name": "Russell", "email": "JRUSSEL", "phone_number": "011.44.1344.429268", "hire_date": "1996-10-01T04:00:00Z", "job_id": "SA_MAN", "salary": 14000, "commission_pct": 0.4, "manager_id": 100, "department_id": 80 }, { "employee_id": 146, "first_name": "Karen", "last_name": "Partners", "email": "KPARTNER", "phone_number": "011.44.1344.467268", "hire_date": "1997-01-05T05:00:00Z", "job_id": "SA_MAN", "salary": 13500, "commission_pct": 0.3, "manager_id": 100, "department_id": 80 }, { "employee_id": 147, "first_name": "Alberto", "last_name": "Errazuriz", "email": "AERRAZUR", "phone_number": "011.44.1344.429278", "hire_date": "1997-03-10T05:00:00Z", "job_id": "SA_MAN", "salary": 12000, "commission_pct": 0.3, "manager_id": 100, "department_id": 80 }, { "employee_id": 148, "first_name": "Gerald", "last_name": "Cambrault", "email": "GCAMBRAU", "phone_number": "011.44.1344.619268", "hire_date": "1999-10-15T04:00:00Z", "job_id": "SA_MAN", "salary": 11000, "commission_pct": 0.3, "manager_id": 100, "department_id": 80 }, { "employee_id": 149, "first_name": "Eleni", "last_name": "Zlotkey", "email": "EZLOTKEY", "phone_number": "011.44.1344.429018", "hire_date": "2000-01-29T05:00:00Z", "job_id": "SA_MAN", "salary": 10500, "commission_pct": 0.2, "manager_id": 100, "department_id": 80 }, { "employee_id": 150, "first_name": "Peter", "last_name": "Tucker", "email": "PTUCKER", "phone_number": "011.44.1344.129268", "hire_date": "1997-01-30T05:00:00Z", "job_id": "SA_REP", "salary": 10000, "commission_pct": 0.3, "manager_id": 145, "department_id": 80 }, { "employee_id": 151, "first_name": "David", "last_name": "Bernstein", "email": "DBERNSTE", "phone_number": "011.44.1344.345268", "hire_date": "1997-03-24T05:00:00Z", "job_id": "SA_REP", "salary": 9500, "commission_pct": 0.25, "manager_id": 145, "department_id": 80 }, { "employee_id": 152, "first_name": "Peter", "last_name": "Hall", "email": "PHALL", "phone_number": "011.44.1344.478968", "hire_date": "1997-08-20T04:00:00Z", "job_id": "SA_REP", "salary": 9000, "commission_pct": 0.25, "manager_id": 145, "department_id": 80 }, { "employee_id": 153, "first_name": "Christopher", "last_name": "Olsen", "email": "COLSEN", "phone_number": "011.44.1344.498718", "hire_date": "1998-03-30T05:00:00Z", "job_id": "SA_REP", "salary": 8000, "commission_pct": 0.2, "manager_id": 145, "department_id": 80 }, { "employee_id": 154, "first_name": "Nanette", "last_name": "Cambrault", "email": "NCAMBRAU", "phone_number": "011.44.1344.987668", "hire_date": "1998-12-09T05:00:00Z", "job_id": "SA_REP", "salary": 7500, "commission_pct": 0.2, "manager_id": 145, "department_id": 80 }, { "employee_id": 155, "first_name": "Oliver", "last_name": "Tuvault", "email": "OTUVAULT", "phone_number": "011.44.1344.486508", "hire_date": "1999-11-23T05:00:00Z", "job_id": "SA_REP", "salary": 7000, "commission_pct": 0.15, "manager_id": 145, "department_id": 80 }, { "employee_id": 156, "first_name": "Janette", "last_name": "King", "email": "JKING", "phone_number": "011.44.1345.429268", "hire_date": "1996-01-30T05:00:00Z", "job_id": "SA_REP", "salary": 10000, "commission_pct": 0.35, "manager_id": 146, "department_id": 80 }, { "employee_id": 157, "first_name": "Patrick", "last_name": "Sully", "email": "PSULLY", "phone_number": "011.44.1345.929268", "hire_date": "1996-03-04T05:00:00Z", "job_id": "SA_REP", "salary": 9500, "commission_pct": 0.35, "manager_id": 146, "department_id": 80 }, { "employee_id": 158, "first_name": "Allan", "last_name": "McEwen", "email": "AMCEWEN", "phone_number": "011.44.1345.829268", "hire_date": "1996-08-01T04:00:00Z", "job_id": "SA_REP", "salary": 9000, "commission_pct": 0.35, "manager_id": 146, "department_id": 80 }, { "employee_id": 159, "first_name": "Lindsey", "last_name": "Smith", "email": "LSMITH", "phone_number": "011.44.1345.729268", "hire_date": "1997-03-10T05:00:00Z", "job_id": "SA_REP", "salary": 8500, "commission_pct": 0.4, "manager_id": 146, "department_id": 80 }, { "employee_id": 160, "first_name": "Louise", "last_name": "Doran", "email": "LDORAN", "phone_number": "011.44.1345.629268", "hire_date": "1997-12-15T05:00:00Z", "job_id": "SA_REP", "salary": 7500, "commission_pct": 0.3, "manager_id": 146, "department_id": 80 }, { "employee_id": 161, "first_name": "Sarath", "last_name": "Sewall", "email": "SSEWALL", "phone_number": "011.44.1345.529268", "hire_date": "1998-11-03T05:00:00Z", "job_id": "SA_REP", "salary": 7000, "commission_pct": 0.25, "manager_id": 146, "department_id": 80 }, { "employee_id": 162, "first_name": "Clara", "last_name": "Vishney", "email": "CVISHNEY", "phone_number": "011.44.1346.129268", "hire_date": "1997-11-11T05:00:00Z", "job_id": "SA_REP", "salary": 10500, "commission_pct": 0.25, "manager_id": 147, "department_id": 80 }, { "employee_id": 163, "first_name": "Danielle", "last_name": "Greene", "email": "DGREENE", "phone_number": "011.44.1346.229268", "hire_date": "1999-03-19T05:00:00Z", "job_id": "SA_REP", "salary": 9500, "commission_pct": 0.15, "manager_id": 147, "department_id": 80 }, { "employee_id": 164, "first_name": "Mattea", "last_name": "Marvins", "email": "MMARVINS", "phone_number": "011.44.1346.329268", "hire_date": "2000-01-24T05:00:00Z", "job_id": "SA_REP", "salary": 7200, "commission_pct": 0.1, "manager_id": 147, "department_id": 80 }, { "employee_id": 165, "first_name": "David", "last_name": "Lee", "email": "DLEE", "phone_number": "011.44.1346.529268", "hire_date": "2000-02-23T05:00:00Z", "job_id": "SA_REP", "salary": 6800, "commission_pct": 0.1, "manager_id": 147, "department_id": 80 }, { "employee_id": 166, "first_name": "Sundar", "last_name": "Ande", "email": "SANDE", "phone_number": "011.44.1346.629268", "hire_date": "2000-03-24T05:00:00Z", "job_id": "SA_REP", "salary": 6400, "commission_pct": 0.1, "manager_id": 147, "department_id": 80 }, { "employee_id": 167, "first_name": "Amit", "last_name": "Banda", "email": "ABANDA", "phone_number": "011.44.1346.729268", "hire_date": "2000-04-21T04:00:00Z", "job_id": "SA_REP", "salary": 6200, "commission_pct": 0.1, "manager_id": 147, "department_id": 80 }, { "employee_id": 168, "first_name": "Lisa", "last_name": "Ozer", "email": "LOZER", "phone_number": "011.44.1343.929268", "hire_date": "1997-03-11T05:00:00Z", "job_id": "SA_REP", "salary": 11500, "commission_pct": 0.25, "manager_id": 148, "department_id": 80 }, { "employee_id": 169, "first_name": "Harrison", "last_name": "Bloom", "email": "HBLOOM", "phone_number": "011.44.1343.829268", "hire_date": "1998-03-23T05:00:00Z", "job_id": "SA_REP", "salary": 10000, "commission_pct": 0.2, "manager_id": 148, "department_id": 80 }, { "employee_id": 170, "first_name": "Tayler", "last_name": "Fox", "email": "TFOX", "phone_number": "011.44.1343.729268", "hire_date": "1998-01-24T05:00:00Z", "job_id": "SA_REP", "salary": 9600, "commission_pct": 0.2, "manager_id": 148, "department_id": 80 }, { "employee_id": 171, "first_name": "William", "last_name": "Smith", "email": "WSMITH", "phone_number": "011.44.1343.629268", "hire_date": "1999-02-23T05:00:00Z", "job_id": "SA_REP", "salary": 7400, "commission_pct": 0.15, "manager_id": 148, "department_id": 80 }, { "employee_id": 172, "first_name": "Elizabeth", "last_name": "Bates", "email": "EBATES", "phone_number": "011.44.1343.529268", "hire_date": "1999-03-24T05:00:00Z", "job_id": "SA_REP", "salary": 7300, "commission_pct": 0.15, "manager_id": 148, "department_id": 80 }, { "employee_id": 173, "first_name": "Sundita", "last_name": "Kumar", "email": "SKUMAR", "phone_number": "011.44.1343.329268", "hire_date": "2000-04-21T04:00:00Z", "job_id": "SA_REP", "salary": 6100, "commission_pct": 0.1, "manager_id": 148, "department_id": 80 }, { "employee_id": 174, "first_name": "Ellen", "last_name": "Abel", "email": "EABEL", "phone_number": "011.44.1644.429267", "hire_date": "1996-05-11T04:00:00Z", "job_id": "SA_REP", "salary": 11000, "commission_pct": 0.3, "manager_id": 149, "department_id": 80 }, { "employee_id": 175, "first_name": "Alyssa", "last_name": "Hutton", "email": "AHUTTON", "phone_number": "011.44.1644.429266", "hire_date": "1997-03-19T05:00:00Z", "job_id": "SA_REP", "salary": 8800, "commission_pct": 0.25, "manager_id": 149, "department_id": 80 }, { "employee_id": 176, "first_name": "Jonathon", "last_name": "Taylor", "email": "JTAYLOR", "phone_number": "011.44.1644.429265", "hire_date": "1998-03-24T05:00:00Z", "job_id": "SA_REP", "salary": 8600, "commission_pct": 0.2, "manager_id": 149, "department_id": 80 }, { "employee_id": 177, "first_name": "Jack", "last_name": "Livingston", "email": "JLIVINGS", "phone_number": "011.44.1644.429264", "hire_date": "1998-04-23T04:00:00Z", "job_id": "SA_REP", "salary": 8400, "commission_pct": 0.2, "manager_id": 149, "department_id": 80 }, { "employee_id": 178, "first_name": "Kimberely", "last_name": "Grant", "email": "KGRANT", "phone_number": "011.44.1644.429263", "hire_date": "1999-05-24T04:00:00Z", "job_id": "SA_REP", "salary": 7000, "commission_pct": 0.15, "manager_id": 149, "department_id": null }, { "employee_id": 179, "first_name": "Charles", "last_name": "Johnson", "email": "CJOHNSON", "phone_number": "011.44.1644.429262", "hire_date": "2000-01-04T05:00:00Z", "job_id": "SA_REP", "salary": 6200, "commission_pct": 0.1, "manager_id": 149, "department_id": 80 } ] }
Elegant? Not so much. But I’m not the SQL/PLSQL Guy Around Here
I’m guessing Chris or SF over on the AskTom team would have a fancy bit of SQL using a model clause or a pivot or even a UNION to give you something even better.
I bet they even already have an answer for you over there.
3 Comments
Thank you very much for your Time
Hi Jeff,
i think, it is not a Good idea to “fake it” with 0 Value. The Web Developer can unterstand it maybe wrong. imagine ‘null’ is a Object or a array. The Developer can code it wrong because you fake it with 0.
the SQl/Json handle it with ABSENT ON NULL (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-C0F8F837-EE36-4EDD-9261-6E8A9245906C).
The APEX_JSON.WRITE (https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29827) handle it too
Regards
Pierre
You’re probably right. I trust the developer to make the best decision for their use case.
We’ll have an option to suppress nulls in a future release.