Managing Error Messages with your Oracle Database REST APIs

Your APIs call SQL or PL/SQL routines in the Oracle Database.

Sometimes something bad will happen. How do you communicate these problems to your API consumers?

How do you debug what's going wrong, so you can fix your APIs?

This shows all those and more for Oracle REST Data Services (ORDS).

Sourced from https://www.thatjeffsmith.com

Transcript

1. Oracle REST Data Services Everything you need to know about error messages Distinguished Product Manager Jeff.D.Smith@oracle.com Tweets: @thatjeffsmith Blogs: https://www.thatjeffsmith.com Jeff Smith Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
2. APEX/PLSQL Gateway REST APIs SQL Developer Web Database Management SODA for REST ORDS…? Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
3. If you build it, they will come “it” = User Friendly APIs“they” = Developers APIs come first, making all resources available Developers then build their apps around your APIs Add value for your customers & partners API Driven Development Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
4. • Python Web Apps • Spring Boot • NodeJS • Golang • Django… REST APIs are popular with Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
5. • HTTPS is compatible with any: • programming language (even Fortran!) • framework (Angular, React.js, jQuery…) • environment (GUIs like Postman and CLI via cURL) • No Oracle Home to install • No Oracle drivers to configure Easier Oracle Access Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
6. • Insert some data? SQL INSERT • Collect statistics? PL/SQL DBMS_STATS If you can write SQL, you can build REST APIs! Oracle Speaks SQL & PLSQL Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
7. HTTPS > SQL > HTTPS GET https://host/ords/hr/emps/1 SELECT * FROM EMPS WHERE ID = 1 HTTP/1.1 200 OK { "id": 1, "name": "Jeff", "job": "PM", "salary": 100 } URI SQL & PLSQL Marshalls to Database HTTP Request HTTP Response Transforms to JSON SQL Result Set / Standard OUT SQL HTTPS {JSON} Oracle REST Data Service (ORDS) Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
8. When things go, less than good Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired ORA-01722 invalid number ORA-12505 Cannot connect to database. SID %s is not registered with the listener at %s. (CONNECTION_ID=%s) ORA-00904 %s: invalid identifier ORA-12560 TNS:protocol adapter error ORA-01017 invalid username/password; logon denied ORA-28040 The database does not accept your client's authentication protocol; login denied. ORA-12541 TNS:no listener ORA-06502 PL/SQL: numeric or value error%s ORA-00979 %s: does not match a GROUP BY expression ORA-30926 Attempted to update the same row. ORA-01034 ORACLE not available ORA-01861 literal does not match format string ORA-65096 invalid common user or role name ORA-01103 database name '%s' in control file is not '%s' ORA-01830 Date format picture ends before converting entire input string. ORA-01843 An invalid month was specified. ORA-01000 maximum open cursors exceeded ORA-01652 unable to extend temp segment by %s in tablespace %s ORA-00933 SQL command not properly ended ORA-01950 no privileges on tablespace '%s' ORA-01775 looping chain of synonyms ORA-01461 A LONG value referenced in an insert statement did not correspond to a column of type LONG. ORA-00932 inconsistent datatypes: expected %s got %s ORA-00907 missing right parenthesis ORA-00942 table or view does not exist ORA-12170 TNS:Connect timeout occurred ORA-00972 identifier is too long ORA-01653 unable to extend table %s.%s by %s in tablespace %s ORA-06512 at %sline %s ORA-12154 TNS:could not resolve the connect identifier specified ORA-28000 The account is locked. ORA-29283 invalid file operation%s ORA-00918 column ambiguously defined ORA-00911 invalid character ORA-01882 time zone region not found ORA-01031 insufficient privileges ORA-00001 unique constraint (%s.%s) violated ORA-28001 The password has expired. ORA-04031 unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\") ORA-01422 exact fetch returns more than requested number of rows ORA-01438 value larger than specified precision allowed for this column ORA-01858 A non-numeric character was found instead of a numeric character. ORA-01019 unable to allocate %s bytes of memory ORA-00604 error occurred at recursive SQL level %s ORA-01008 not all variables bound ORA-03113 end-of-file on communication channel ORA-12516 TNS:listener could not find available handler with matching protocol stack ORA-01747 invalid column specification: %s
9. Remember the friendly part? Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. • Trap expected, common errors • Return friendly messages/guidance • Make your APIs inviting, not head-scratching
10. Let’s talk about Errors Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. • Dealing with Database Errors • How to debug • How to avoid • HTTP 500 vs 555 • Returning JSON vs HTML
11. Database vs HTTP Errors Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
12. Dealing with Database Errors Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. • Database Errors should be limited to server logs • End users should only see Application or HTTP Errors • 500’s are unhandled Oracle (Back-End) Errors
13. 500’s vs 555’s Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. • 500 -> General problem with the database / ORDS • 555 -> Problem with your database code (Handler)
14. Backtracking the Logs Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. • Database errors go to ‘back-end ORDS server logs’ • Users only see HTTP Errors
15. Forcing DB Errors to the ‘Screen’ Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. <entry key="debug.printDebugToScreen">true</entry>
16. Handling Errors w/Exceptions Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
17. Handling Errors w/Exceptions Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
18. Quit printing HTML already Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. <entry key="error.reponseFormat">json</entry> Accept: application/json
19. End of Presentation
20. • ORDS Docs – Developer’s Guide • Blogs Debugging ORDS Handling 500’s More on Handling 500’s Resources Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
21. Modern Tooling Empowers Users, Increases Productivity Use the right tool for the right job Desktop REST APIs CLI WEB Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.