Using Oracle REST Data Services (ORDS) to create web services with query or PL/SQL back-end services can be a powerful way to access and manipulate data stored in an Oracle database. However, it is important to be aware that when defining the service handler source code, there is always the possibility of encountering an error at runtime which can be difficult to debug and resolve. This article explains how to use ChatGPT to suggest an explanation for the error reported at runtime.

1. What’s the problem?
2. Ask a chatbot
   1. Why ChatGPT?
   2. Get an API key
3. ORDS Custom Error Pages
   1. How the error page is rendered
   2. Custom error page for status 555 – User Defined Resource Error
   3. Configure the custom error pages location
4. ChatGPT: Explaining runtime errors
   1. Helpful explanations
5. Conclusion

What’s the problem?

ORDS is used to create, manage, and run RESTful web services. Developers can write their own REST APIs by simply providing the SQL or PL/SQL behind the endpoints. This is the module/template/handler paradigm mentioned in Developing Oracle REST Data Services Applications. It is always a possibility that when developing a service a mistake is made, or something changes in the system which breaks a service that was previously working. Diagnosing the problem, and then fixing it, is not always straight forward.

When an error occurs while executing the query or PL/SQL block and it appears to be due to the handler source then ORDS will return a HTTP 555 User Defined Resource Error response. This indicates that an error occurred, but it was not due to problems connecting to the database, or with authorisation, and therefore a problem with executing the handler source in the database. In other words, it is an issue that the developer of the handler should be able to address. However, there may not be any more information than that and a developer would have to understand the ORA error codes and error message mentioned by the database.

Having debug.printDebugToScreen enabled in the environment can provide more context but resolving the issue still requires familiarity with what the database is complaining about.

Ask a chatbot

A chatbot is a computer program that is designed to simulate conversation with human users through written or spoken messages. Chatbots are typically used in customer service, online helpdesks, and other online services to provide answers to common questions and help customers find the information they need. In this case the goal is to ask a chatbot for a suggested solution to the user defined resource error. More specifically, when displaying the error message to also display the answer to the question How can I fix this user defined resource error I encountered in Oracle REST Data Services? It’s not so much a back and forth conversation, but the first answer can be quite useful.

Why ChatGPT?

The chatbot used in this article is ChatGPT, specifically: gpt-3.5-turbo, but in theory one can integrate with any AI model sufficiently broad enough that can handle conversations around SQL and PL/SQL development. ChatGPT is a natural language processing (NLP) model developed by OpenAI that uses deep learning to generate human-like responses to a given input. The model is quite accurate and can generate realistic responses that mimic how a human would converse. To over simplify it, based on the huge amount of conversations it has seen before, the model predicts what’s the next word to output when generating text. ChatGPT is not perfect and could in fact return a vague or inaccurate answer but in the majority of cases, the answer is helpful.

Get an API key

To interact with the OpenAI chat API to ask a question of the ChatGPT model an API key is required. Get your own API key at https://platform.openai.com/account/api-keys. You will need it later in the custom error page.

ORDS Custom Error Pages

ORDS allows users to customise the appearance of error pages that are displayed when an error occurs. Custom error pages can be defined to include text, graphics, and HTML elements that can be used to provide helpful information to users when an error occurs. In the majority of cases where ORDS customers configure custom error pages it is generally to have specific branding in place and perhaps additional links for their own project description, documentation and support.

How the error page is rendered

The error page in ORDS is a moustache template file which is used to generate a HTML response based on a few parameters. It is worthwhile getting an understanding of the moustache syntax. However, the simplest explanation of its use in this case is that certain portions of the static HTML template file are replaced with parameters at runtime. These parameters have specific names:

• statusCode – The HTTP status code to display. Custom error pages filenames also correspond to status code but more about that later.
• statusMessage – The text that corresponds to the statusCode.
• reasons – A HTML block of List Items ( <li>...</li> ) with text descriptions of one or more reasons for the error reported.
• unauthorized – A HTML block with a link to the sign-in page if the error is an authentication or authorisation error.
• debug – A HTML block for displaying the debug.printDebugToScreen output.

Not all of the above parameters are set at runtime but for HTTP 555 User Defined Resource Error the statusCode, statusMessage and reasons are. As mentioned above, the reasons parameter is a HTML block which will look like this:

<li>
  <span style="font-size: 1.1em;">
