© Edward Sciore 2020
E. ScioreUnderstanding Oracle APEX 20 Application Developmenthttps://doi.org/10.1007/978-1-4842-6165-1_1

1. The SQL Workshop

Edward Sciore1 
(1)
Newton Center, MA, USA
 

Congratulations! You are on the brink of learning how to build APEX web applications. But before you can begin, you need an APEX account. There are several ways to obtain an account: you can register for a free account through the apex.oracle.com website, you might be given an account to an APEX server at your job, or you can even install an APEX server yourself and create your own account to it.

No matter the circumstance, your APEX server will have an associated URL. Invoking the URL from your browser takes you to a login screen. Figure 1-1 shows the login screen used by the apex.oracle.com server.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig1_HTML.jpg
Figure 1-1

APEX login screen

Entering your credentials then takes you to the APEX home screen, the top of which is shown in Figure 1-2.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig2_HTML.jpg
Figure 1-2

APEX home screen

The APEX development environment contains several tools. Of primary importance is the App Builder tool, which will be covered in depth starting in Chapter 2. We begin here by looking at two tools in the APEX SQL Workshop: the object browser and the SQL command tool. These tools allow you to directly access the database—the object browser lets you manipulate a graphical user interface, and the SQL command tool lets you execute SQL statements and PL/SQL code blocks.

Although the SQL Workshop tools are not essential for application development, using them can make your life much easier. Here are five ways that they can help:

  • The object browser reminds you of the database objects and their structures. For example, a typical application involves several tables, each of which can have numerous columns. It is often impractical to memorize the details of each one. When building a page that references a table, you can use these tools to help refresh your memory.

  • To modify the structure of the database. For example, these tools are the easiest way to execute the alter table command given in the introduction.

  • To modify the contents of the database. For example, you might want to insert or modify records to test the behavior of a page with new or altered data, or to reset the database after testing the page.

  • To examine the contents of the database tables. After running a page, you can verify that the database updated correctly.

  • To debug an SQL statement or PL/SQL block. By executing code in the SQL commands tool first, you can verify that it produces the expected result before you actually assign it as the value of some property on a page.

To get to the SQL Workshop, click the SQL Workshop button on the APEX home screen. Figure 1-3 shows the resulting screen. From this screen, you can click the Object Browser or SQL Commands button to get to the desired tool.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig3_HTML.jpg
Figure 1-3

SQL Workshop home screen

Downloading Tables

This book makes frequent use of the sample tables EMP and DEPT. If your workspace does not contain these tables, here is how to load them. Click the arrow of the SQL Workshop tab, select Utilities, and then Sample Datasets; see Figure 1-4. Figure 1-5 shows the resulting screen, which lists the available tables. Click the Install button for the EMP/DEPT dataset, and follow directions.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig4_HTML.jpg
Figure 1-4

Getting to the sample datasets

../images/335103_3_En_1_Chapter/335103_3_En_1_Fig5_HTML.jpg
Figure 1-5

The Sample Datasets screen

To verify that the tables have been installed, return to the SQL Workshop home screen (as shown in Figure 1-3). Entries for EMP and DEPT should now appear in the Recently Created Tables region.

Object Browser

The object browser lets you interact with your tables quickly and easily. From it, you can examine the description of each table—that is, the types and properties of its columns and its constraints, indexes, and triggers—as well as its contents. You can also use the object browser to make simple changes to the description or contents of a table.

The home screen for the object browser displays a list of table names along its left side. Clicking a table name displays information about that table. For example, the screen for the EMP table appears in Figure 1-6.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig6_HTML.jpg
Figure 1-6

Viewing the EMP table from the object browser

The main portion of the screen displays information about each column of the table. Above this information is a series of buttons that let you modify it. As an example, recall that the introduction discussed the need for an Offsite column; let’s add that column to the table now. Clicking the Add Column button displays a form for you to fill in the details of the new column. Figure 1-7 shows how I filled in this form.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig7_HTML.jpg
Figure 1-7

Adding a new column to EMP

Clicking the Next button takes you to a confirmation screen; from there, click Finish to complete the action. The EMP screen should now display the new column.

