AUTO vs ‘Bespoke’ REST Services

thatjeffsmith ORDS, SQL Developer 3 Comments

Tell Others About This Story:

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.

Low/No Code Solution. You get pretty much no say in the API and how the results/responses are generated.

But…I don’t want a ‘null’ in my JSON collection for value pair “commission_pct.” What’s an Oracle developer to do?

Had this debate with my wife on our new car…she let me win 🙂

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().

No more ‘nulls’ – make sure your data consumers understand what 0 means.

And the results.

Ta-da.

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.

first the employees w/o commission_pct values, then those that do have them – no dreaded NULLs.

{
    "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.

Related Posts

Tell Others About This Story:

Comments 3

  1. 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

    1. thatjeffsmith Post
      Author

Leave a Reply

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