This article explains how to use the Gitora AI Chat feature.
The Gitora AI Assistant can help you write, debug, explain and improve SQL and PL/SQL code.
Gitora AI Chat uses OpenAI GPT. If you have not done so already, please follow the steps at this link to create an OpenAI account, register your API Key with the Gitora and open the PL/SQL Editor.
The AI Chat UI
If one is not already open, click the + sign in the editor and open an SQL Pad.
An SQL Pad will show up. Using the plug icon, assign a database connection to the SQL Pad, if it does not have one already.
Gitora uses this database connection to teach AI about the tables and their relationships in your database. Gitora NEVER queries the data in the tables and send them to the OpenAI servers.
The SQL Pad has two button on the right that are of interest. We’ll start from the robot icon. Click the robot to open the AI Chat window.
The AI Chat window has two sections. The upper section displays your dialog with the AI. The lower section is where you type your messages to the AI.
The Ai Chat window has a three menu button. From left to right:
SQL Only: Toggling this button on will make the AI skip the explanations and reasoning and return only code.
Start Over: Clicking this button will erase all the context and the previous conversation with the AI.
Table Sensor: The AI examines your message and automatically infers what tables it needs to know about. This feature is on by default and I highly recommend to keep it that way. If you have more than 1500 tables in the schema SQL Pad is connected to and if you are not happy with response times of the AI, experiment with turning the Table Sensor off to improve response times.
Chatting with the Gitora AI Assitant
In this example, we are connected to the Oracle Sample Schema CO which contains the following tables:
CUSTOMERS, INVENTORY, ORDER_ITEMS, ORDERS, PRODUCTS, SHIPMENTS, STORES
We ask the AI to write a query for us: List product inventory for each store.
Hint: As you type, the auto suggest feature in the editor recommends completions. Making a selection from the list will add the selected table to the AI’s context explicitly. If the Table Sensor is on, making a selection is unnecessary almost all the time. So do not feel compelled to make a selection or get the table name right. The AI will figure it out. However, if the Table Sensor is off, making an explicit selection from the completions dialog is the only way you can inform the AI about the context of the question.
Pressing the Enter key or clicking the Send button will submit your message. In a few seconds, the AI response will show up.
There are a few things you can do with this response. Hover your mouse over the highlighted SQL in the AI response. A menu will show up.
From left the right, the menu buttons are as follows:
Execute: Runs the Ai generated SQL and displays the results in the SQL Pad.
Info: Hovering over this menu item, lists the tables the AI considered while generating the query.
Hint: If the Table Sensor is on and you do not see a table that you expect the AI to include to the query, in the information popover, then add the table to your next message. For example write something like “Use ORDER_ITEMS table.”
Append to Pad: Appends the query to the SQL Pad.
Paste to Pad: Overwrites the text in the SQL Pad and pastes the query.
Copy: Copies the query to the clipboard.
The AI does not always produce correct SQL queries. If this is the case, you can inform the AI about the error to help it fix its mistake.
For example, if write the following message, the AI creates an invalid query:
product count per store
The AI produces the following query:
SELECT s.store_id, s.store_name, COUNT(p.product_id) AS product_count FROM stores s LEFT JOIN products p ON s.store_id = p.store_id GROUP BY s.store_id, s.store_name;
This query is wrong because there is not relationship between the STORES and the PRODUCTS table. Executing the query using the Execute menu button produces an error that is visible in the SQL Pad’s error tab.
Note the new red button that showed up in the menu. Gitora detected that the query did not run successfully and displayed the “Send Error to AI” button. Click this button to inform the AI about the error. Gitora will automatically package the query and the error and send a message to the AI on your behalf.
Using the AI Assistant to Analyze Errors
The Gitora AI Assistant can also be used to analyze errors during SQL, PL/SQL execution.
In the screenshot below, I wrote a simple query but misspelled the table name.
Clicking the question mark button under the Errors tab sends the error message along with the text (or the selected text) in the SQL pad to the AI Assistant like so:
Using the AI Assistant to Explain Queries
The AI Assistant can also be used to explain SQL queries (and PL/SQL blocks.)
In the screenshot below , the SQL pad contains an SQL query with the PIVOT command.
Clicking the question mark button in the toolbar will send a message to the AI Assistant asking it to explain the code.
These are just a few examples about how you can use the Gitora AI Assistant and its schema-aware capabilities. OpenAI GPT models are very capable and I am sure you will come up with many more use cases.