• 35
• Developer 2000 Integration
• Displaying Oracle Graphics in Oracle Forms
• Executing Oracle Reports from Oracle Forms
• Displaying Oracle Graphics in Oracle Reports
• Interfacing to Non-Oracle Applications
• Interfacing with the Windows API and other DLLs
• Summary

35

Developer 2000 Integration

The previous three chapters discussed the Developer 2000 components as independent, standalone tools. Based on Oracle's concept of the open-enterprise connectivity, each of the Oracle tools have been designed to be combined into a multi-faceted, integrated application. Oracle Forms can display graphics on the presentation canvas, and reports can be spawned from within the tool. Additionally, Oracle Reports can display an Oracle Graphic's chart directly in the report itself. In addition to the ability of the Developer 2000 tools to work with each other, the Oracle tools have the ability to work in an integrated environment with other compatible non-Oracle applications.

This chapter will describe the steps necessary to produce these integrated applications quickly.

Displaying Oracle Graphics in Oracle Forms

Oracle Graphics charts can be integrated into an Oracle Forms module using specific forms objects and built-in procedures and functions. An example of a module that integrates these tools is shown in Figure 35.1. In this form, the annual sales for all warehouses is shown in standard form text fields, and a pie chart that indicates the relative contribution of each warehouse is shown next to the text items. Below these text items, the monthly shipments for the current warehouse is shown in a bar chart. The user may click on any of the pie segments to select a record in the form. Additionally, if the user selects any record in the form, the bar chart will change to show data for the new warehouse.

Figure 35.1. Integrated Oracle Forms and Oracle Graphics module.

To build this module, first build the two graphics modules using the Oracle Graphics Designer. The first chart should be based on the following query:

Next, define a pie chart for this query using the pie with depth attribute. After the pie chart is displayed in the layout editor, double-click on one of the pie segments to present the trigger-definition form for the pie slices. Select the drill-down tab, and set this item to set the P_wh_code parameter equal to hist_wh_code on a mouse down trigger. (Note, do not select a query for the drill-down trigger.)

The next chart will be a bar-chart with shadow and will be based on the following query:

The P_wh_code parameter will hold the value for the warehouse code for the chart.

Now, create the form for the WH_ANNUAL_SHIPS view displaying the warehouse name and total annual shipments for 1994. Next, create a control block to hold the chart items and using the chart item tool in the layout editor, create the two areas for the Graphics charts on the canvas. These items will be linked to the Graphics modules using PL/SQL. To use the Oracle Graphics built-in program units, the OG.PLL library must be attached to the form.

The first trigger that should be created is the WHEN-NEW-FORM-INSTANCE trigger that will set up the charts as soon as the form is started. The bar chart requires parameters to define the warehouse that should be displayed in the chart. This trigger should be defined as follows:

The parameters for the OG.Open procedure are the chart module name, form display object, clip indicator, refresh indicator, and parameter list id. By defining the clip indicator in this procedure call as FALSE, the chart will be included on the form by scaling it to fit within the defined area. The default value of TRUE will cause the chart to be clipped to fit within the display. Now, the mouse down trigger interface must be built in the form. For the PIE_CHART item, create a WHEN_MOUSE_CLICK procedure as follows:

The GETCHARPARAM function will extract the parameter from the first chart, and the OG.REFRESH procedure will pass the new parameter value to the second chart.

Executing Oracle Reports from Oracle Forms

Printing an Oracle Reports 2.5 report from Oracle Forms requires the use of a single built-in procedure, RUN_PRODUCT. To demonstrate this concept, open the form that was created in Chapter 32 in the section on multi-block relationships. This form was used to enter new orders into the system. The boilerplate graphic's report that was built in Chapter 33 was a Shipping Document for an order. This two modules will be integrated using a form button with a WHEN-BUTTON-PRESSED trigger. First create a new button on the form canvas labeled Print Document, then create the trigger as:

As indicated by this module, the command line parameters for the R25RUN command are passed to the product as part of the parameter list. This is all that is needed to run a report directly to the printer from Oracle Forms. Also, note that the report will be run asynchronously in background, while the operator is free to execute other tasks as the report is printing. The RUN_PRODUCT built-in may also be used to invoke Oracle Graphics from Oracle Forms; however, the Graphics chart will not have the same interactive functionality as shown in the prior section.

