Navigation: Guide to Examples >====== Oracle Demo ====== | |
The Oracle Accelerator example application (SVOracle.APP) is a simple order entry system created to demonstrate the Oracle file driver as a teaching example. It is located in the
\(CLARION ROOT)\EXAMPLES\ORACLE folder.
This example illustrates the methods described in the Oracle accelerator help topic.
Before running the application, install Oracle Accelerator as described in the Oracle Installation
topic, then start your Oracle database.
This section points out the programming techniques used to create the application. Although all procedures use the Clarion templates, some “tricks” have been used to optimize performance. In most cases these “tricks” are merely a line or two of embedded source code. In other cases the optimization is accomplished in the data dictionary. These methods are documented in the help for those procedures.
This program also demonstrates different methods to accomplish the same task. By understanding different approaches to problem solving, you can choose the best method for the job at hand.
Starting the Example Program
After you start the program, you should begin by initializing the data files. Under the
Utilities menu, choose the Copy Clarion Data… option or press the Copy button to create
the Oracle tables. This copies some Clarion data files to Oracle tables on the server to which you connected. When you are finished with the example program, you can use the Remove Data Files command to remove the sample tables from the server. You can recreate the tables at any time.
Procedure Descriptions
LoginWindow
Procedure template: WINDOW
This procedure displays an introductory screen to collect Oracle connection information
and opens a table on the Oracle server and keeps it open until the application is terminated. This avoids repeated connection and disconnection and prevents the logon screen from appearing every time a new file is opened.
Opening the first file takes longer than any other file because the initial connection to
Oracle is made at that time. By opening the first file from the first procedure, this time
delay is incurred early on in the program.
The LogInWindow procedure collects User Name, Password, and Server Name and concatenates them to initialize the GLO:Login global variable:
Glo:login=Clip(Glo:Name)&'/'&Clip(Glo:Password)&'@'&Clip(Glo:Server)
This procedure also collects this information and stores in an INI file using ABC calls to
do so. When you run to app at a later time, your last settings are remembered.
MainMenu
Procedure template: FRAME
This procedure is a basic Application Frame with a toolbar. The toolbar buttons initiate the most frequently used procedures.
ListOrders
Procedure template: BROWSE
This is a standard browse. The template generates a VIEW to access Orders and Client.
A command button (with a printer icon) calls the SingleInvReport procedure that prints a single invoice for the highlighted record.
UpdateOrders
Procedure template: FORM
A standard Form procedure populated with a browse box control and totals for the browse box. The browse box displays records from multiple tables.
ValidateAccountNum
Procedure template: BROWSE
This procedure is a standard Browse used to validate Customers when entering an order.
ValidateItemNumber
Procedure template: BROWSE
This is a standard Browse used to Validate items entered in the UpdateOrders Form.
GetOrderRecord
Procedure template: BROWSE
A simple browse procedure to browse all Orders in reverse date order and allow a selected order to be printed (using the SingleInvReport Procedure to print). The browse box displays records from multiple tables. Extension templates provide the select and print mechanisms.
SingleInvReport
Procedure template: REPORT
This is a standard Report to print a single invoice.
BatchChange
Procedure template: BROWSE
This is a simple browse list with two buttons. One calls PriceChange that uses Clarion code to affect changes to all rows on the Oracle backend. It also starts a timer that keeps track of how long it took to complete the action.
The other button uses SQL commands to affect the changes and it also uses a timer.
Both time values are displayed on the window so that comparisons in speed can be seen.
SQLBuilder
Procedure template: WINDOW
This procedure builds and executes an SQL select statement, then displays the results, demonstrating one way to provide ad hoc queries. Note however, that for the present, the number and data types of the SQL columns selected must match the number and data types of the fields declared in the receiving FILE structure. Therefore, you must declare a series of FILES that match the output of the potential SELECT statements, then you must control the number and sequence of columns selected, and finally, you must direct the output to the matching FILE. A mismatch generates a -921 error code from Oracle.
PLSQLFreeForm
Procedure template: WINDOW
This procedure allows you to write and execute PL/SQL code. PL/SQL is Oracle's procedural language extension for SQL (allows loops, branching, variable declaration and manipulation, etc.)
Use the pre-written PL/SQL block to use a CURSOR to change the area code in the Client file, or write your own free form code. Do not execute code that returns data (such as a SELECT statement). Although this is legal (see the SQLBuilder procedure), this particular procedure is not designed to receive data.
RemoveData
Procedure template: WINDOW
This procedure calls routines to remove the example data from the Oracle server. To allow other windows programs to work during this procedure, the routines are based on a timer. This conforms to the cooperative multi-tasking standard.
CpyToServer
Procedure template: WINDOW
This procedure creates the example tables and views on the Oracle server, then calls the appropriate Process procedure to add data to the tables. To allow other windows programs to work during this procedure, this routine is done based on a timer on the window. This conforms to the cooperative multi-tasking standard. On each timer event a new table is created, then the appropriate procedure is called to add the data to the table.
AddClients
Procedure template: PROCESS
Standard Process procedure that copies records from the Clarion file to the corresponding Oracle table.
AddItems
Procedure template: PROCESS
Standard Process procedure that copies records from the Clarion file to the corresponding Oracle table.
AddOrders
Procedure template: PROCESS
Standard Process procedure that copies records from the Clarion file to the corresponding Oracle table.
AddProducts
Procedure template: PROCESS
Standard Process procedure that copies records from the Clarion file to the corresponding Oracle table.
AddStates
Procedure template: PROCESS
Standard Process procedure that copies records from the Clarion file to the corresponding Oracle table.
PrintClients
Procedure template: BROWSE
An intermediate procedure using a standard Browse to select a client for which to print invoices. The menu option calling this procedure dictates which Report procedure is called when the user selects a client.
There are two Report procedures, both of which print similar reports; however, different methods are used to generate each report. This demonstrates the flexibility of the Clarion and Oracle tools plus the performance trade-off with each method.
The ClarionPrint Procedure uses related files in the Data /Table Pad. The ViewPrint Procedure uses an existing view on the Oracle server.
ClarionPrint
Procedure template: REPORT
This report prints all Invoices for a single client using standard Clarion files. This is the slower of the two methods used.
ViewPrint
Procedure template: REPORT
This report prints all Invoices for a single client using an SQL VIEW that is stored on the Oracle server. The view was created by the CpyToServer function.