Slide 1

Slide 1 text

Oracle REST Data Services Everything you need to know about error messages Distinguished Product Manager [email protected] Tweets: @thatjeffsmith Blogs: https://www.thatjeffsmith.com Jeff Smith Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Slide 2

Slide 2 text

APEX/PLSQL Gateway REST APIs SQL Developer Web Database Management SODA for REST ORDS…? Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Slide 3

Slide 3 text

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.

Slide 4

Slide 4 text

• Python Web Apps • Spring Boot • NodeJS • Golang • Django… REST APIs are popular with Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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.

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Database vs HTTP Errors Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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)

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

Forcing DB Errors to the ‘Screen’ Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. true

Slide 16

Slide 16 text

Handling Errors w/Exceptions Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Slide 17

Slide 17 text

Handling Errors w/Exceptions Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Slide 18

Slide 18 text

Quit printing HTML already Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. json Accept: application/json

Slide 19

Slide 19 text

End of Presentation

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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.