| **Navigation:**  [[guide to the clarion examples.htm|Guide to Examples]] >====== Oracle Demo ====== | [[ole property demonstration.htm|{{btn_prev_n.gif|Previous page}}]][[guide to the clarion examples.htm|{{btn_home_n.gif|Return to chapter overview}}]][[people 1.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

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.htm|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.