Oracle Application Express Tutorial IDE Overview

Recently, I was surprised to find that, it turns out, sensible tutorials on Oracle Application Express (aka APEX, apex, but not to be confused with apex, which is used in Salesforce) in nature does not exist.

This is not the most common technology, of course, but, nevertheless, quite popular. There are dozens of blogs about the apex (English-speaking, mostly), a couple of thousand questions at Stackoveflow, a special section on the apex on the oracle official forums, the apex itself has been around for 15 years and is constantly evolving. I was sure that there were plenty of good tutorials. But no!

A brilliant idea immediately occurred to me to fill this gap.

Table of contents

Why are existing tutorials bad
Introductory information
IDE Overview
App builder
Application page
Application properties
Shared Components
Page Designer
SQL Workshop
Object Browser
SQL Commands

Why are existing tutorials bad

I will explain a little why I consider existing tutorials bad. Apex is not a programming language, it is such a very high-level development tool, the closest ideological analogue of which I would call MS Access. Accordingly, most of the development on the apex consists of working with the web interface of this environment: clicking on buttons, clicking on links, filling in fields with the names of components, choosing values ​​from lists, and so on. What makes a sample tutorial? It shows screenshots of these same screens with the captions "click there somewhere." At the same time, the IDE itself is quite convenient and understandable, almost all elements are provided with reference explanations, there is a built-in help. As a result, if you need to explain to the user how to create an application, in the tutorial there will be a large screenshot explaining how to click on the "Create a new app" button. Here, look at the official Oracle tutorial yourself:

They would also explain with screenshots that a “click” is a single click on the left mouse button.
Of course, there is also useful information in such tutorials, but there are crumbs there. The 80 percent tutorials consist of such screenshots, and from my own experience I can say that a person who has not read any tutorials at all will find on his own how to create an application, add a page, place a report on it, and so on. And then he will need to organize a more or less non-trivial interaction of several elements of the apex, and it turns out that this question is almost not covered by the tutorial. Let me give you an analogy: you just learn to program, studied variables, arrays, cycles, conditions, and received a task, within the framework of which you need to sort the array. To sort an array somehow is quite simple, but as soon as the task becomes complicated to “sort a large array quickly ”, it turns out that your knowledge is not enough. Googling by digging Stackoverflow and forums, you will find a quick sort algorithm. Of course, someone himself will reinvent this algorithm sooner or later, but a more effective way would be to immediately read the book with the analysis of standard algorithms. And just such a set of “typical algorithms” for apex usually is not found anywhere, and the same method of scientific typing, search engines, forums and Stackoverflow (and, I hope, this tutorial) come to the rescue.

Introductory information

It is assumed that the reader is familiar with the Oracle Database version at least from the 11th, SQL, PL / SQL, and especially with what EXECUTE IMMEDIATE and how it works.

The apex's IDE changed significantly from the transition from version 4.2 to 5.0 (the release was just 4 years ago, in March 2015). Since then, IDE has changed little, but sometimes new features are added. I will take screenshots on apex.oracle.com, where version 19.1 is now installed, but if you have an earlier version somewhere (5.x, 18.x) used, you can easily find the necessary item, unless it appears in a later version than yours.

I'll start by reviewing the most commonly used sections of the IDE. I will also show some screenshots. Where without them, since everything here is made of visual components. But there will not be as many of them as in bad tutorials, and mostly they will be in the initial chapters, and then I will simply indicate the name of the IDE section in which the necessary things are located. Immediately I draw your attention that this is just the first acquaintance with the IDE. More detailed information on when to press which button and where to look, will be given further when I go to specific topics.

Go.

IDE Overview

The start screen, which you will see immediately after logging into the IDE:

The main elements here are 4 large clickable pictures:

1. App Builder is, in fact, the place where you can create applications and where you will spend up to 99% of the time.
2. SQL Workshop is a substitute for the usual IDE for working with Oracle DBMS. There you can view database objects (tables, indexes, packages, etc.) and execute SQL queries. If you have the opportunity to do all this through "ordinary" IDEs (like Oracle SQL Developer or PL / SQL Developer), do it through them. Nevertheless, this section can also be useful and I will also tell about it.
3. Team Development - tools for team development. Honestly, I have never seen anyone use them in practice (including me). For the sake of curiosity, I also dropped in there, and I would not call this section completely useless, although I really want to.
4. App Gallery - a section with demo applications. By the time he appeared, I already knew the apex well and almost did not look there, but for beginners, I think it will be useful. There are many different unpretentious applications, using their example you can learn how to use these or other features.

