1 / 64

Chapter 10 Using SQL in Data Blocks, Debugging, and Creating Calendars

Chapter 10 Using SQL in Data Blocks, Debugging, and Creating Calendars. In this chapter you will: Base your data blocks on custom SQL code to increase performance and enhance capabilities Add your own DML statements to the data block Use Instead-Of triggers and database triggers

gordy
Download Presentation

Chapter 10 Using SQL in Data Blocks, Debugging, and Creating Calendars

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 10 Using SQL in Data Blocks, Debugging, and Creating Calendars Oracle9i Developer: Developing Web Applications with Forms Builder

  2. In this chapter you will: • Base your data blocks on custom SQL code to increase performance and enhance capabilities • Add your own DML statements to the data block • Use Instead-Of triggers and database triggers • Base a data block on PL/SQL stored procedures to perform SQL and DML operations • Identify, create, and modify database objects using the Object Navigator • Debug the form manually or with Forms Builder Debugger • Add a calendar to a form Oracle9i Developer: Developing Web Applications with Forms Builder

  3. Basing a Data Block on Custom PL/SQL • Data blocks that use a table as the data source seldom have all of the needed attributes. • Databases are normalized and descriptive values are contained in related tables. Oracle9i Developer: Developing Web Applications with Forms Builder

  4. Basing a Data Block on Custom PL/SQL Oracle9i Developer: Developing Web Applications with Forms Builder

  5. Basing a Data Block on Custom PL/SQL • There are three ways to bring descriptive values into a form: • Post-Query trigger • View • From Clause Query Oracle9i Developer: Developing Web Applications with Forms Builder

  6. Basing a Data Block on Custom PL/SQL • The Post-Query trigger fires a Select statement retrieving descriptive values for each record retrieved from the database. • This method allows the developer to use the Data Block wizard to create the data block. • Using this method the developer does not have to create Select, Insert, Update, and Delete statements. Oracle9i Developer: Developing Web Applications with Forms Builder

  7. Basing a Data Block on Custom PL/SQL • Operators cannot modify descriptive values using the Post-Query method. • Operators cannot use descriptive values as search criteria. • The Post-Query method can cause performance problems when large numbers of records are returned to the data block. This causes a large number of secondary queries to occur. Oracle9i Developer: Developing Web Applications with Forms Builder

  8. Basing a Data Block on Custom PL/SQL • The View method allows the developer to use the Data Block wizard to create the data block. • The View method can increase performance by reducing the number of Select statements issued by the client to the server. • Descriptive values can be used as search values using a view. • The data block cannot execute default Insert, Update, and Delete statements against the view. Oracle9i Developer: Developing Web Applications with Forms Builder

  9. Basing a Data Block on Custom PL/SQL • Special form triggers can be created to update view values. • Views are database objects and are not associated to a form. It is possible to modify a view without modifying the form. This may disable the form. • The From Clause Query method can increase performance by reducing the number of Select statements issued by the client to the server. • The From Clause Query is created using a data block’s properties. It is closely associated to the form. Oracle9i Developer: Developing Web Applications with Forms Builder

  10. Basing a Data Block on Custom PL/SQL • Descriptive values can be used as search values using a From Clause Query. • The data block cannot execute default Insert, Update, and Delete statements against the From Clause Query. • The From Clause Query method is not supported by the Data Block wizard. Oracle9i Developer: Developing Web Applications with Forms Builder

  11. Basing a Data Block on Custom PL/SQL • To base a data block on a From Clause Query, perform the following: • Set the data block’s Query Data Source Type property to From Clause Query. • Add the Select statement to the Query Data Source Name property. • Manually create a data block item for each expression in the Select statement. • Set the Name of the data block item to the expression name or set the Column Name property to the name of the expression. Oracle9i Developer: Developing Web Applications with Forms Builder

  12. Basing a Data Block on Custom PL/SQL Oracle9i Developer: Developing Web Applications with Forms Builder

  13. Adding Your Own DML Statements • Data block records retrieved using the View or From Clause Query methods can be modified using the following triggers: • On-Lock • On-Insert • On-Update • On-Delete • On-Type triggers replace the action that fired them. Oracle9i Developer: Developing Web Applications with Forms Builder

  14. Adding Your Own DML Statements • On-Type triggers can be used to maintain multiple tables in the complex Select statements used in the View and From Clause Query methods. • Whenever a value is modified on the data block, Forms Builder locks the corresponding record in the database. • Locking prevents another user from modifying the record before the operator can commit the record. Oracle9i Developer: Developing Web Applications with Forms Builder

  15. Adding Your Own DML Statements • The data block’s Locking Mode property controls when the form locking occurs. It has the following settings: • Automatic – Forms Builder places a lock on the data record. • Immediate – Same as automatic but is only used for Oracle databases. • Delayed – Causes Forms Builder to lock the record only when the change is posted to the database rather than when the user is editing the record. Oracle9i Developer: Developing Web Applications with Forms Builder

  16. Adding Your Own DML Statements Oracle9i Developer: Developing Web Applications with Forms Builder

  17. Adding Your Own DML Statements • Whenever you use a From Clause Query or view, you must provide a locking mechanism. • This requires an On-Lock trigger. • Placing the statement “Null” in the On-Lock trigger satisfies Forms Builder but does not actually lock the record. It has the same effect as the Delayed Locking Mode Property setting. Oracle9i Developer: Developing Web Applications with Forms Builder

  18. Adding Your Own DML Statements • To actually lock a record, add a Select statement to the trigger that retrieves the record. Use the For Update clause to lock the record. Begin Select deptno into :block_name.deptno from dept where deptno = :block_name.deptno for update; Exception when others then message(‘Error occurred when locking record’); End; Oracle9i Developer: Developing Web Applications with Forms Builder

  19. Using Instead-Of and Database Triggers • The Oracle database has two types of triggers: database triggers and Instead-Of triggers. • Database triggers are fired before or after an insert, update, or delete transaction on a table. • Database triggers are also fired when a form performs a transaction. • This can cause a synchronization problem for the form operator. Oracle9i Developer: Developing Web Applications with Forms Builder

  20. Using Instead-Of and Database Triggers • If the form executes a transaction and a database trigger modifies a value, the form record and the database record do not match. • As soon as the Forms record is committed, a “FRM-40654: Record has been updated by another user. Re-query to see change.” error is issued. Oracle9i Developer: Developing Web Applications with Forms Builder

  21. Using Instead-Of and Database Triggers Oracle9i Developer: Developing Web Applications with Forms Builder

  22. Using Instead-Of and Database Triggers • To avoid this error you must clear the block and query the database again so that the form record matches the database record. • Instead-Of triggers are fired when a DML transaction is launched against a view. • Instead-Of triggers are similar to On-Type triggers except they are stored in the database. • Instead-Of triggers can cause the form developer trouble when the developer is unaware of the trigger. The developer may perform a great deal of debugging to find out the form is actually operating correctly. Oracle9i Developer: Developing Web Applications with Forms Builder

  23. Using Instead-Of and Database Triggers • The data block’s Key Mode property should be set to Updateable when a view is to be updated. • You should also make sure that at least one of the data block item’s Primary Key properties is set to Yes. Oracle9i Developer: Developing Web Applications with Forms Builder

  24. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations • Data blocks can be based on PL/SQL stored procedures. • Stored procedures allow you to utilize complex logic in the form’s Select, Insert, Update, and Delete transactions. • Placing code that affects a common entity into a package has an element of object-oriented design. • It also enhances reusability since an object can access the database and use the same stored procedures. Oracle9i Developer: Developing Web Applications with Forms Builder

  25. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations • Stored procedures used in Select statements must have a cursor variable. • A cursor variable is composed of PL/SQL records that match the expressions in a Select clause. • The Select statement result set is placed into the cursor variable. The cursor variable is passed to the data block since the cursor variable is a stored procedure parameter. Oracle9i Developer: Developing Web Applications with Forms Builder

  26. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations Oracle9i Developer: Developing Web Applications with Forms Builder

  27. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations • PL/SQL records and tables are created using the Type command. • The Data Block wizard can be used to associate the data block with a stored procedure. • A stored procedure can be used as the data source of the data block as well as the source of the Insert, Update, and Delete statements issued by the data block. • Stored procedures used in forms are often put in packages. Oracle9i Developer: Developing Web Applications with Forms Builder

  28. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations Oracle9i Developer: Developing Web Applications with Forms Builder

  29. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations Oracle9i Developer: Developing Web Applications with Forms Builder

  30. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations Oracle9i Developer: Developing Web Applications with Forms Builder

  31. Basing Your Block on PL/SQL Stored Procedures to Perform SQL and DML Operations Oracle9i Developer: Developing Web Applications with Forms Builder

  32. Using the Object Navigator to View, Create, and Modify Database Objects • The Object Navigator Database Objects node is a good tool for identifying and maintaining database objects. • This node displays all of the Oracle IDs on the current Oracle Installation. Oracle9i Developer: Developing Web Applications with Forms Builder

  33. Using the Object Navigator to View, Create, and Modify Database Objects Oracle9i Developer: Developing Web Applications with Forms Builder

  34. Using the Object Navigator to View, Create, and Modify Database Objects • Under the Oracle ID are the following child nodes: • PL/SQL Stored Program Units • PL/SQL Libraries • Java Source Objects • Java Class Objects • Tables • Views • Instead-Of Triggers • Columns Oracle9i Developer: Developing Web Applications with Forms Builder

  35. Using the Object Navigator to View, Create, and Modify Database Objects • PL/SQL objects can be created, maintained, and deleted from the Object Navigator. • To create a PL/SQL object, select the appropriate node and press the Create tool. You will be prompted for the name of the object and the type of object. After entering these values in the dialog box the PL/SQL editor will be displayed. • To maintain a PL/SQL object, double-click the object’s icon to display the PL/SQL editor containing the object script. Oracle9i Developer: Developing Web Applications with Forms Builder

  36. Using the Object Navigator to View, Create, and Modify Database Objects • To delete a PL/SQL object, select the object and click the Delete tool. • Saving the PL/SQL object causes Oracle to compile the object. Errors will be displayed in the Error pane of the PL/SQL editor. • Uncompiled PL/SQL object names are followed by an *. Oracle9i Developer: Developing Web Applications with Forms Builder

  37. Debugging a Form Manually or with Forms Debugger • As forms are developed, mistakes result. • Two types of errors can occur: • A value was not calculated correctly • The application did not behave as the developer thought it would • The process of finding and correcting these errors is called debugging. Oracle9i Developer: Developing Web Applications with Forms Builder

  38. Debugging a Form Manually or with Forms Debugger • Regardless of whether you manually debug your form or use a debugging tool such as Forms Debugger, debugging is a four stop process: • Develop a theory on the cause for the incorrect action or value. • Develop a test that proves or disproves the theory. • Perform the test and compare the results to the theorized results. • If the results prove your theory, correct the condition. If the results do not, return to Step 1. Oracle9i Developer: Developing Web Applications with Forms Builder

  39. Debugging a Form Manually or with Forms Debugger • The Message built-in is an excellent debugging tool. It displays a value at a specific point in the application. • The Message built-in displays a value on the status line. The value is often a concatenation of several expressions. • The Message built-in can display for the developer the value of a variable verifying whether a calculation is correct. • The Message built-in can help the developer determine whether the application has entered or not entered a target construct verifying the logic of the application. Oracle9i Developer: Developing Web Applications with Forms Builder

  40. Debugging a Form Manually or with Forms Debugger message (‘1’||security_value); if security_value != ‘Y’ then message (‘2 Entered the if construct’) show_alert(‘generic’) Oracle9i Developer: Developing Web Applications with Forms Builder

  41. Debugging a Form Manually or with Forms Debugger Oracle9i Developer: Developing Web Applications with Forms Builder

  42. Debugging a Form Manually or with Forms Debugger • Forms Debugger is the Forms Builder debugging tool. • It allows the developer to do the following: • Stop the processing at a specific point or statement. At that point, the developer can inspect the values of all system variables, global variables, and trigger variables. • Step through the application’s PL/SQL statement by statement. • View the code statements currently being executed while simultaneously viewing the form. • Change the value of any variable during the debugging process. Oracle9i Developer: Developing Web Applications with Forms Builder

  43. Debugging a Form Manually or with Forms Debugger • The Forms Builder Debug Console window is the debugging tool. It is opened by: • Adding a breakpoint to a trigger statement and executing the form using the Run Form Debug tool. • Adding a breakpoint to a trigger statement and clicking the Debug/Debug Module menu selection. • Clicking the Debug/Debug Console menu selection. Oracle9i Developer: Developing Web Applications with Forms Builder

  44. Debugging a Form Manually or with Forms Debugger • A breakpoint is a designation placed in the PL/SQL editor of a form trigger that causes the application to pause when the form is run in the Debug mode. • The breakpoint is added by double-clicking the left pane of the PL/SQL editor adjacent to the target statement. • Forms Builder will stop execution before the target statement is executed. • Forms Builder will only stop the application on an actual statement. PL/SQL keywords are not statements. Oracle9i Developer: Developing Web Applications with Forms Builder

  45. Debugging a Form Manually or with Forms Debugger • The Debug Console has tools to display seven different windows. These are: • Form values • Global/system variables • Breakpoints • Variables • Stack • Watch • PL/SQL Packages Oracle9i Developer: Developing Web Applications with Forms Builder

  46. Debugging a Form Manually or with Forms Debugger Oracle9i Developer: Developing Web Applications with Forms Builder

  47. Debugging a Form Manually or with Forms Debugger • The Form Values window displays the data block items and their current values. The values can be modified by overtyping. A tab in the window displays a page containing form parameters. • The Global/System Variables window has three tab pages: • Global variables • System variables • Command-line variables Oracle9i Developer: Developing Web Applications with Forms Builder

  48. Debugging a Form Manually or with Forms Debugger • The Breakpoints window has two tab pages: • Active breakpoints that can be enabled and disabled. • Break on Exception that displays various database exception errors. • The Variables window shows variables that exist within the current PL/SQL objects. The values can be modified. • The Stack window displays the current and subordinate PL/SQL objects such as triggers or stored program units. Oracle9i Developer: Developing Web Applications with Forms Builder

  49. Debugging a Form Manually or with Forms Debugger • The Watch window contains variables that the developer wants to monitor through the debugging session. • The PL/SQL Packages window displays the variables and associated values in PL/SQL packages used by the form. • Windows can be moved from the Console window using the Dock/Undock tools. • Figure 10-19 illustrates the Forms Builder IDE during a typical debugging session. Oracle9i Developer: Developing Web Applications with Forms Builder

  50. Debugging a Form Manually or with Forms Debugger Oracle9i Developer: Developing Web Applications with Forms Builder

More Related