SQL Workshop

Abstract

Now that you have a graphical representation of what your underlying tables should look like, in the form of an entity-relationship diagram (ERD), it’s time to dig in and start creating the objects. As mentioned before, you could use your ERD tool to generate the scripts, but to get used to using the SQL Workshop, here you’ll create these objects from scratch.

Now that you have a graphical representation of what your underlying tables should look like, in the form of an entity-relationship diagram (ERD), it’s time to dig in and start creating the objects. As mentioned before, you could use your ERD tool to generate the scripts, but to get used to using the SQL Workshop, here you’ll create these objects from scratch.

FormalPara Note

For this and many of the following chapters, you need to download the code that accompanies the book. If you haven’t already done so, download the code .zip file from this book’s home page at www.apress.com . Then unzip it to a directory from which you can retrieve the files easily.

Creating Objects with the Object Browser

SQL Workshop’s Object Browser is somewhat misnamed, because it not only allows you to view database objects, but also lets you create and edit them. For now, you’ll skip the USERS table; you will come back to it later in the book. Right now, you’ll focus on the TICKETS and TICKET_DETAILS tables. From this point forward, you’ll follow step-by-step instructions interspersed with figures and discussions about what you’re trying to achieve and why you’re doing it the way you are. Let’s get started:

• 1. Log in to your APEX workspace. You’re presented with the workspace’s Home page, which, unless you’ve been doing other work in this workspace, probably looks a little sparse.

• 2. Using the tabbed navigation bar across the top of the Home page, pull down the SQL Workshop submenu by clicking the arrow on the right side of the tab (see Figure 4-1).

  Figure 4-1.

  

  Navigate to the Object Browser

• 3. Click the Object Browser option.

• 4. In the Object Browser, click the “+” icon (which stands for Create) button in the upper-right corner and select Table from the drop-down menu.

  The Create Table Wizard opens. The first screen (Figure 4-2) allows you to name the table and enter the details for each of the table’s columns. Using the two arrows in the Move column, you can move the columns into whatever order you like. This affects the order in which they’re defined and stored in the table. If you run out of empty rows in which to enter columns, you can click the Add Column button to add a new, empty column-definition row to the form.

  Figure 4-2.

  

  Defining the table and its columns

• 5. Enter the details for the TICKETS table as indicated in the ERD from the end of Chapter 3 and in Figure 4-2. Make sure you include the appropriate checks in the Not Null column of the form. Then click Next.

  The next step in the wizard (Figure 4-3) lets you choose how you would like the primary key to be populated and which column to use as the primary key. The four options for primary key are fairly self-explanatory, but the two in the middle are probably the most common. You’re starting from scratch and therefore don’t have any existing sequences defined in your database. By selecting “Populate from a new sequence,” you tell APEX to create a sequence for you and to create a database trigger on the table that will populate the selected primary-key column with the next value from the sequence, unless the field already has a value. You’re given the chance to name the sequence in this step as well. In this instance, you’ll use the default name given.

  Figure 4-3.

  

  Defining the table’s primary key

• 6. Select the Populated from a new sequence radio button. After the screen changes, select TICKET_ID (NUMBER) for the Primary Key. Leave the Sequence Name set to its default and click Next.

• 7. You’re not going to create any foreign keys in this table just yet, so leave the defaults and click Next.

  The Constraints screen in Figure 4-4 allows you to add either Unique or Check constraints to the table definition. You add a constraint by defining the constraint in the Add Constraints region and clicking the Add button to add it to the list. Below the Add Constraints region are two Help regions. Clicking the arrow to the left of the region title expands the help and shows the columns you defined in the table and examples of how to code various check constraints.

  Figure 4-4.

  

  The constraints definition step

  When you click the Add button, the definition of the constraint is added to the list of constraints at the top of the page. You can define as many constraints on a given table as is necessary. Once you’re done, simply continue with the wizard.

• 8. You’re not going to create any Unique or Check constraints here, so stick with the defaults and click Next.

  The final step of the Create Table Wizard gives you the chance to confirm your request and, if desired, review the code that will be executed. If you need to make changes to the table definition, you can use the buttons at the bottom of the region to navigate back through the wizard steps. To view the code, click the arrow to the left of the SQL label to expand the region, as shown in Figure 4-5.

  Figure 4-5.

  

  Review the Create Table Wizard’s SQL