The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 1422, Error Message: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
  </span>
</li>

In the ORDS distribution one can review the error.html template file that is used by default. It can be found in the ords-http-${build.number}.jar and I have used it as the basis for my custom error page for 555 status codes 555.html. That’s why it looks like the standard ORDS error page, but with one new text area.

Custom error page for status 555 – User Defined Resource Error

At a high level the additional changes to the out of the box error.html is a javascript function called getExplanation() which is called when the error page is loaded by the browser. This function calls an openai.com endpoint for chat completion. To get a further understanding of the model, the parameters in the request payload and the format of the response it is best to start with the above OpenAI documentation. It is sufficient to know that the getExplanation() javascript function in the custom error page starts a conversation with the model as a user, specifying that the model can lean slightly in the deterministic direction. That’s what the temperature parameter is for.

var content = 'How can I fix this user defined resource error I encountered in Oracle REST Data Services? ' + reasonsSingleLine.trim() + '.';
var params = `{ "model": "gpt-3.5-turbo", "messages": 
                [{"role": "user", 
                  "content": "${content}"}
                ],
                "temperature": 0.7 }`;

The javascript invokes https://api.openai.com/v1/chat/completions endpoint with a question based on the reasons text and displays the response in a textarea element called explanation. Before making that call, the reasons text is restructured to be a single line string and the HTML elements are removed. This makes it easier for ChatGPT to parse the content of the message. You can walk through the code in your own time.

Download 555.html and save it in a directory that ORDS will have access to at runtime. Let’s say it is /path/to/error/pages/folder/ because that’s the example used in the ORDS documentation. Remember that OpenAI API key that was created earlier? Edit the 555.html file and find this section at line #133..

var openapi_key = '<!-- PUT YOUR OPENAI.COM API KEY HERE -->';

Replace the HTML comment portion with your OpenAI API key. Note that this key will be accessible to anyone who accesses your ORDS REST Services and gets a HTTP 555 response. It is possible that someone may use your key for their own API calls to OpenAI. Therefore it may be necessary to change the keys on a regular basis or perhaps only use this approach for the development and internal production environments but not for an environment accessible from the internet.

Configure the custom error pages location

When the error.externalPath configuration setting has a value ORDS uses that to find a customer error page template file based on the status code of the response. If there is no corresponding status code HTML file then the default error page is used. Before starting up the ORDS instance set the error.externalPath configuration setting to the directory that the 555.html customer error page is.

ords --config /path/to/conf config set error.externalPath /path/to/error/pages/folder/

See Configuring the Custom Error Pages section in ORDS documentation.

ChatGPT: Explaining runtime errors

With the custom error page in place for 555 status codes we can revisit our example ORDS REST service endpoint that has an error at runtime and see a slightly different error page.

The request to the OpenAI endpoint occurs when the page is loaded by the browser. There is a noticeable delay with getting a response back. For three requests the response time recorded by the browser for https://api.openai.com/v1/chat/completions were ( in seconds ) : 15, 8, 10. That’s a huge variation in elapsed time and anything more than a few seconds can seem like an eternity when sitting at a computer. It’s still faster than the length of time it takes to copy and past the message into your preferred search engine and trawl through the pages for an helpful explanation and actionable suggestions.

Helpful explanations

Of course this is a dynamic conversation with a model which works through a plethora of options and permutations so the next time it is asked for suggestions on how to fix the problem there could be a different answer.

For more deterministic output consider reducing the temperature parameter in the javascript but having a variety of suggestions could in fact be more helpful when trying to solve a problem.

Conclusion

The use of ChatGPT to produce explanations and suggested fixes for user defined resource errors is a useful aid for developers creating ORDS REST Services. It is an easy-to-use and efficient solution for automatically diagnosing and resolving errors. Even working with a small error description, through the use of natural language processing, ChatGPT can quickly respond with actionable suggestions. This has the potential to allow developers to quickly identify and resolve errors, saving time and resources.

Potential next steps for your own custom error page might be to improve the formatting / branding of the page, develop something similar for other HTTP status codes, change the model or temperature parameters to experiment with the results.

Why spend time searching for answers on stackoverflow when you can get them automatically? Try an AI chatbot in your custom error page today to make diagnosis a breeze – you won’t look back!

Post Publishing Edits: 
March 20th 2023 - Added text on the elapsed time for getting a response from the OpenAI endpoint.