Assignment 1: Using the DBMS_ALERT Package
Brewbean's wants to add an alert in the product management page to advise the manager of a product stock level falling below the reorder point. The alert simply needs to state which product needs reordering.
1. Start SQL Developer, if necessary.
2. Create a database trigger on the BB_PRODUCT table, using the DBMS_ALERT package to send an alert when the stock value falls below the reorder value. Name the alert reorder and have it contain a message stating that a product needs to be reordered. Include the product number in the message.
3. If an error is raised because you don't have permission to use the DBMS_ALERT package, you need to log on as SYSDBA and enter the following command to grant the necessary permissions. (This code assumes you're the user PLBOOK.) GRANT EXECUTE ON DBMS_ALERT TO PLBOOK;
4. Start a second session of SQL Developer as SYSTEM MANAGER.
5. Type and run the following block to register the alert: BEGIN DBMS_ALERT.REGISTER('reorder'); END;
6. Type and run the following block to initiate the wait for an alert: DECLARE lv_msg_txt VARCHAR2(25); lv_status_numNUMBER(1); BEGIN DBMS_ALERT.WAITONE('reorder', lv_msg_txt, lv_status_num, 120); DBMS_OUTPUT.PUT_LINE('Alert: '|| lv_msg_txt); DBMS_OUTPUT.PUT_LINE('Status: '|| lv_status_num); END;
7. The preceding code sets a wait period of two minutes. Return to the first SQL Developer session. Type and run the following code to cause the alert to fire: UPDATE bb_product SET stock = stock - 2 WHERE idproduct = 4; COMMIT;
8. Return to the second SQL Developer session. The alert message should be displayed.
Assignment 2: Using the DBMS_DDL Package
Because Brewbean's is in the middle of constructing its application and making several database modifications, the developers are going to build a procedure to recompile all invalid objects automatically.
1. Start SQL Developer, if necessary.
2. Type and run the following code: SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';
3. If you see the BB_JOBTEST procedure listed with a VALID status onscreen, skip to Step 7. If this procedure doesn't exist, proceed with Step 4.
4. Open the assignment02.txt file in the Chapter10 folder, and use its code to create the BB_JOBTEST procedure.
5. Type and run the following code: SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';
6. Verify that the query results list BB_JOBTEST with a VALID status, which tells you the procedure exists.
7. Type and run the following statement to modify the BB_JOBQ table. Because the BB_JOBTEST procedure uses this table, the procedure changes to the status INVALID, indicating the need for recompiling. ALTER TABLE bb_jobq MODIFY (msg VARCHAR2(30));
8. Type and run the following query to confirm the status INVALID: SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';
9. Create an anonymous block, using DBMS_DDL.ALTER_COMPILE to compile all INVALID objects. To simplify this process, retrieve all the INVALID objects in a cursor. Run the anonymous block.
10. Type and run the following query to confirm that these objects' status is now VALID: SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';
Assignment 3: Using the UTL_FILE Package to Read and Insert Data
Brewbean's has struck a deal with a new tea product supplier, who has sent a test file showing how files containing product names and descriptions will be submitted. Create a PL/SQL block, using the UTL_FILE package, to read from the file and insert data in the BB_PRODUCT table.
1. Start Windows Explorer. Open the tea.txt file in the Chapter10 folder. Check the file's contents to confirm that it contains product names and descriptions, and review the data's format.
2. Start SQL Developer, if necessary.
3. Create an anonymous block that reads each line in the tea.txt file and inserts the values in the BB_PRODUCT table. Use the UTL_FILE reading feature in a loop to read each line from the text file.
4. Run the block to perform the INSERT statements.
5. Type and run the following code. The results should display the three tea products from the tea.txt file. SELECT productname, description FROM bb_product;
Assignment 4: Using the UTL_FILE Package to Export Data ColumnsTheBrewbean's manager wants to have a file extracted from the database containing product information for inventory and cash flow analysis. The manager uses this file in spreadsheet software on a laptop computer. Using the UTL_FILE package, create a PL/SQL block that places data columns in a comma-delimited text file named prod_ext.txt in the c:\oraclass directory. The extracted file should contain one line per product and the following columns of the BB_PRODUCT table: IDPRODUCT, PRODUCTNAME, PRICE, TYPE, STOCK, ORDERED, and REORDER.
Assignment 5: Sending E-mail with UTL_SMTP
The Brewbean's manager decided he wants to get e-mail notifications when product stock levels fall below the reorder point. Create the BB_STKALERT_TRG trigger on the BB_PRODUCT table to perform this task. The e-mail body should state the product ID and name. Use the following UPDATE statement to set up a product to test:
UPDATE bb_product
SET stock = 26 WHERE idProduct = 4;
COMMIT;
Test the trigger with this code:
UPDATE bb_product
SET stock = stock - 2
WHERE idproduct = 4;
COMMIT;
When you finish the assignment, disable the trigger so that it doesn't affect other assignments.
Assignment 6: Using DBMS_OUTPUT Using the DBMS_OUTPUT package, create and run a PL/SQL block that displays lines for each product in the BB_PRODUCT table. If the stock level for a product is above the reorder point, only a single line should be displayed that looks like the following code:
Product 5 - Sumatra does NOT need ordering
If the stock level is below the reorder point, lines such as the following should be displayed:
Product 5 - Sumatra needs ordering!
Stock = 24 , reorder point = 25
Before running the block, issue the following statements to make sure product 4's stock level is below the reorder point:
UPDATE bb_product
SET stock = 24 WHERE idProduct = 4;
COMMIT;
Assignment 7: Using the DBMS_SQL Package
Brewbean's employees want an application page where they can query the CUSTOMER table for ID and last name based on one criterion on any customer information column. To perform this task, you create a procedure by using DBMS_SQL to set up a dynamic query. Name the procedure DYN_CUST_SP and verify that the procedure works by issuing two queries: one with the state NC and one with the e-mail value [email protected]. Keep in mind that any column can be used as a criterion, which could result in the query returning more than one row. For example, two customers might have the same last name. Therefore, this procedure needs to be able to handle multiple rows being returned.
1. Start SQL Developer, if necessary.
2. Open the assignment07.txt file in the Chapter10 folder, and use its code to create the DYN_CUST_SP procedure.
3. Next, you need to run the procedure with the two test cases. First, use the STATE column criteria, and run an anonymous block that includes the statement dyn_cust_sp ('state', 'NC');.
4. Now check the customer e-mail address by using an anonymous block that includes the statement dyn_cust_sp('e-mail', '[email protected]');.
Assignment 8: Using Native Dynamic SQL to Add ColumnsTheBrewbean's manager wants an application page that makes it easy for employees to add columns to the database. Create a procedure that accepts input and uses native dynamic SQL to perform this task. Test the procedure by adding a column named MEMBER to the BB_SHOPPER table with the data type CHAR(1).
1. Start SQL Developer, if necessary.
2. Open the assignment08.txt file in the Chapter10 folder. Review the uncompleted procedure code, and then finish the EXECUTE IMMEDIATE statement to allow column additions.
3. Create the procedure in SQL Developer.
4. Run the procedure by using the following block to add the MEMBER column: BEGIN dyn_addcol_sp('member','bb_shopper','CHAR(1)'); END;
5. Type DESC bb_shopper and press Enter to list the table structure and confirm the addition of the MEMBER column.
Assignment 9: Using Native Dynamic SQL for Product SearchesBrewbean's wants to allow customers to do product search by selecting a product name or description, and then typing a search term. Using native dynamic SQL, create a procedure named SEARCH_SP that returns the product name, description, and price based on users' search criteria. This procedure needs to handle multiple rows being returned.
Assignment 10: Understanding Business Intelligence
Business intelligence (BI) is a term used to describe providing more database power for users. Describe briefly what it means in applications, including the terms "data mining," "data marts," "OLAP," and "executive dashboards." What role might dynamic SQL play in BI? Describe BI products that Oracle offers.
Assignment 11: Using the Wrap Utility
In this assignment, you show how to use the Oracle wrap utility to obfuscate the BB_JOBTEST procedure's source code. Create an .sql file (job.sql) using the script file for the procedure (available in assignment02.txt in the Chapter10 folder of the data files). Use the wrap utility on the job.sql file to hide the script's source code. Check the file the utility produces.