• 9. Review the text in the SQL region presented by the Create Table Wizard. Click Create Table to complete the wizard.

  Once you’ve successfully completed the wizard, you’re taken back to the Object Browser, and the definition of the TICKETS table is displayed. Take a moment to examine the definition of the table. You should see all the columns that you defined listed. If you click the Constraints tab at the top of the definition region, you will see a number of different constraints, including the primary-key constraint on TICKET_ID.

  In the upper-left corner of the Object Browser is a select list that defines the object type being browsed. Use this select list to choose Sequences. You see that APEX created a sequence called TICKETS_SEQ that will be used to fill the TICKET_ID.

  Once again, use the Object Type select list and choose Triggers. You will see a trigger named BI_TICKETS (BI stands for “before insert”). Selecting the BI_TICKETS trigger on the left-hand side and then clicking the Code tab above the trigger details will show the code for the trigger that is using the TICKETS_SEQ sequence to fill the TICKET_ID if it is null. You should see code similar to the following:

  create or replace trigger "BI_TICKETS"

    before insert on "TICKETS"

    for each row

  begin

    if :NEW."TICKET_ID" is null then

      select "TICKETS_SEQ".nextval into :NEW."TICKET_ID" from sys.dual;

    end if;

  end;

  Now that you have the TICKETS table defined, let’s go back and create the TICKET_DETAILS table. This time, you’ll create a foreign key to the TICKETS table, as a CASCADE DELETE. This means that if you delete a ticket, the ticket details will automatically be deleted as well.

• 10. Start the Create Table Wizard using the Create (+) button.

• 11. Enter the table name and column definitions based on the ERD and Figure 4-6, and click Next. Again, make sure you check the appropriate Not Null checkboxes.

  Figure 4-6.

  

  Defining the TICKET_DETAILS table

  The next set of steps is purposely a bit more vague than the previous ones. You should be used to using the Create Table Wizard by now, but if you need a refresher, just look at the previous steps.

• 12. Choose Populate from a new sequence for the primary key, select TICKET_DETAILS_ID(NUMBER) as the Primary Key column, and click Next.

• 13. Add a foreign key between the TICKET_ID in the TICKET_DETAILS table and the TICKET_ID in the TICKETS table. Make sure the Delete action is set to Cascade Delete. Your screen should look similar to that in Figure 4-7. Additionally, make sure you tab out of the References Table field in order to cause APEX to display the shuttle control that allows you to choose the referenced columns.

  Figure 4-7.

  

  Defining a cascade-delete foreign key for TICKET_ID

• 14. Click the Add button to add the new foreign-key constraint.

• 15. Click Next (see Figure 4-8).

  Figure 4-8.

  

  Foreign key as defined in the table wizard

• 16. No constraints are required for this table. Click Next.

• 17. Review the SQL and click Create Table to complete the wizard.

Loading Data with the Data Workshop Utility

Now that you have your two base tables defined, you can begin working to migrate the old data into your shiny new data structure. You can use SQL Workshop’s Data Workshop utility to load and unload data from an Oracle schema in a number of ways, as shown in Figure 4-9. The Data Load option allows you to choose Text Data, XML Data, and Spreadsheet Data.

Figure 4-9.

Data Load and Unload methods provided by the Data Workshop utility

Although three separate options are presented, the Text Data and Spreadsheet Data options actually use the same Data Load Wizard. There is little or no discernible difference in the actions of the wizard regardless of which option you select.

The third option (XML Data) allows you to load data that has been exported in Oracle’s proprietary XML Data Transport format. The format looks like this:

<ROWSET>

<ROW>

  <USER_ID>2</USER_ID>

  <USER_NAME>DOUG</USER_NAME>

  <PASSWORD>A69856770A9AB9CBB0479573FCB3E2A5</PASSWORD>

</ROW>

<ROW>

  <USER_ID>3</USER_ID>

  <USER_NAME>DAVID</USER_NAME>

  <PASSWORD>E2E89134B8AC6E1FFC14139A6FB2C10B</PASSWORD>

</ROW>

</ROWSET>

