|
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.
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:
select hist_wh_code,
sum (hist_ord_shipped) Shipments,
from warehouse_history
where hist_year = 1994
group by hist_wh_code;
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:
select to_char (to_date (to_char (hist_month_no), ' MM' ), ' MON' ) Month,
hist_ord_shipped Shipments
from warehouse_history
where hist_year = 1994
and hist_wh_code = :P_wh_code;
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:
declare
plist ParamList;
begin
execute_query;
OG.Open (' g35oun01.ogd' , ' CONTROL.PIE_CHART' , FALSE);
plist := Create_parameter_list (' chart_parms' );
Add_parameter (plist, ' P_wh_code' , TEXT_PARAMETER,:wh_annual_ships.wh_code);
OG.Open (' g35oun02.ogd' , ' CONTROL.BAR_CHART' , FALSE, TRUE, plist);
Destroy_parameter_list (plist);
end;
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:
declare
wh VARCHAR2(3);
plist ParamList;
begin
OG.mouse_down (' g35oun01.ogd' , ' CONTROL.PIE_CHART' );
wh := OG.GETCHARPARM (' g35oun01.ogd' , ' CONTROL.PIE_CHART' ,
' P_wh_code' );
go_block (' WH_ANNUAL_SHIPS' );
loop
if wh = :WH_ANNUAL_SHIPS.WH_CODE then
exit;
end if;
next_record;
end loop;
plist := create_parameter_list (' chart_parms' );
add_parameter (plist, ' P_wh_code' , TEXT_PARAMETER, wh);
OG.REFRESH (' g35oun02.ogd' , ' CONTROL.BAR_CHART' , plist);
destroy_parameter_list (plist);
end;
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.
 One thing to note when working with Graphics in Oracle Forms is that the displays may look different when displayed in Forms, especially when the chart is scaled to fit within the chart area. The primary problem
relates to the text labels on a chart due to the fact that Oracle Forms tries to use the closest fitting font to the defined display, often resulting in overlapping labels. The best advice is to define the chart with the smallest possible fonts, and hope
for the best. Honestly, building forms where everything fits properly can be done, but it will require a significant effort. Also, when building the chart, define the chart area as approximately the same size as it will be in the integrated module.
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:
declare
plist ParamList;
begin
plist := create_parameter_list (' print_parms' );
add_parameter (plist, ' P-order_no' , TEXT_PARAMETER,
to_char (:orders.order_no));
add_parameter (plist, ' PARAMFORM, TEXT_PARAMETER, ' NO' );
add_parameter (plist, ' DESTYPE' , TEXT_PARAMETER, ' PRINTER' );
add_parameter (plist, ' BATCH' , TEXT_PARAMETER, ' YES' );
RUN_PRODUCT (REPORTS, ' r35oun01.rdf' , ASYNCHRONOUS, BATCH,
FILESYSTEM, plist);
destroy_parameter_list (plist);
end;
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.
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.
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.
Table 35.1. OLE Object properties.
Property
|
Value
|
|
OLE In-Place Activation
|
True
|
|
OLE Activation Style
|
Double-Click
|
|
OLE Resize Style
|
Scale
|
|
OLE Tenant Types
|
Embedded
|
|
Show Do In Out
|
True
|
|
OLE Tenant Aspect
|
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.
Linking files in Oracle Forms requires a lot of resources on the client computer. Make sure that only necessary applications are running on a machine that has been maximized with RAM. Without a very powerful
machine, object linking results in frequent General Protection Faults.
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:
fh_mylib := ora_ffi.load_library (path, filename);
After the library has been loaded, the function must be registered in FORMS (example shown for a C subprogram module):
fh_func := ora_ffi.register_function (fh_mylib, function_name, ora_ffi.C_STD);
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):
ora_ffi.register_parameter (fh_func, ORA_FFI.C_INT);
Finally, the last step needed to register the function is to define the return value type for the function:
ora_ffi.register_return (fh_func, ORA_FFI.C_INT);
The PL/SQL interface to this function would then be created as follows:
FUNCTION ff_pls_equiv_my_func (ff_handle ORA_FFI.FUNCHANDLETYPE,
parm1 IN BINARY_INTEGER,
parm2 IN BINARY_INTEGER)
RETURN BINARY_INTEGER is
PRAGMA interface (C, ff_pls_equiv_my_func, 11265);
BEGIN
...
END;
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.
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.
| 