Returning to Figure 1-6, observe the tab bar above the row of modification buttons. The Table tab is currently selected, which displays column information for the table. The other tabs show you other kinds of information and provide appropriate ways to view and modify that information. For example, clicking the Indexes tab displays the current indexes for the table. Figure 1-8 shows the three indexes for EMP. Clicking the name of an index displays additional detail about that index.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig8_HTML.jpg
Figure 1-8

Browsing the indexes of EMP

Finally, consider the Data tab, which displays the contents of the table. The top of this table appears in Figure 1-9. Note that there is a button to insert a new row and an edit link at the beginning of each row.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig9_HTML.jpg
Figure 1-9

Viewing the contents of EMP

Clicking a row’s edit link displays a form for modifying it. Figure 1-10 shows this form for employee 7698. Clicking the Apply Changes button performs any modifications that may have been made to the column values; clicking the Delete button deletes the record.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig10_HTML.jpg
Figure 1-10

Editing the contents of employee 7698

If you wish, you can edit this record, setting the value for Offsite to N. You can then proceed to edit the other records, setting their column value to Y or N as desired. Given the tediousness of this approach, however, it is easier to use the SQL command tool, which is discussed in the next section.

SQL Command Tool

Most of the actions that you can perform in the object browser correspond to one or more SQL statements. In effect, the object browser is merely a convenient way to formulate and execute simpler SQL statements. If you want to perform more complex activities, use the SQL command tool.

The SQL command tool divides the screen into two sections. You type an SQL statement or PL/SQL block into the top section, and the result appears at the bottom. Figure 1-11 shows the screen after executing the SQL statement select * from EMP.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig11_HTML.jpg
Figure 1-11

Using the SQL command tool

APEX displays only 10 of the 14 employee records. The reason is due to the select list labeled Rows at the top of the figure, whose value specifies the maximum number of rows to display. By default, its value is set to 10; if you want more rows displayed, you must first select a larger number. This feature is intentional. By forcing you to explicitly specify the output size of your query, APEX protects you from yourself. Suppose, for example, that you execute a multi-table query in which you forgot to include the join conditions. The resulting output could easily have billions of records that, if not truncated, would cause your APEX session to be unusable.

To the right of that select list is a Find Tables button, which is useful if you need to be reminded about the tables and their columns. Clicking this button displays a window similar to the object browser. You can scroll through the available tables; selecting a table shows its column information. Figure 1-12 shows the result of using the Table Finder window to display the DEPT columns.
../images/335103_3_En_1_Chapter/335103_3_En_1_Fig12_HTML.jpg
Figure 1-12

Using the Table Finder window

You might have noticed from Figure 1-11 that the EMP records still do not have values for Offsite. In the introduction, I stated that all employees work onsite except SCOTT, ALLEN, WARD, and TURNER. The easy way to handle this is to execute two update commands: the first one sets everybody’s Offsite value to N, and the second sets the four chosen records to Y. You can either run each statement individually in the command tool or combine them into a single PL/SQL block and execute it. The code for the latter option appears in Listing 1-1.
begin
    update EMP
    set OffSite = 'N';
    update EMP
    set OffSite = 'Y'
    where EName in ('SCOTT', 'ALLEN', 'WARD', 'TURNER');
end;
Listing 1-1

A PL/SQL Block to Assign Offsite Values

Summary

This chapter examined the object browser and SQL command tools in the APEX SQL Workshop. Both tools allow you to view and modify the database, but via very different interfaces. The object browser provides a visual interface, in which you perform tasks by clicking buttons and filling out forms. The SQL command tool provides a command-based interface, in which you perform tasks by executing SQL statements.

The object browser is ideal for performing common and simple tasks. As its name implies, the object browser is also well suited for exploring the database. The point-and-click interface makes it easy to discover the various tables in the database and explore their columns. The object browser also does not require familiarity with SQL, so it is especially suitable for casual users.

The SQL command tool, on the other hand, assumes that the user is both familiar with the database structure and proficient in SQL. The command tool is therefore suitable for experienced users of the database. If you know the appropriate SQL, you can perform many tasks much more easily from the SQL command tool than from the object browser.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.12.36.30