In your imaginary company, the help-desk technicians have been using Microsoft Excel to track tickets, so you’re going to load the data using the Spreadsheet Data option. A quick glance at the spreadsheet your technicians use shows you that they have two separate sheets in the Excel workbook: TICKETS and TICKET_DETAILS.

Knowing that you’re using preexisting tables that already have primary and foreign keys in place, you need to be careful about how you load the data. TICKET_DETAILS depend on TICKETS for their parentage, so you need to load the TICKETS data first. Your spreadsheet should look like that in Figure 4-10.

Figure 4-10.

Spreadsheet data from the TICKETS tab of your Excel workbook

Once you have the TICKETS data in the clipboard, you can switch back to APEX and use the Data Load Wizard to insert this data into your TICKETS table. Here are the steps to follow to load data from the spreadsheet into the database:

• 1. Locate the helpdesk_spreadsheet.xls file where you downloaded the supporting files for this book, and open it with Microsoft Excel. Navigate to the TICKETS tab. Notice that you have a row for each ticket and a header row that contains the column headings for each of the columns.

• 2. Select all the data, including the column headings, and copy it to the clipboard. Be cautious not to accidentally select any rows that don’t have data in them, because that may cause phantom rows or errors in the Data Load Wizard.

• 3. Switch back to your web browser, and, using the pull-down menu on the SQL Workshop tab, select Data Workshop under the Utilities section.

• 4. In the Data Load region, click Spreadsheet Data. You should see the Load Data dialog shown in Figure 4-11.

  Figure 4-11.

  

  Preparing to copy and paste the spreadsheet data and load it into the existing TICKETS table

• 5. In the wizard, select Existing table for Load To and Copy and paste for Load From, and click Next.

• 6. Select your “parse as” schema from the Table Owner select list. This is the same schema in which you created your tables in the Object Browser.

• 7. Select TICKETS for the Table Name, as shown in Figure 4-12, and click Next. This is the table into which you’ll load the TICKETS data.

  Figure 4-12.

  

  Enter the name of the table into which you’re going to load the data

• 8. Paste the data that you copied to the clipboard in step 2 into the Data text area. Change the Separator from a comma to \t, which stands for Tab Delimited. Now ensure that the First row contains column names box is checked, as shown in Figure 4-13. Click Next. (You may have to scroll within the dialog to see all the options.)

  Figure 4-13.

  

  Pasting the spreadsheet data into the Data text box

  When you click Next, APEX parses the data you’ve pasted in and does its best to match the column names in the first row of the spreadsheet data to the column names of the table into which you’re loading the data. On the next screen, you’re presented with column mapping so you can check its accuracy and, if necessary, make alterations and corrections.

  APEX is very good about matching column names as defined in the spreadsheet with those that have the same name in the table. However, if the names differ, it doesn’t try to guess but instead leaves the mapping to you.

  If you scroll to the right, you should see that APEX has matched all the column names from the spreadsheet correctly to the table columns. If, for some reason, the mappings aren’t right, you can adjust them using the drop-downs shown in Figure 4-14.

  Figure 4-14.

  

  Manually mapping the data columns to the table

• 9. When you’re sure all the mappings are correct, click the Load Data button to load the data into the TICKETS table.

  After the data is loaded, you’re presented the Spreadsheet Repository screen shown in Figure 4-15. That screen shows that twenty rows were loaded into the database and zero errors occurred during loading.

  Figure 4-15.

  

  Data has been loaded into the TICKETS table

  If you navigate to the Object Browser, select the TICKETS table, and look at the data in that table, you can see that the records that were in your spreadsheet have been loaded into the database. To finish the job, you need to load the data for TICKET_DETAILS. Here’s what to do:

• 10. Navigate to the Data Workshop, click the Spreadsheet Data link in the Data Load region, and click Next.

• 11. In the wizard, select Existing Table for Load To and Copy and paste for Load From, and click Next.

• 12. Select your “parse as” schema from the Table Owner select list. This is the same schema in which you created your tables in the Object Browser.

• 13. Select TICKET_DETAILS for the Table Name, and click Next.

• 14. In Microsoft Excel, navigate to the TICKET_DETAILS tab and copy all the data, including the column headings, in that spreadsheet to the clipboard.

• 15. In your browser, paste the data you copied to the clipboard into the Data text area, change the Separator to \t, and ensure that First row contains column names is checked, and click Next.

