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, any SQL*PLUS-specific syntax will be ignored by APEX.
Once a script is created or loaded, it is moved into the Script Repository where it will remain until you decide to remove it. From the script repository, you can decide to edit or run the script. Once you run a script, APEX stores the results for you to view later. For example, you can come back later to review it for possible error messages.
You’re going to load and run a script that will modify your underlying data just a bit, and here’s why.
In the real world, the spreadsheet you received from your 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 are very likely not 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 that they’re recent.
Another thing that 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 your data. You need to drop and recreate your sequences so that 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.
Lastly, you’re going to create a function that, when passed a status name such as OPEN, will pass you back the ID for that status. This function will be used in a number of places, as 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, there is an icon menu in the upper right corner of the screen that provides quick links to each of the other tools. In Figure 4–19, the hand-shaped pointer is pointing to the SQL Scripts icon.
Figure 4–19. The icon based Quick Menu in SQL Workshop
Here’s what to do to create the Lookup function to return status ID codes:
1. Click on the SQL Scripts icon in the SQL Workshop’s Quick Menu. If you’re not already in the SQL Workshop, use the pull-down menu from the SQL Workshop tab and chose SQL Scripts.
2. Click the Upload button in the upper right section of the screen.
3. Click on Browse 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 do not need to give the script a name; it will default to the name of the script as it appears at the OS level.
Once the file has been uploaded, you’ll be presented with the 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 are able to run the script from the edit screen as well.
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 Script report).
5. As shown in Figure 4–20, you’ll be asked to make a selection between Run in Batch and Run Now. Select Run Now.
Figure 4–20. Choose whether to Run in Batch or Run Now
The script will be run and you will immediately be taken to the Manage Script Results page. You’ll most likely see that your script status is COMPLETE.
6. Click on the View Results icon at the very right of the report row to see the results of the script. Figure 4–21 shows the button 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 ran. The default view shows you 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 your 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 Detailed. Doing so will give you far more insight, especially if you have a script that has errors during execution. Figure 4–23 shows a detailed view.
Figure 4–23. The Detail view of the script results
In either view you can quickly see if the script encountered any errors by scrolling to the bottom of the page and looking at the report footer where the report will display 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 process from your run of the script.
Figure 4–24. In the footer of either report is the success summary for the script.