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.