• 16. Review the mappings made by APEX in the Define Column Mapping region. It should have mapped everything correctly. Click Load Data to complete the data load. The summary should say that twenty-two records were loaded into the TICKET_DETAILS table with zero errors.

You now have both of the main tables created and loaded with the legacy data. This alone is enough to start developing an application, but you’re not quite ready to begin yet.

Creating a Lookup Table

Have a look at the definitions and data of the tables you just created. They’re basically mirror images of the spreadsheet tabs the technicians were using before. If you examine the data closely, you will notice that there are still some areas where the data isn’t quite normalized as well as it could be.

For instance, in the TICKETS table, the STATUS column has only three values—OPEN, CLOSED, and PENDING—which repeat over and over. The data values in this column indicate that it’s a perfect candidate for creating a lookup table. Although it’s tempting to create the table manually with the Create Table Wizard and then manually migrate the data, APEX can create a lookup table—complete with its own sequence, trigger, and foreign key—and modify the original table so it points to the new lookup table, all without you writing a line of code. Here’s how:

• 1. Navigate to the Object Browser and select the TICKETS table in the Object List on the left side of the screen. You should see results similar to those shown in Figure 4-16.

  Figure 4-16.

  

  Clicking the Create Lookup Table button starts the Create Lookup Table Wizard

• 2. Make sure the Table tab is selected.

• 3. Below the tab bar is a set of button-like links. Click the Create Lookup Table button, as shown by the mouse arrow in Figure 4-16; it starts the Create Lookup Table Wizard.

  The first step of the Create Lookup Table Wizard (Figure 4-17) gives you the option to show either only VARCHAR column types or all column types. It defaults to VARCHAR because that’s most likely to be the candidate for lookup tables. Looking at the columns presented in the wizard, you will see that one of the VARCHAR columns is your STATUS column.

  Figure 4-17.

  

  Selecting the STATUS column as the source of your lookup table

• 4. Select STATUS as the column from which you want to create the lookup table, and click Next.

• 5. The next step allows you to name your lookup table and the sequence that is related to it. APEX has chosen a reasonable name for the new table and sequence, so take the defaults and click Next.

• 6. The final screen of the wizard (Figure 4-18) provides you with information about the choices made and the action that is about to be performed. It’s easy to miss the SQL syntax link just below the wizard region. Click the SQL link to show the SQL.

  Figure 4-18.

  

  Clicking the SQL syntax link shows the SQL about to be executed

  Examining the SQL shows the steps that will be taken to create the new lookup table, associated sequence, and trigger; insert the data into the table; and update the data in the originating table so that it references your new lookup table. That’s quite a lot of work saved on your part.

• 7. Click Create Lookup Table to complete the wizard. You’re taken back to the Object Browser. The STATUS_LOOKUP table is highlighted and its details are shown.

Use the Object Browser to examine the objects that the wizard created.

Loading and Running SQL Scripts

The SQL Scripts tool of SQL Workshop allows you to create, upload, manage, and run SQL scripts. These scripts are similar to SQL*PLUS scripts in many ways. However, if you use scripts written for SQL*PLUS, APEX ignores any SQL*PLUS-specific syntax.

Once a script is created or loaded, it’s moved into the script repository, where it remains until you decide to remove it. From the script repository, you can decide to edit or run the script. When you run a script, APEX stores the results for you to view later. For example, you can come back to review the results for possible error messages.

You’re now going to load and run a script that will modify the underlying data just a bit. Here’s why: In the real world, the spreadsheet you received from the help-desk team would have current dates and data in it; however, the ticket dates in the spreadsheet that is downloaded with the .zip file accompanying this book very likely aren’t current. This would cause you to have to search back in history for the tickets if you were searching by date. This script will update these dates so they’re recent.

Another thing you need to take into consideration is that you loaded a bunch of data into your tables that already had IDs assigned to them. Because the IDs were loaded with the data, you didn’t use your database sequences. Therefore, your sequences are out of synch with the data. You need to drop and re-create your sequences so the next sequence number is greater than the largest ID used in the associated table.

You’re also going to alter the Before Insert trigger that was automatically created on the TICKETS table so that it automatically fills in the CREATED_ON column. You’ll also create a couple of database views that will be used later to retrieve data formatted for some of the specific charts and calendars you’re going to create.

