Oracle APEX Application Development, also known as “APEX Service,” provides citizen and professional developers with a fully managed, low-code application development and deployment platform in Oracle Cloud Infrastructure. You not only get Oracle APEX, but also an underlying Oracle Autonomous Database - Shared Infrastructure (ADB) database. With APEX Service, you can create powerful apps quickly and easily right in your browser - no additional tools required.
This tutorial will help you get started using APEX Service. If you already have a service instance pre-provisioned and have received a welcome message in your email inbox, then skip down to the section titled Executing Instance Lifecycle Operations. Otherwise, it is recommended to follow this tutorial from the beginning. If you do not finish it, you can come back later and return to where you left-off using the links below.
Table of Contents
First, you need to have an OCI tenancy and a user account to sign-in with. If you have this, go ahead and sign in now. If you do not have this, begin by following this blog post about signing up for a tenancy in Oracle Cloud Free Tier. Return here once you able to sign in and see the OCI Console homepage (pictured below).
At the top left corner of the OCI Console homepage, click the hamburger icon to open the OCI Service Navigation Menu. Scroll down in this menu until you see a service category named Database Related Services. Expand the category to see APEX Application Development > APEX Instances. This is your main entry point into APEX in OCI. Click on APEX Instances to proceed.
You now enter into a new user experience that allows you to see APEX on any ADB-derived service, quickly create a new APEX Service instance, or directly launch into APEX or SQL Developer Web on a running instance. Below is an example where APEX already is running on two Always Free Autonomous Database instances, and there are no instances of APEX Service yet. Continue by clicking the button Create APEX Service. It is located right above the instances list.
This displays the APEX Service instance creation dialog. Fill in the dialog as follows:
Once you finish filling in the dialog, click Create APEX Service at the bottom to commence instance creation.
You immediately are taken to an APEX-specific page that shows helpful details about your new APEX Service instance including its name, version information, and usage metrics. This is the same detail page you would see if you drilled into an APEX instance from the prior instances list screen. Notice that the state of your new APEX Service instance initially displays as Provisioning. After approximately 1-3 minutes, the state should change to Available.
Soon after the state changes to Available, you will receive a welcome message in the email inbox of the administrator for the OCI tenancy. This message contains important information about your new APEX Service instance including direct links to its APEX Administration Services, APEX Application Development Services, and SQL Developer Web. It also contains a link to this tutorial.
Now is a good time to learn about lifecycle operations for APEX Service instances. These operations include instance start, stop, and terminate - to name a few. They are useful for several reasons. For example, you may wish to stop a running QA instance while testing activities are paused, and then start it again when testing resumes. Or, you may need to terminate an instance after all of its APEX applications and data have been decommissioned and the instance is no longer needed. These lifecycle operations are available in three places: in the APEX Instances list screen mentioned above (see the dropdown menu available at the far right side of each APEX row in this list), in the APEX Instance details page, and in the details page of the underlying database. Below you will see how to do lifecycle operations from the database details page. In addition, you will see how to easily switch between the APEX details page and the database details page. Let's take a closer look.
If you are not already viewing the details page of the APEX Service instance that you created previously, first sign back into your OCI tenancy and navigate to this page. After it loads, locate the text Database that has a link adjacent to it. This link opens a details page for the database included with your APEX Service instance. Click it to proceed.
Here you see the details page of the database. Notice helpful database information displayed here. For example, you can see the exact database version number.
To see a list of instance lifecycle operations that are available, click the More Actions button at the top of this page. Notice how different operations are available or not available depending on the current state of your database.
Ensure that your database has been started (is in Available state) before proceeding to the next section. After reviewing the list of lifecycle operations, click somewhere else on the screen to hide it again. There is no need to execute an instance restart at this time.
Back on the database details page, locate the link at APEX Instance > Instance Name, and then click on it. This takes you back to the APEX instance details page where you started.
Before you start developing APEX apps, you must first create an initial APEX workspace and user account. You can do this using APEX Administration Services. This is the main console for managing APEX workspaces, user accounts, and more. The workspace you will create becomes your main pallet for building and deploying apps. The new user account will serve multiple purposes: we will use it to sign into the workspace for app development and also use it as an end-user of a running app. While this setup is sufficient for demonstration purposes, in production APEX environments, it is recommended to separate duties into distinct APEX user accounts with minimum privileges. You can read more about user authorization and privileges in the APEX documentation.
There are a multiple ways to get to APEX Administration Services. With your APEX Service instance running, proceed by clicking Launch APEX on the instance details page or instance list screen (both pictured below), or by opening the APEX Administration Services link contained in your welcome email. If you recently provisioned or restarted the instance, it is recommended to wait an extra 1-3 minutes before opening APEX Administration Services to ensure that everything related to APEX is running properly.
Upon entering APEX Administration Services, you will be prompted to sign in using the database administrator account. Enter the ADMIN password you set when creating the APEX Service instance.
Soon you will see an APEX Administration Services welcome screen that looks like the one below. Review the message text, and click the button to proceed.
Next, you are prompted for an APEX workspace name, a database user name, and a password. This new workspace will be your shared work area where one or more low-code developers can build and run APEX applications. After you finish this step, an APEX workspace, a database user, and a database schema (with same name as your database user) will be automatically created. Later, when we create an APEX app, it will run against database objects in your schema.
For our example, enter DEMO as the database user name and workspace name. Make sure to remember your password. Click the Create Workspace button to continue.
Here you see the APEX Administration Services main page. Notice a message at the top indicating your workspace has been created.
Before you do anything else, bookmark this page in your browser so you have an easy way to open APEX Administration Services in the future with a single click. The bookmark should look something like what is shown below. It also is available in the welcome email that was sent upon creating your APEX Service instance.
Now we are going to sign out of APEX Administration Services and go to the sign-in page for APEX workspaces, also known as APEX Application Development Services. In the message at the top of your screen, notice it contains a link with the name of your new APEX workspace (in our example, DEMO). Click this link to continue.
You are signed out of APEX Administration Services and redirected to the APEX workspace sign in page with your workspace name automatically filled in. Enter your workspace password, then click Sign In to continue.
We now see the APEX Workspace homepage. It gives you easy access to the four main areas of APEX: App Builder, SQL Workshop, Team Development, and App Gallery. App Builder is where you go to create and edit APEX apps. SQL Workshop provides a full database object explorer plus an interface to run SQL queries and see query results. Team Development gives you access to the rich collaborative development features of APEX. App Gallery includes a suite of predefined productivity applications you can install and use. For this tutorial, we will work mainly in App Builder.
Bookmark this page in your browser so you have an easy way to sign into your APEX workspace in the future with a single click. The bookmark should look something like what is shown below. It also is available in the welcome email that was sent upon creating your APEX Service instance.
At this point, you can stop and pat yourself on the back because you’ve finished a major milestone. You have an instance of APEX Service running in Oracle Cloud and a shiny new APEX development workspace. Now you are ready to create your very first APEX app!
There are several ways to quickly create APEX apps. You can import spreadsheet-type data from a file, adapt an existing app from the APEX App Gallery, or create an app from scratch. APEX App Builder makes all of these options available in a single wizard. In the interest of time, we’re not going to cover all of the options here. We will focus on creating an app from sample data because it is probably the most common way to kickstart a new APEX app.
The reminder of this tutorial is divided into four distinct sections. In Part I, we will launch the app creation wizard and load a sample data set. In Part II, we will finish the wizard and automatically generate an APEX app. In Part III, we will run the app and sign into it. Lastly, in Part IV, we will explore the app, make two small changes, and then re-run it.
From the APEX workspace homepage, click App Builder.
Click Create a New App.
Click From a File.
When creating an application from a file, APEX allows you to upload CSV, XLSX, XML, or JSON files and then build the app based on this data. Alternatively, you can also copy and paste CSV data or use sample data sets that are included with APEX.
Within the Load Data dialog, click the Copy and Paste option at the top.
Click the dropdown box that shows a list of sample data sets, and select Project and Tasks. Notice a sample data set is automatically pasted for you into the text area below. From here, you can simply click Next.
Review the parsed data. At the bottom of this page is a sampling of actual table data that will be created in the database.
Set Table Name to PROJECT_TASKS and click Load Data to proceed. You can ignore the prompt to select individual columns because we will be loading all columns (default behavior).
After clicking Load Data you will see a spinner until the wizard finishes loading data. Continue to Part II at that point.
The data loading step has created a new database table and populated it with records from the sample data. Now you can create an APEX app based on this table.
In the final Load Data screen, verify that 73 rows have been loaded into the PROJECT_TASKS table, then click Create Application.
In the Create Application page, review the new app pages that will be generated. Click the Edit button for a given page if you want to review details about it.
Click Check All next to Features, and then click Create Application.
When the wizard finishes creating the app, you will be redirected to the new app’s homepage in App Builder.
Click Run Application. This will open the runtime app in a new browser tab, allowing you to see how end users will view the app.
Enter your user credentials and click Sign In. So far we have created only a single APEX user: the user generated when we created an APEX workspace. Use this same Username and Password now to sign into the app.
Explore the application a little. Click Dashboard (in the home menu or navigation menu) to view charts that were created. Click Project Tasks Search (in the navigation menu) to experiment with faceted searches. Click Project Tasks Report to view an interactive report, then click the Edit icon for a given record to display details in an editable form. Next, navigate to the Calendar page and review the data displayed (you may need to scroll back a few months to see data). Finally, review the options available under Administration.
Congratulations, you have a live APEX app! This app was generated almost entirely for you by APEX. All you had to do was load pre-packaged sample data and make a few simple settings.
In this final section, you will learn how to enhance your app. We will improve the app’s faceted search page for better clarity and improved usability.
Start by reviewing the Project Tasks Search page in this app. See that you can check and uncheck various search facets. When you check a facet, the counts on all remaining facets are updated to show the number of records that meet your search criteria. Note the Status facets, which are commonly used to report on project status, are located down the page instead of at the top. Also, users are allowed to select multiple values for Status, when most likely they will be reporting only one project status at a time. Let’s improve this user experience a bit.
From the runtime application, go to the search page by clicking Project Tasks Search. Given you ran this app from the App Builder, a special Developer Toolbar is displayed at the bottom of the screen. Note: end users who log directly into the app will not see this toolbar.
In Developer Toolbar, click Edit Page 3.
Alternatively, you can also navigate back to App Builder in your browser manually by selecting the appropriate browser tab or window. Once you are in App Builder, click the page 3 - Project Tasks Search.
You should now be in APEX Page Designer. This is where you will spend the majority of your time improving the application. There are three panes within Page Designer. The left pane displays a hierarchical list of page components called the Rendering Tree. The middle pane displays page Layout (a high-level representation of the rendered page) and Gallery (from which you can drag and drop app components into the Layout). The right pane is the Property Editor, where you can change attributes for a selected component.
Status is a very common search criteria, so you want to reorder facets so that Status is higher up in the page between Project and Assigned To.
In the Rendering Tree (left pane), under Search, within Facets, click and hold P3_STATUS. Drag it up until it is immediately under P3_PROJECT, then release the mouse.
Currently the Status facet is a series of checkboxes. However, it is unlikely users will want to select more than one status at a time, so you want to convert this into a radio group.
Continuing with P3_STATUS selected, within the Property Editor (right pane), for Identification > Type, select Radio Group. Scroll down the properties in Property Editor, and for List Entries > Zero Count Entries, select Show Last.
At the top right of the page, click Save and Run Page to save changes made to the facets and review the app.
In the runtime environment, click one of the statuses. See how the counts on other facets are updated based on your selection. Also, notice how you can still select one of the other statuses or click Clear within Status to clear your selection and again show all status counts.
You have just finished the final part of this tutorial. You implemented a few changes to a live APEX app and then re-ran the app to verify them. Great work!
In closing, I hope this quick tour of low-code app development using APEX Service has been helpful. You saw how easy it is to create and manage an APEX Service instance. Then, you stood up a nice looking, full featured web app in a matter of minutes without a stitch of coding required. These "data-driven" apps start with your data and work upwards to build and deploy powerful apps on top. This approach can transform anyone who has basic familiarity with databases and web technology into a highly productive low-code developer.
Enjoy using APEX Service!