In the menu at the very top of the page, the same 4 points are duplicated.

In addition, there are a few less useful things: below there is a certain amount of statistical information (such as the most frequently edited applications or the most active developers). And there is a column on the right, where there are also some statistics and additional links.

Consider the App Builder and SQL Workshop in more detail.

App builder

This page looks like this:

The main elements of this page are: four large buttons on the previous page, a list of existing applications, a right column with a list of recently modified applications and additional actions. The four buttons are:

1. "Create" - create a new application. The same function is duplicated just below (the blue "Create" button above the report with the list of applications). The button launches the wizard to create applications.
2. "Import" - import application. Apex allows you to transfer applications from one server to another in the form of text files (which, in fact, are long PL / SQL scripts with calls for creating all elements of the application).
3. "Dashboard" is a low-value collection of application development statistics. Who, when, where, how many changes made. I think even your boss will not be interested in looking there.
4. "Workspace Utilities" - a set of additional tools for managing the application. Honestly, I don’t almost look in there either.

I also want to draw your attention to just such a small button in the report panel:

It switches the report view from the one shown in the screenshot above (with the "pictures" of applications) to the report in the form of a table:

Similar reports are found in other sections of the IDE. I prefer the tabular view, so further all the screenshots will be with him.

Application page

After you create an application or open an existing one, you will see the following page (very similar to the previous one in structure):

You will come to this page very often, and there are also a lot of useful functions here. Large buttons with pictures:

1. "Run Application". This button launches your application. It will open in a separate window. By default, it is in the window, not in the browser tab, but this behavior is customizable (more on that later).
2. "Supporting Objects". This section is used to solve issues related to installing, updating and uninstalling applications. Usually they almost do not use.
3. "Shared Components" - I would say, this is the first or second most important section of the IDE (another one is the page editor). If you develop a lot on the apex, you will very soon notice that you have learned the structure of this section as “Our Father”.
4. "Utilities" is another set of (few) useful helper functions.
5. "Export / Import". Allows you to export or import applications or parts of an application.

And two additional buttons that are circled in orange frame in the screenshot:

1. Button "Edit application properties". Clicking on it will take you to the application properties settings page. Perhaps you will not go to this section often, but it is very important because the application settings are stored there, on which a lot of things depend. Further, if I mention somewhere "application properties" - look for them in this section.
2. For once a useful feature in the right pane: "Delete this Application". While many functions in the apex are duplicated in different places, this function is only here, and it is difficult to find it the first time without using it.

Application properties

Here, first of all, the organization of access to a large number of settings is interesting.

The settings are grouped into 4 groups (see the upper frame), and in each group there are several subgroups (see the lower frame), each group has its own name. Similarly, settings are organized in so many places in the apex. In the future, I will simply specify the path to the desired setting like this: "Application Properties" - "Definition" - "Name" - "Version".

Also, if you look at the bread crumbs at the top of the page, you will see that it says "Edit Application Definition", although you would appear to appear in "Application Properties". Just ignore it.

Shared Components

Shared Components is our everything. This section is an intermediate point on the path to specific components of the application. I will show here a general view, just so you can imagine where things are:

There is almost no unimportant nonsense here. Each section you somehow may need. The section titles speak for themselves, there is almost nothing to add:

1. Application Logic - application logic controls.
2. Security - security issues and control access to the application entirely and to individual components, depending on user roles.
3. Other Components are "all other components". In good, this section should be the last, but for some reason it is here. And the sub-item "List of Values" I would transfer it to Application Logic.
4. Navigation - application components for navigation.
5. User Interface - customize the appearance of the application, styles, etc.
6. Files - section for files (for example, css or js). It is irreplaceable if you do not have access to the OS, and it is still convenient, even if there is access.
7. Data Sources - work with data sources (for loading and unloading).
8. Reports - reports, obviously.
9. Globalization - a section for translating applications into other languages.

Page Designer