Finally, you’ll create a function that, when passed a status name such as OPEN, passes back the ID for that status. This function is used in a number of places, because you can’t guarantee you know the ID value of a given status. Therefore, this function is the only safe way to get the associated ID for a given status.

When you’re in any of the SQL Workshop tools, you can use the pull-down menu of the SQL Workshop tab as a quick way to navigate to each of the other tools. Figure 4-19 shows this menu and highlights the SQL Scripts option.

Figure 4-19.

Using the SQL Workshop menu to navigate to the underlying tools

Here’s what to do to run the script that will update your schema objects appropriately:

• 1. Navigate to the SQL Scripts tool using SQL Workshop menu.

• 2. Click the Upload button in the upper-right section of the screen.

• 3. Click Browse or Choose File buttons to search for the SQL file to upload.

• 4. In the pop-up file-finder window, locate and select the ch4_schema_changes.sql file and click Upload. You don’t need to give the script a name; it defaults to the name of the script as it appears at the OS level.

  Once the file has been uploaded, you’re presented with a SQL Scripts report showing the script that you just uploaded. From this point, you can either edit or run the script. If you want to see what the script contains, feel free to edit it. You can run the script from the edit screen as well.

• 5. Run the script by clicking either the Run button (if you’re editing the script) or the Run icon (if you’re still viewing the SQL Scripts report).

• 6. As shown in Figure 4-20, you’re asked to make a selection between Run in Background and Run Now. Select Run Now.

  Figure 4-20.

  

  Choose whether to Run in Background or Run Now

  The script is run, and you’re immediately taken to the Manage Script Results page. You’ll most likely see that your script status is COMPLETED.

• 7. Click the View Results icon at the far-right end of the report row to see the results of the script. Figure 4-21 shows where to click.

  Figure 4-21.

  

  Click the View Results icon to view the results of running the script

The View Results page allows you to see what happened when the script was run. The default view shows an overview by displaying the first 50 or so characters of each statement along with some brief feedback and the number of rows affected by the statement. Figure 4-22 shows the results from a run of the script.

Figure 4-22.

The summary view of the script results

You can, however, get more detailed feedback by changing the report view to Detail. Doing so gives you far more insight, especially if you have a script that had errors during execution. Figure 4-23 shows a detailed view.

Figure 4-23.

The detailed view of the script results

In either view, you can quickly see whether the script encountered any errors by scrolling to the bottom of the page and looking at the report footer, which is where the report displays the total number of statements processed, the number of those that were successful, and the number that generated errors. Figure 4-24 shows the number of statements processed from a run of the script.

Figure 4-24.

In the footer of either report is the success summary for the script

User Interface Defaults

Before you start to write your application, one last thing you can do that will make your life easier along the way is to create some User Interface (UI) Defaults. This, in my opinion, is one of the most underutilized features of APEX.

Understanding User Interface Defaults

UI Defaults allow you to customize the default display attributes for tables, views, and their columns. They can be used to control many properties, including alignment, searchability, display sequence, what type of item is created for a column, default values, and many more.

For instance, when you’re creating a new form or report via a wizard (which is most of the time), APEX asks if you wish to use UI Defaults. If you select “Yes” and defaults are available, APEX applies them to the appropriate regions or items based on the tables or columns for which the attributes are defined. UI Defaults are divided into two categories: Attribute Dictionary and Table Dictionary.

The Attribute Dictionary allows you to create more-generic UI Defaults based on attribute names. Consider this a more macro-level definition.

Let’s say you create an attribute-level default for any attribute named PHONE_NUMBER. If a column named PHONE_NUMBER appeared in a table and didn’t have a Table Dictionary default assigned, the Attribute Dictionary default would take effect.

Attribute Dictionary definitions can also be assigned synonyms, allowing more than one attribute name to share the same actual definition. So, for instance, you could create the synonyms PHONE, TELEPHONE, PHONENUMBER, and so on for the original PHONE_NUMBER definition. If the wizard ran into a column with any of those names, it would apply the PHONE_NUMBER defaults to the APEX item that is created.

The Table Dictionary allows you to define defaults for a specific table or column, and those defaults are only applied to APEX regions or items created for those specific items.

Here are some things to note about UI Defaults:

• Table Dictionary defaults always override Attribute Dictionary defaults.

• When an item is created using UI Defaults, no relationship is established with the UI Default. Therefore, if you later change the definition of the UI Default, the changes aren’t propagated to previously created items.

• Items created before UI Defaults have been established don’t inherit properties of the UI Default.

• Developers can choose not to use UI Defaults, and even if they’re used, can override them after the component is created.

Having said that, UI Defaults do help ensure consistency across your application and make your job much easier as a developer.

Defining UI Defaults for Tables

UI Defaults can be managed either from SQL Workshop’s Object Browser or from SQL Workshop’s Utilities page. Here’s what to do:

• 1. Navigate to SQL Workshop’s UI Defaults page via the drop-down menu on the SQL Workshop tab and select Utilities; then, choose User Interface Defaults from the drop-down menu.

  You’re taken to the UI Defaults dashboard, where things likely look pretty sparse. This is because you haven’t actually created any UI Defaults yet. The first step in creating UI Defaults is to synchronize the Table Dictionary with the database so it knows what tables are in your schema.

• 2. Click the Table Dictionary tab along the top of the page, and then click the Synchronize button on the screen that appears.

  This initiates the Synchronization Wizard. This wizard shows you the number of tables with defaults defined and the number without. In this case, you should have zero objects with defaults and six objects without.

• 3. Click the Synchronize Defaults button to begin the synchronization with the database. This may take a little time.

  Once the Table Dictionary is synchronized with the definitions in the database, you’re presented with the report seen in Figure 4-25, which shows each table that now has base UI Defaults. If you have other tables in your schema, they also appear in this report.

  Figure 4-25.

  

  List of objects with UI Defaults defined

  You can now view or edit the UI Defaults for each of these tables. Start by viewing the UI Defaults for the TICKETS table:

• 4. Click the TICKETS link in the report. You should see the results shown in Figure 4-26.

  Figure 4-26.

  

  The table and column UI Defaults overview

  On the page in Figure 4-26 you can see an overview of the UI Defaults for the TICKETS table. In the upper portion of the report are the table-level definitions, including what the Form and Report regions based on this table will be called. In the lower portion is a list of the table’s columns, the labels that will be used, how they will be aligned when used in a report, whether they will be displayed in a report or a form, whether their REQUIRED attribute will be set in a form, and whether they have any help text.

  Next, edit both the table-level and column-level attributes:

• 5. Click the Edit Table Defaults button in the upper-right portion of the report. This allows you to edit how Form and Report regions based on this table are named.

• 6. Enter Manage Tickets for the Form Region Title, leave the Report Region Title as it is, and click Apply Changes.

Clicking any of the column names takes you to a page that allows you to set UI Defaults for that specific column. As you peruse the column UI Defaults, notice that several things have been set for you, including the REQUIRED attribute. When APEX synchronized with the database, it saw that certain fields were marked as NOT NULL at the database level and translated those constraints into UI Defaults for you.

APEX also makes some decisions based on the column’s data type, such as how to align the column when it’s displayed in a report. Use the following information to alter the UI Defaults for the indicated columns by clicking the link in the column name:

• Column: SUBJECT

• Label: Subject

• Help Text: A brief title for the issue.

• Column: DESCR

• Label: Description

• Help Text: Describes the ticket in detail. Please be as complete as you can.

• Resizable: YES

• Width: 50

• Height: 5

• Column: STATUS_ID

• Label: Status

If you wish, you can go ahead and set the UI Defaults for any of the other columns and/or tables. Just remember, what you do now will affect what the wizards create for you later, so if something doesn’t look exactly like what is shown in this book, check what you set for UI Defaults.

Summary

SQL Workshop may not measure up to some of the more popular GUI tools, but it certainly has the power to do most things you need to do relating to the creation and management of tables and data. You’ve also seen that SQL Workshop has a few built-in but hidden gems like the Create Lookup Table Wizard. Finally, among the many useful utilities is the UI Defaults manager, making your job as a developer just a bit easier.

Sure, this chapter hasn’t covered SQL Workshop in its entirety, but you’ve definitely gained a fair amount of insight as to what it’s capable of. You will use SQL Workshop for a number of other things throughout this book, but don’t wait. Go poke around in some of the dark nooks and crannies and see what you find!