Create and Maintain SQL Using Natural Language with APEX AI Assistant: Demo

Introduction

In the realm of Oracle APEX development, the process begins with establishing a comprehensive data model that outlines the very information our applications will manage. To empower users to efficiently find, visualize, and modify this data, we create pages within the page designer. Each of these pages serves as a canvas where we can configure the displayed or editable data in various regions. APEX thrives on a declarative SQL language, a beloved tool for developers over the past fifty years.

SQL Simplified with APEX Assistant

For instance, when creating a revenue overview page and defining a chart, we can simply type our SQL query if we know it. However, with the new APEX Assistant feature, there exists a more productive alternative: we can use natural language. For example, we could instruct the Assistant by typing: "Show the sum of the amount due for patient procedures in the preceding 24 months, grouped by month." The APEX Assistant then generates the corresponding SQL statement, rendering our chart functional immediately.

On another page displaying recent medical procedures, we may allow end-users to generate reports outlining patient names, procedure descriptions, total amounts, and doctor names for procedures conducted over the past 60 days, sorted by the most recent. By simply typing this request in natural language, the APEX Assistant, well-versed in the relationships between tables, generates the necessary SQL with appropriate joins, enabling us to deploy the report in record time.

Throughout the development process, APEX Assistant can assist with any SQL-related questions. For example, if we ponder the difference between a JOIN and a LEFT OUTER JOIN, the Assistant acts like a personal SQL coach, providing explanations complete with concrete examples that facilitate our learning.

Enhancing SQL Workshop with APEX Assistant

APEX Assistant's capabilities extend into SQL Workshop as well. This allows developers to iteratively refine their queries. For instance, starting with a basic select query displaying patient names, we might ask for additional data: "Add a count of medical procedures conducted for each patient." The Assistant updates the query, which we can execute to view the newly obtained results. Furthermore, we might request: "Include the cost of each patient’s most expensive medical procedure from the last year, and only display patients with at least one procedure, showing the priciest first." This streamlined and intuitive approach significantly enhances productivity in constructing data-centric applications.

In cases where our SQL queries produce errors, we can rely on APEX Assistant to clarify the issues and guide us toward rectifications. Each engagement with the Assistant serves as an opportunity to learn, allowing us to discern and understand the nuances of error resolution.

Powering Application Development with APEX Assistant

The APEX Assistant is not limited to aiding in SQL writing; it can also initiate the entire application development process within Oracle APEX. Simply articulate the desired purpose of our application in natural language, detailing the types of pages and specific features we want, and APEX Assistant will suggest a set of pages and options tailored to our requirements. This conversational interface encourages continuous dialog, allowing us to introduce additional functionalities, such as ACL-based security or user feedback mechanisms.

If we realize we need additional pages later on, such as a faceted search page or a dashboard, we can effortlessly request these enhancements from APEX Assistant. Upon completion of outlining our requirements, we can click “create application” to review the suggested app structure, followed by another click to finalize its creation. This feature provides an excellent starting point to incorporate further elements unique to our vision.

In conclusion, the new APEX Assistant is poised to turbocharge Enterprise application development, offering a revolutionary approach to writing SQL and developing applications that cater to our precise needs.

Keywords

• Oracle APEX
• Data model
• SQL
• APEX Assistant
• Natural language
• Application development
• Query refinement
• Error resolution
• Data visualization
• User feedback

FAQ

Q1: What is Oracle APEX?
A1: Oracle APEX is a low-code development platform that enables developers to build scalable and secure enterprise applications rapidly.

Q2: How does the APEX Assistant enhance SQL writing?
A2: The APEX Assistant allows developers to use natural language to generate SQL queries, making it accessible and efficient for users of all skill levels.

Q3: Can APEX Assistant help with learning SQL?
A3: Yes, APEX Assistant can explain SQL concepts, differences, and examples, serving as a personal SQL coach to enhance learning.

Q4: How does APEX Assistant assist in application creation?
A4: Developers can describe their application’s purpose using natural language, and APEX Assistant will propose a set of pages and features to meet those needs.

Q5: Is APEX Assistant useful for debugging SQL queries?
A5: Absolutely! APEX Assistant can help identify errors in SQL queries and provide guidance on how to resolve them, facilitating a learning experience through troubleshooting.