Page Designer is a page editor. This is probably the main place in the IDE. Here you will create something with which the user interacts directly. Here, just a bunch of features, almost like in the cockpit. In the screenshot, multi-colored frames highlight the main components of the editor:

Button bar at the top (in the red frame):

• Field to enter the page number, up and down arrows and the button "Go": navigate through the pages of the application. Each page has a number, here you can either enter the number of the desired page, or go to the next / previous arrow, or click on the leftmost button and in the appeared modal window select a page from the list.
• Lock button: allows you to block the page from being modified by other developers.
• Undo / Redo buttons
• Two buttons with access to auxiliary functions.
• Button link to Shared Components. Useful, because you have to go back and forth all the time. (I myself, however, prefer to just keep the Shared Components in a separate tab - it's even faster).
• Button "Save" - ​​saves the changes on the page.
• The "Run" button - "launches" the page, that is, you see it as the user sees it later.

On the left (in the orange frame) is a panel with application components. There are 4 tabs in this panel:

• "Rendering" - shows what components the page consists of, what processes are executed during rendering;
• "Dynamic Actions" - actions performed on the client (for example, javascript event handlers);
• "Processing" - server processes associated with the page;
• "Page Shared Components" - for a direct transition to the common components used on the page (the same Shared Components).

In the center (in the green frame) is a panel containing a lot of heterogeneous information. At the top of the panel there are 5 tabs where you can find:

• "Layout" - shows the approximate location of components on the page;
• "Component view" - by and large, it is a relic of the past (similarly looked like the page editor in the 4th version of the apex, in version 5.0 or 5.1 this old "view" got the status of deprecated, although it was used in parallel with the new one, and now it has remained only his pale shadow);
• "Messages" - there will be error messages (for example, a message that a required field is not filled in);
• "Page Search" - search on page. Searches for the search string in all places where you can enter something (in code, in message texts, in component names, etc.);
• "Help" - help. Quite sensible, though not always. You can select a component property and view its description.

Also, the central panel has another additional panel below, which can be used to quickly add components to the page.

Right (in the blue frame) - the properties panel. Everything is simple - it shows the properties of the currently selected component.

SQL Workshop

As I already said, SQL Workshop is a substitute for the usual IDE (if you for some reason have no opportunity to use it).

It consists of 5 sections:

1. Object Browser - a browser of database objects (tables, packages, functions and procedures, indexes, sequences, etc.);
2. SQL Commands - section where you can execute any SQL query or PL / SQL code;
3. SQL Scripts - here you can download a text file with a script and execute it;
4. Utilities - various additional features associated with the database: for example, there is a graphical query builder (but not only);
5. RESTful Services - creating REST services. Apex also has REST (and long ago, they were already in version 4.2)!

Object Browser

It has everything you need to work with database objects: creating, modifying, deleting, viewing properties of objects, entering data into tables — for all of this, there is a graphical interface in this section. In the upper left of the drop-down list, you can select the type of objects (the tables show in the screenshot), in the upper right, you can select a scheme whose objects you can see. In the list of available schemes, you will not see all the schemes at once - this is normal. How to make the circuits visible in the apex, I will tell when I get to the administration.
Also, I do not see much sense in describing this section in more detail: if you know what the phrase “add a column into a table” means, you can easily guess when you need to click on the “Add Column” button and what can wait for you next.

SQL Commands

In this section, you can perform SQL queries and PL / SQL code.

In terms of capabilities, this section is almost as good as full-fledged development environments. What is missing is the auto-completion of the code and syntax highlighting (and in the Object Browser there is syntax highlighting, by the way). Available features:

• executing SQL, including with parameters (a separate window will appear for entering parameter values);
• executing PL / SQL with the ability to output text via dbms_output (the output will appear in the window below);
• executing only the selected part of the code (select the text with the mouse, then "Run");
• view request plan;
• saving queries.

***

This, as you understand, is not all that is in the APE IDE, but at this stage it makes no sense to go further. The individual elements of the apex interface will be discussed in more detail in topics that are directly related to them.

PS I still can’t say that I clearly thought out the structure of this tutorial, maybe in the future I will add more screenshots and descriptions to this article or describe additional features in other articles. It remains to figure out how to notify those interested in significant changes to old articles.