Displaying Oracle Graphics in Oracle Reports

Oracle Graphics can be added to a Report by using the Oracle Graphics tool in the Reports Layout Editor. To demonstrate this concept, open the first report created in Chapter 33.

Select the Oracle Graphics tool from the tool palette, and drag an area for the chart below the main report layout. The new object will display a chart in the designated area. Double-click in this area to display the chart definition dialog box as shown in Figure 35.2. Name this object D_performance, and define the Graphics File as g35oun01.ogd. Run the report and save the new report in a file.

Figure 35.2. Oracle Graphics Chart Definition dialog box.

Interfacing to Non-Oracle Applications

Oracle Forms 4.5 provides the ability to interface with non-Oracle applications, such as Microsoft Excel or Microsoft Word, using OLE2 protocol as defined by Microsoft Corporation. For example, the Customer Credit Maintenance Form shown in Figure 35.3 includes an Excel Spreadsheet embedded directly as part of the form. Whenever a user double-clicks on the spreadsheet box, the complete functionality of Microsoft Excel will be included in the form.

Figure 35.3. Integrating an OLE Application into Oracle Forms.

To construct this form module, first build the CUSTOMERS block as shown displaying the customer number, name, and the credit information fields. Now, select the OLE2 tool from the palette in the Layout Editor. Drag an area on the canvas that will hold the OLE container. (Note: When the tool is activated, the actual area for the tool will appear larger to accommodate the row and column headers, as well as the scroll bar information.)

Select the property sheet for the OLE object, and modify the properties as indicated in Table 35.1.

Content

Now select the OLE object in the Layout Editor and click the right mouse button to display the OLE object menu. Select Insert Object from the menu to display the dialog box shown in Figure 35.4. Select Excel Worksheet and click on OK. (Note: The display icon check box will cause the item to display as an Excel icon until it is activated.) At this point, the Excel spreadsheet is embedded into the form module. To initialize the spreadsheet, select the edit spreadsheet function from the OLE object menu (right mouse button) and create the initial spreadsheet. For more information, refer to your Excel Documentation to understand the functions of this tool.

Figure 35.4. OLE Object Type definition dialog box.

These techniques can be used to embed an OLE compliant application in an Oracle Forms module. Alternatively, instead of embedding an application in the form, OLE linking can be used. OLE linking causes the form module to link directly to an existing OLE compliant file, such as any .XLS Excel Spreadsheet. To link the file, the only differences needed would be to define the OLE Tenant Types property to Linked, and to select the existing file from the insert object menu pick.

Interfacing with the Windows API and other DLLs

Oracle Forms also allows the user to interface to subprograms or routines that have been written in a third-generation programming language such as C or COBOL. These functions must be loaded into a dynamic library, such as a .DLL file in Microsoft Windows, or a shared library in UNIX systems. These functions may be Oracle Pre-Compiler foreign functions or non-Oracle functions. The method for using either type of function is the same.

In order to use a foreign function, first the dynamic library must be loaded using the ORA_FFI.LOAD_LIBRARY procedure:

After the library has been loaded, the function must be registered in FORMS (example shown for a C subprogram module):

The next step would be to register the parameters for the module in the order in which they would appear in the function definition (as shown below for a C integer value):

Finally, the last step needed to register the function is to define the return value type for the function:

The PL/SQL interface to this function would then be created as follows:

The foreign function can then be called directly using PL/SQL IN parameters to return a value.

This interface is recommended over building user exits because libraries are only linked in as needed, and thus the form modules can be smaller. Additionally, using a user exit requires that the Oracle Forms runtime, designer need to be recompiled, and all forms need to run the version of the tools with the embedded user exit. As a result, user exits tend to require more resources than may be needed for each form.

Summary

This chapter has shown how the Oracle Developer 2000 tools can be used to integrate with each other, as well as non-Oracle applications and subprograms. As one of the key features of Oracle Corporation's concept of open enterprise computing, Developer 2000 integration is highly useful for state-of-the-art powerful applications.

One thing to note in using these features is the fact that all of these tools demand a large amount of resources. When multiple application modules and tools are combined, the limited resources of a PC will quickly be consumed, resulting in applications that can perform slowly. or not at all.

I am by no means attempting to dissuade anyone from using the integrated features of these tools. I am just trying to caution you as to the pitfalls that you will surely encounter.