Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Managing Error Messages with your Oracle Database REST APIs

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

thatjeffsmith

June 28, 2022
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

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

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

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

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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)

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  19. End of Presentation

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide