Query Conversion Details

In the following discussion, Access queries that return data result sets (Select, Union, and Crosstab) are referred to as "Selection" queries. Access MakeTable, Append, Delete, and Update queries are termed "Action" queries and do not return any data.

By default, Scriptoria implements Access queries using the linked tables they include. Although linked QueryDefs are updatable, network and database performance will usually improve, sometimes dramatically, by creating Access pass-through queries based on Oracle views. Pass-through queries however, are not updatable.  

Select, Union, and Crosstab Queries

All Access "Selection" queries are converted to Oracle views. The only exception is Crosstab queries that return a variable range of values; i.e., those that do not supply Crosstab Column Headings. Oracle procedures are to used implement these run-time dependant queries. A minimal amount of manual effort is required to incorporate run-time dependant Crosstabs into Access front-end applications .

MakeTable, Append, Delete, and Update Queries

Converting Action queries to execute on the Oracle server improves performance and operational control.

Action queries are converted to PL*SQL procedures.

MakeTable and Append queries may be optionally implemented using Oracle GLOBAL TEMPORARY TABLES. The Oracle global temporary tables are created from Access tables that are generated by running the MakeTable query. The Oracle global temporary tables are local to each user session. This means that each concurrent user has their own copy of the global table, thereby preventing users from interfering with each others work.

An Oracle stored procedure is generated that implements the logic of Access Append and MakeTable queries and populates the global table with data from other tables.

Bulk Updates

After converting MakeTable and Append queries to Oracle permanent table, it may be desirable to create a script with calls to the converted procedures which can be scheduled to run overnight. It may also be appropriate to include Delete and Update queries in the script depending on your application.

The Client-Server page of the Create Oracle Application form contains an option to replace the original Access Action queries with pass-through queries that call the converted Oracle procedure.  There is also an option that instructs Scriptoria to remove MakeTable and Append queries from the converted application.

Scriptoria Assigned Names when Generating Applications

When Scriptoria links Oracle tables and creates pass-through queries by appending a suffix to the end of Access names as follows: