This appendix explains how the components of Oracle Developer/2000 work with Personal Oracle7 for Windows 95 as a self-contained development environment. The principal Developer/2000 tools are
Forms 4.5 is the best choice as an Oracle applications development tool if your requirements include the following:
The following steps show you how to build a simple form using the Forms 4.5 Designer.
When you invoke the Forms 4.5 Designer, it displays the Object Navigator. (See Figure B.1.) This graphical tool simplifies the process of locating an object to view or to modify its properties.
Figure B.1.The Object Navigator. Before you can do anything useful with the Designer, you must connect to an Oracle database. In this example, you connect to Personal Oracle7. In the File menu, select the Connect option. The Designer displays a Connect dialog box. (See Figure B.2.) Enter a valid Oracle username and password but leave the Database box blank. Click the Connect button to establish the connection to Personal Oracle7.
Figure B.2.Connecting to Personal Oracle7. After the Oracle connection has been established, select Edit | New Block. The Designer displays a window with four tabbed folders--;General, Items, Layout, and Master/Detail. On the General tab, the Base Table is set to <NONE>. (See Figure B.3.) Click the Select button to the right of <NONE> to choose a base table for the block.
Figure B.3.Creating a new block. The Designer displays a Tables window on which you can choose whether to display all tables or only those owned by the current user. (See Figure B.4.) Use the default settings and click OK.
Figure B.4.Choosing options for displayed tables. In this example the list shows all the tables owned by FRAYED_WIRES. Scroll down to the desired table, highlight it, and click OK. (See Figure B.5.)
Figure B.5.Selecting a new block's base table. After you've selected a base table, the Forms 4.5 Designer displays the new block's base table and sets the name of the block to the base table name. (See Figure B.6.) ***XBDPO06***
Figure B.6.New block options with base table. Once you've chosen a base table, you can select the columns to include on the block. Click the Items tab and then click the Select Columns button. (See Figure B.7.)
Figure B.7.Using the items page for new block options. The Designer displays a scrollable list of the columns in the Repair_Header table. By default, all columns are included in the block. To exclude a column from the generated block, simply click the column in the list. (See Figure B.8.) The + to the left of the column name changes to a X.
Figure B.8.Selecting columns for a new block. When you click OK in the New Block Options window, the Designer generates the new block. The Object Navigator appears and displays the new generated block. (See Figure B.9.)
Figure B.9.The Object Navigator displays the new block. To run the generated form, select File | Run. The Designer displays a Working. . . message in the lower-left corner of the window. The generated form appears with a menu bar. (See Figure B.10.) Notice that the form is empty; by default, a generated block does not perform an automatic query of its base table (in contrast to Oracle Power Objects--;see Appendix E for more information).
Figure B.10.Running the generated form. To query the base table (Repair_Header), select Query | Execute. The first row in the table appears. (See Figure B.11.) To navigate through the retrieved records, select Next or Previous from the Record menu. To return to the Forms 4.5 Designer, select Action | Exit.
Figure B.11.Querying the base table. Now you are ready to construct a simple master/detail form. Create another block by selecting Designer Edit | New Block. For the new block, select Repair_Item as the base table and select the columns from Repair_Item that you want to appear in the detail block. To establish the relationship between the two blocks, click the Master/Detail tab. Click the Select button to choose the Repair_Header as the master block. (See Figure B.12.)
Figure B.12.Choosing the master block. Click OK to return to the Object Navigator. The Object Navigator now displays both blocks: Repair_Header and Repair_Item. (See Figure B.13.)
Figure B.13.Object Navigator displays both blocks. Run the generated form by selecting File | Run. The Designer displays a Working. . . message in the lower-left corner of the window. The generated form now contains two blocks. (See Figure B.14.)
Figure B.14.Running the master/detail form. To query both blocks, select Query | Execute. You see the first row in the Repair_Header table along with its detailed records from the Repair_Item table. (See Figure B.15.) To navigate through the retrieved records, select Next or Previous from the Record menu. To return to the Forms 4.5 Designer, select Action | Exit.
Figure B.15.Querying the master/detail form. Of course, the default form that is generated by the Forms 4.5 Designer will probably need some adjusting. For example, as you can see in some of the previous figures, many of the column headings overlay each other. To correct this problem, invoke the Layout Editor to adjust the appearance of the form. (See Figure B.16.)
Figure B.16.Using the Layout Editor to adjust the appearance of the form. Forms 4.5 and Reports 2.5 are both equipped with a built-in PL/SQL engine. When you generate a form, the Forms 4.5 Designer creates several application triggers to control the behavior of the form. Figure B.17 shows an example of one of the triggers that has been generated. You can create, modify, or drop these triggers with the PL/SQL Editor.
Figure B.17.Viewing a trigger with the PL/SQL Editor.
Oracle Browser is an ad hoc query tool designed for casual Oracle users. It is now part of the suite of Oracle products called Discoverer/2000. Browser enables a user to define a query without having any knowledge of SQL. Once a query has been created, it can be stored for later use. The query can be used to produce simple reports. Also, a retrieved data set can be exported as an Excel or 1-2-3 spreadsheet.
As with the other Developer/2000 and Discoverer/2000 components, Browser runs in three environments: Windows, Macintosh, and Motif. Here's an example of how to connect Browser to a Personal Oracle7 database and execute a query.
To begin, invoke the Browser 2.0 User utility. You see a Connect dialog box. Type in the Oracle username and password but leave the Connect field empty. (See Figure B.18.) Click OK to establish the connection to Personal Oracle7.
Figure B.18.Establishing the connection to Personal Oracle7. Next, Browser displays a radio button group with three choices. The default choice is for Browser to create a new query. (See Figure B.19.) Click OK for the default choice.
Figure B.19.Choosing to create a new query. A window appears with a list of tables that you can select. Highlight the table you wish to query and click Include. (See Figure B.20.) You see an hourglass while Browser constructs a list of the columns in the selected table. When the hourglass disappears, click Close to choose the columns.
Figure B.20.Selecting a table. The untitled query appears in a window with each column and a graphical representation of its datatype. (See Figure B.21.)
Figure B.21.The table's columns are displayed. To proceed, you should save the query. Select File | Save As. Specify the directory and filename to be used for the query. (See Figure B.22.) The default file extension for a Browser query is .brw.
Figure B.22.Saving a query. After the query is saved, select the columns to be displayed by selecting Results | Display. The selected table's columns appear in the list on the left side of the window. On the right is a list of columns that will be retrieved by the query. (See Figure B.23.) To select a column for retrieval, simply highlight it from the list on the left and click the Copy button. When you're finished choosing the columns to retrieve, click Execute.
Figure B.23.Choosing the columns to retrieve. Finally, Browser executes the query and displays the results in a Results window. (See Figure B.24.)
Figure B.24.The query results are shown.
Procedure Builder is a handy tool for creating and maintaining stored procedures and functions. As with the other pieces of Developer/2000, Procedure Builder works well with Personal Oracle7. When you invoke Procedure Builder, a window entitled PL/SQL Interpreter appears. (See Figure B.25.)
Figure B.25.The PL/SQL Interpreter window. To connect to Personal Oracle7, choose Connect from the File menu. Enter the Oracle username and password but leave the Database box empty. (See Figure B.26.) Click Connect to establish the connection to the Personal Oracle7 instance.
Figure B.26.Connecting to Personal Oracle7. Once you connect to the database, Procedure Builder displays an object hierarchy. (See Figure B.27.)
Figure B.27.Procedure Builder displays PL/SQL object hierarchy. To view a stored procedure or function in the Personal Oracle7 database, select Tools | Stored Program Unit Editor. Procedure Builder queries Personal Oracle7 to retrieve any stored procedures or functions. Figure B.28 is an example of a stored function retrieved and displayed by Procedure Builder.
Figure B.28.Displaying a stored function.
Reports 2.5 is a full-featured report writer that can be used in the Windows, Macintosh, or Motif environment. With all of its features, Reports 2.5 is capable of building complex reports. Like Forms 4.5, Reports 2.5 also has a built-in PL/SQL engine for creating report triggers that are invoked at execution time.
Now you can build a simple report from the Personal Oracle7 database. When the Reports 2.5 Designer is invoked, the Object Navigator appears. (See Figure B.29.)
Figure B.29.The Object Navigator. To connect to Personal Oracle7, choose File | Connect. Enter the Oracle username and password but leave the Database box blank. (See Figure B.30.) Click OK to establish the connection to the Personal Oracle7 instance.
Figure B.30.Connecting to Personal Oracle7. For Reports 2.5, the first step in building a report is to create a data model--;a graphical representation of the tables to be queried and their relationships. Select Tools | Data Model Editor. The Data Model Editor window displays an empty screen. To create a query, click the SQL icon from the Data Model Editor toolbox and draw the query on the screen. (See Figure B.31.)
Figure B.31.Creating a query. Double-click the Q_1 rounded rectangle. The Reports Designer displays a tabbed folder. (See Figure B.32.) Click Tables/Columns to select a table for the query.
Figure B.32.The query parameters. In the Table and Column Names window (see Figure B.33), scroll down to the Repair_Header table and select it by clicking the mouse. The Reports Designer retrieves the Repair_Header table's columns and displays them in the list of columns. (See Figure B.34.) Highlight the * to include all the Repair_Header columns in the query and click Select-from and Close.
Figure B.33.Choosing the table.
Figure B.34.Choosing the columns. The Reports Designer constructs the SELECT statement as shown in Figure B.35. Click OK to continue.
Figure B.35.The constructed SELECT statement. The Data Model now displays the query and its associated report group G_1, which consists of all columns in the Repair_Header table. (See Figure B.36.)
Figure B.36.The generated report group. To select a default layout for the report, click the default layout icon from the Data Model Editor toolbar--;it's a green drafting triangle. The Default Layout window appears. (See Figure B.37.) Use the default Tabular layout.
Figure B.37.The Default Layout window. You can view the report group by clicking the Data/Selection tab. You can view or modify each column, its label, and its width and height. (See Figure B.38.) Click OK to continue.
Figure B.38.Viewing the report group. The Reports Designer displays the default layout. (See Figure B.39.)
Figure B.39.The generated default layout. To run the report, click the green signal light icon. Before the report actually appears, you see a Runtime Parameter form. (See Figure B.40.) On this form, you can direct the report to the screen, printer, or other destinations.
Figure B.40.The Runtime Parameter form. To view the generated report on the screen, click Run Report. You might have some trouble finding the Previewer window. To find it, select Window | Previewer. Figure B.41 contains the first page of the generated report.
Figure B.41.Page 1 of the generated report.