Other Tips

Table of contents:

This chapter covers topics that a report writer might find useful in certain circumstances, including materialized views, reporting off non-Oracle data via external tables, and heterogeneous connectivity. Reporting on a past state of the database using Oracle Flashback is also described, as well as modifying the database using stored procedures or Crystal Reports SQL Commands.

Materialized Views

Creating materialized views from SELECT statements is similar to creating regular views. However, the view contents are “materialized”—that is, written to disk. Because materialized views are instantiated, they can be treated, in many respects, like tables. They can have indexes, for instance, and they appear as tables in the Oracle data dictionary and the Crystal Reports Database Expert. (Regular views also appear as tables in the Database Expert, but they are merely stored queries and hold no data, whereas materialized views are stored as tables with their contents maintained automatically by Oracle.)

Materialized views can be refreshed, or brought into sync with their underlying tables, in several different ways according to their definition: If defined as ON COMMIT, they are refreshed automatically when data in the underlying tables is changed. If defined as ON DEMAND, they are refreshed when a command to refresh them is executed. If defined with a refresh schedule, they will be refreshed automatically according to that schedule. Additionally, materialized views can be refreshed using several different methods. They can be “fast” refreshed, which updates the materialized view with changes that have been made to the underlying tables; they can be “complete” refreshed, which re-creates the entire materialized view; or they can be “force” refreshed, which uses a fast refresh if possible and a complete refresh otherwise.

Overview

Materialized views can be very important to report writers. For example, they can speed access to complex datasets by precomputing them. Say that you often need the total order amount by month for various reports, and you are willing to have today’s reports reflect the month-to-date total for the current month as of yesterday; that is, you do not require the current month’s total to include orders from today. You could create a materialized view that would be refreshed every morning at 1:00 AM. To create the following materialized view, the Xtreme user must have the CREATE MATERIALIZED VIEW system privilege granted directly, not via a role.

CREATE MATERIALIZED VIEW XTREME.ORDERS_MONTH_TOTALS_YESTERDAY
BUILD IMMEDIATE 
REFRESH COMPLETE 
NEXT TRUNC(sysdate+1)+1/24 
AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
 EXTRACT(MONTH FROM order_date) AS order_month,
 SUM(order_amount) AS month_amount
 FROM orders
 GROUP BY EXTRACT(YEAR FROM order_date),
 EXTRACT(MONTH FROM order_date);

The example precomputes an aggregate but could also precompute complex joins.

The CREATE MATERIALIZED VIEW statement has many options. A few will be discussed here; see Oracle documentation for complete coverage.

The BUILD clause can be either IMMEDIATE or DEFERRED. If BUILD IMMEDIATE is chosen, the materialized view will be created and populated immediately, although in some cases, this may not be desirable. If the time or resources involved in creating the view are excessive, the view can be DEFERRED. If BUILD DEFERRED is chosen, the view will be created and populated at its first refresh time.
The REFRESH clause has several subclauses. The REFRESH type can be either FAST, COMPLETE, or FORCE. REFRESH COMPLETE re-creates the entire materialized view from its SELECT statement. REFRESH FAST updates the materialized view with changes that have been made to the underlying tables; it requires that materialized view logs be created for each underlying table. REFRESH FORCE will attempt a fast refresh if possible, otherwise it will do a complete refresh. FORCE is the default value for the refresh type.
The REFRESH interval can be NEVER, START WITH/NEXT, ON DEMAND, or ON COMMIT. If NEVER REFRESH is used, the materialized view can never be refreshed. If either or both START WITH or NEXT are used, the materialized view will be refreshed on a scheduled basis. The START WITH clause requires the date and time of the first refresh. The NEXT clause, as used in the preceding example, requires the date and time of all subsequent refreshes. If ON DEMAND is chosen, the view can be refreshed with one of the DBMS_MVIEW refresh procedures. This option is useful when scheduling refreshes along with other batch processes. The ON COMMIT option will do a fast refresh of the materialized view when any changes are made to the underlying tables and, because of this, materialized view logs must exist. If you choose ON COMMIT, modifications to the underlying tables will take longer due to the additional work involved in also updating the materialized view.

The following example creates a materialized view that will be refreshed on demand with a DBMS_MVIEW command:

CREATE MATERIALIZED VIEW XTREME.ORDERS_MONTH_TOTALS_ONDEMAND
BUILD IMMEDIATE 
REFRESH ON DEMAND 
AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
 EXTRACT(MONTH FROM order_date) AS order_month,
 SUM(order_amount) AS month_amount
 FROM orders
 GROUP BY EXTRACT(YEAR FROM order_date),
 EXTRACT(MONTH FROM order_date);

Once this materialized view is created, it can be refreshed at any time with the following command:

EXECUTE DBMS_MVIEW.REFRESH('XTREME.ORDERS_MONTH_TOTALS_ONDEMAND');

Note that you can specify a list of materialized views to refresh instead of just one, and you can use either of two other refresh procedures, REFRESH_ALL_MVIEWS and REFRESH_DEPENDENT. See Oracle documentation for a full description of these procedures and their syntax.

To enable a fast refresh, which is required for the ON COMMIT refresh interval and can be used with the other refresh intervals, you must first create materialized view logs for the underlying tables. These logs keep track of changes to the tables so that they can be applied to the materialized view.

For Oracle 9i, first create the log:

CREATE MATERIALIZED VIEW LOG
 ON XTREME.ORDERS 
TABLESPACE USERS 
WITH ROWID, PRIMARY KEY ( ORDER_DATE, ORDER_AMOUNT), SEQUENCE 
INCLUDING NEW VALUES;

You can then create the materialized view:

CREATE MATERIALIZED VIEW XTREME.ORDERS_MONTH_TOTALS_ONCOMMIT 
BUILD IMMEDIATE 
REFRESH FAST 
ON COMMIT 
AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
 EXTRACT(MONTH FROM order_date) AS order_month,
 SUM(order_amount) AS month_amount
 FROM orders
 GROUP BY EXTRACT(YEAR FROM order_date),
 EXTRACT(MONTH FROM order_date);

For Oracle 8i, create the log as follows:

CREATE MATERIALIZED VIEW LOG
 ON XTREME.ORDERS 
TABLESPACE USERS 
WITH ROWID, PRIMARY KEY ( ORDER_DATE, ORDER_AMOUNT) 
INCLUDING NEW VALUES;

Then create the materialized view as shown:

CREATE MATERIALIZED VIEW XTREME.ORDERS_MONTH_TOTALS_ONCOMMIT 
BUILD IMMEDIATE 
REFRESH FAST 
ON COMMIT 
AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
 EXTRACT(MONTH FROM order_date) AS order_month,
 SUM(order_amount) AS month_amount,
 COUNT(*) AS Row_Count,
 COUNT(order_amount) AS Amount_Count
 FROM orders
 GROUP BY EXTRACT(YEAR FROM order_date),
 EXTRACT(MONTH FROM order_date);

Note that each table can have only one materialized view log created for it. If multiple materialized views use the same table for fast refresh, the materialized view log must be defined to contain all the data necessary to enable fast refresh for all of the materialized views that use it.

You have now created three different materialized views. One will be updated on a scheduled basis, one on demand, and one whenever the underlying data changes. Next, we will compare the report processing times for a report using a materialized view versus the base tables. There is a report called Chapter 9Amount by Order Month Direct.rpt in the download files that displays the total order amount by order month. As shown in the following illustration, its database query uses the base table Orders:

click to expand

Its performance information is displayed in Figure 9-1.

click to expand
Figure 9-1: Performance Information for Amount by Order Month Direct

There is a second report called Chapter 9Amount by Order Month MV.rpt. As shown in the following illustration, its database query uses a materialized view:

click to expand

Its performance information is displayed in Figure 9-2.

click to expand
Figure 9-2: Performance Information for Amount by Order Month MV

You can see that the difference in the query runtime is small but still noticeable, improving from 9 milliseconds to 6 milliseconds. You may see different results depending on your environment. This particular materialized view is relatively simple and returns few rows; improvements will be greater when using more complex materialized views.

Query Rewrite

Materialized views can also be used in a more transparent manner. Oracle will recognize when a submitted query could be satisfied with an existing materialized view and will transparently rewrite the query to use that materialized view. In this case, the report developer or user need not even know about the existence of materialized views to reap their benefits.

In order to use query rewrite, you must set the initialization parameter QUERY_REWRITE_ENABLED to TRUE using the following syntax:

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;

Note that for some queries to be rewritten, the initialization parameter QUERY_REWRITE_INTEGRITY may need to be changed from its default value of ENFORCED to either TRUSTED or STALE_TOLERATED.

The owner of the underlying tables must have the QUERY REWRITE system privilege. To create query rewrite enabled materialized views on tables that do not belong to you, you must have the GLOBAL QUERY REWRITE privilege or be granted the QUERY REWRITE object privilege on each underlying table referenced in the view. To grant the QUERY REWRITE privilege, the syntax is as follows:

GRANT QUERY REWRITE TO xtreme;

You must also create a materialized view with the ENABLE QUERY REWRITE option, and the view must meet the requirements for query rewrite. Here is the syntax to create a materialized view with QUERY REWRITE enabled in 9i:

CREATE MATERIALIZED VIEW XTREME.ORDERS_MONTH_TOTALS_REWRITE
BUILD IMMEDIATE 
REFRESH FAST 
ON DEMAND 
ENABLE QUERY REWRITE 
AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
 EXTRACT(MONTH FROM order_date) AS order_month,
 SUM(order_amount) AS month_amount
 FROM xtreme.orders
GROUP BY EXTRACT(YEAR FROM order_date),
 EXTRACT(MONTH FROM order_date);

Here is the equivalent for 8i:

CREATE MATERIALIZED VIEW XTREME.ORDERS_MONTH_TOTALS_REWRITE 
BUILD IMMEDIATE 
REFRESH FAST 
ON DEMAND 
ENABLE QUERY REWRITE 
AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
 EXTRACT(MONTH FROM order_date) AS order_month,
 SUM(order_amount) AS month_amount,
 COUNT(*) AS Row_Count,
 COUNT(order_amount) AS Amount_Count
 FROM xtreme.orders
 GROUP BY EXTRACT(YEAR FROM order_date),
 EXTRACT(MONTH FROM order_date);

Oracle recommends that you qualify the object names with their schema when creating materialized views.

You must also gather statistics for the materialized view for it to be considered for use in query rewrite:

BEGIN
 DBMS_STATS.GATHER_TABLE_STATS(
 ownname => 'XTREME', 
 tabname => 'ORDERS_MONTH_TOTALS_REWRITE'); 
END;

The queries generated by Crystal Reports will not necessarily be rewritten even if it appears that they should be. The difficulty appears to be related to either the aliases or the double quotes that Crystal always includes; you will obtain more consistent results using SQL Commands. Open the Chapter 9Amount by Order Month Rewrite.rpt report.

Note

This report will not work in Oracle 8i because it uses the execution plan subreport.

This report uses the SQL Command shown here:

SELECT (EXTRACT(YEAR FROM ORDER_DATE)) ORDER_YEAR,
 (EXTRACT(MONTH FROM ORDER_DATE)) ORDER_MONTH, 
 SUM(ORDER_AMOUNT) ORDER_AMOUNT, 
 USERENV('SESSIONID') AUDSID
FROM XTREME.ORDERS
GROUP BY EXTRACT(YEAR FROM ORDER_DATE),
 EXTRACT(MONTH FROM ORDER_DATE) 
ORDER BY EXTRACT(YEAR FROM ORDER_DATE),
 EXTRACT(MONTH FROM ORDER_DATE)

Note that the query uses the Orders table directly, not a materialized view. The AUDSID is included so that the SQL Plan subreport can function. Run the report. The results should be similar to Figure 9-3.

click to expand
Figure 9-3: Amount by Order Month Rewrite

The SQL Plan shows that the query has been rewritten by Oracle to use the Orders_Month_Totals_Rewrite materialized view.

Implementing materialized views that can successfully support query rewrites can be a difficult and complex process. Because the queries that Crystal generates cannot be rewritten, a report writer can use only SQL Commands whose text has been pretested for its rewrite capabilities. These restrictions make what should be a transparent process into a clumsy routine.

Freshness Subreport

For those reports that are intentionally written against materialized views, you can create a subreport that will display the last refresh date and time for the materialized view. Open and run Chapter 9Amount by Order Month MV with Freshness Subreport.rpt. You will see a result similar to that shown in Figure 9-4, with the materialized view refresh time displayed.

click to expand
Figure 9-4: Amount by Order Month MV with Freshness Subreport

Note that this subreport must be modified for each materialized view with which it is used. The selection formula must be changed to indicate the correct materialized view, and there is no way to link the subreport to the main report on the materialized view name.

External Tables

Oracle external tables allow you to query a flat file as if it were a database table. This functionality is usually associated with loading data into data warehouses and eliminating the need for an internal staging table, but it has applications for reporting as well. For example, if you need to include in your report a small amount of data in a comma-delimited file and you have a reason for not loading this data into the database, such as it being unrelated to most other data and not needed for any other purpose, or the database developers are not available to code the load routines.

Note

External tables are a new feature of Oracle 9i.

Configuration

The example will use a file of employee IDs and hypothetical salary increases, assuming that a manager wants to analyze the new salaries before committing himself and having the changes made in the database.

Create a directory on the Oracle server to hold the files. Copy Chapter 9Raises.txt to that directory (Raises.txt is a comma-delimited file containing each employee ID and a proposed percentage increase in salary). Execute the following command, substituting your directory name where appropriate, to create an Oracle directory object. To create a directory object, the user must be a DBA or have the CREATE ANY DIRECTORY privilege.

CREATE DIRECTORY EX_TABS AS 'E:External_Tables';

Grant Xtreme the privilege to read from and write to the directory. Write is required to allow the writing of error logs:

GRANT READ, WRITE ON DIRECTORY EX_TABS TO xtreme;

Create the external table with the following code:

CREATE TABLE RAISES (emp_id NUMBER, sal_inc NUMBER)
 ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY EX_TABS
 LOCATION ('Raises.txt'));

Note that the CREATE TABLE statement for the Raises table is very simple because the default line terminator (NEWLINE) and the default field delimiter (comma) are used in the file. However, many types of files can be described and used, including those with fixed width fields, odd terminators, and binary data. See Oracle documentation for full coverage on creating external tables from files.

Sample Report

Create a new report. You will see that the table Raises appears in the Database Expert along with the other Oracle tables. You can now use it in your reports just as if it were a normal database table.

Open the Chapter 9Proposed Raises.rpt report. Choose Database | Database Expert and look at the Links tab. As shown in Figure 9-5, the Raises table has been linked to the Employee table using the Emp_ID field.

click to expand
Figure 9-5: Raises linking

Run the report. You will see the report displayed in Figure 9-6, showing that the external table data has been used.

click to expand
Figure 9-6: Proposed Raises report

Heterogeneous Services Generic Connectivity

Heterogeneous services is a mechanism built into Oracle that allows transparent access to data on other database platforms. Heterogeneous services can be implemented in two ways. The first method uses purchased gateways; the second uses generic connectivity. Gateways are purchasable options with highly optimized interfaces to other database platforms such as SQL Server or DB2. Generic connectivity uses ODBC or OLE DB, which is potentially slower but definitely cheaper.

Note

For connectivity to other Oracle databases, DB Links can be used. Objects available via DB Links are not recognized by Crystal but can be accessed via SQL Commands or stored procedures.

After setting up heterogeneous services for the database and generic connectivity to particular data sources, you will be able to write queries against those data sources as if they were normal Oracle tables, including joining them to actual Oracle tables. This can be very useful in a reporting environment where you may be required to bring together data from different sources. You can, of course, use Crystal Reports itself for this type of operation, but using Oracle heterogeneous services is usually more efficient.

Configuration

You may use either ODBC or OLE DB to configure heterogeneous services using generic connectivity. The instructions that follow are for ODBC.

Note

The examples shown are for Windows only. See Oracle documentation for other platforms.

Several preliminary steps are required. Verify, or have the DBA verify, that Generic Connectivity using ODBC has been installed with the Oracle Universal Installer. If it has not been installed, then install it. Verify that the heterogeneous services tables exist by attempting to query SYS.HS_FDS_CLASS. If they do not exist, create them by running the caths.sql script found in the Oracle_HomeRDBMSADMIN directory logged in as the SYS user. You must also verify that your database server can act as a client to the external data source. If, for instance, you want to connect to an Informix database, you must install the Informix client on the database server and configure it properly, then verify that the appropriate ODBC driver has been installed on the database server and that a system DSN for your data source exists.

For this example, you will use the Crystal XTREME sample ACCESS database, so you will need to copy xtreme.mdb to a directory on your server. The database is installed by Crystal Reports at C:Program FilesCrystal DecisionsCrystal Reports 9SamplesEnDatabases.

Create a system ODBC DSN called XTREME9DSN, as shown in Figure 9-7, on your server. Set the database location to wherever you copied the file in the previous example. Note that the database can reside anywhere that is network accessible to the server, but the DSN must be created on the server and must be able to connect to the database, wherever it is.

click to expand
Figure 9-7: XTREME9DSN Data Source Name configuration

Heterogeneous services use a special initialization file to map the Oracle service name representing the data source to the ODBC DSN. Under the Oracle home directory in HSAdmin, there will be sample initialization files. Make a copy of the inithsodbc.ora file and call it initXTREME9ODBC.ora. The contents of the file should be as shown here:

HS_FDS_CONNECT_INFO = XTREME9DSN
HS_FDS_TRACE_LEVEL = 0

Note that other options are available and that tracing can be turned on.

Next, you must modify the tnsnames.ora file on your server to include a service name that can use heterogeneous services. In the following example, the (HS = OK) indicates that this service can use heterogeneous services:

XTREME9ODBC =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521)) 
 ) 
 (CONNECT_DATA =
 (SID = XTREME9ODBC) 
 ) 
 (HS = OK) 
 )

Substitute your appropriate host and port and use the name of the heterogeneous services initialization file less the “init” for the SID.

Next, you must modify the listener.ora file on the server to include a new service in the SID_LIST. The program name of hsodbc instructs Oracle to use the programs that implement heterogeneous services for ODBC. The SID_NAME must match what was used in the tnsnames file.

 (SID_DESC =
 (PROGRAM = hsodbc)
 (SID_NAME = XTREME9ODBC)
 (ORACLE_HOME = E:oracleora92)
 )

Restart the listener.

Now you can create a database link using the new service. Logged in as Xtreme, execute the following statement:

CREATE DATABASE LINK xtreme_link USING 'XTREME9ODBC';

If your database requires a user ID and password, the proper authentication type must be set up in the database link. To test the link, execute the following statement:

Select "Employee ID" from employee@xtreme_link;

Note that because the field names in the Access database contain mixed case and spaces, they must be surrounded by double quotes in the SELECT statement. Note also that attempting to retrieve LOB types in SQL*Plus fails.

Report Example

Suppose that you are in a data warehouse environment and you have loaded your Oracle Xtreme schema from the Xtreme Access database. Now you need to verify that the employee salaries were loaded properly. Heterogeneous connectivity makes this easy.

Crystal Reports does not list or otherwise recognize tables available via Oracle database links. To use this type of data, you must create your own query. Therefore, you must write a SQL Command to display the salary data from the Access database. In addition, linking the results of a SQL Command to a regular table in Crystal fails, so you will have to gather all of the fields required for the report in the SQL Command.

Open Chapter 9Salary Data Integrity Check.rpt. Note that the SQL Command is as follows:

SELECT l."Employee ID", l."Salary" access_salary,
 e.salary oracle_salary
FROM employee@xtreme_link l, employee e
WHERE e.employee_id=l."Employee ID"

Run the report. You will see a result like the one shown in Figure 9-8 but, depending on any changes made to the Oracle data, it may or may not match the Access data.

click to expand
Figure 9-8: Salary Data Integrity Check

A similar result could be achieved by using two data sources in the Crystal report: one for the Access database and one for the Oracle database. However, there are several benefits to using Oracle generic connectivity over Crystal’s multiple database reporting capabilities: it centralizes database access, only the server needs the software to be able to connect to the “foreign” databases, and report developers need only Oracle clients and drivers installed.

Using Synonyms

Synonyms can be created for the tables available via a database link. In this case, you can create a synonym such as the following:

CREATE SYNONYM XTREME.ACCESS_EMPLOYEE FOR employee@xtreme_link;

Then you can query the table as follows:

SELECT "Employee ID" FROM access_employee;

Creating a synonym also means that the table is now listed in the Crystal Database Expert, at least for the native and Oracle ODBC drivers, and it can be selected from there, as shown in Figure 9-9.

click to expand
Figure 9-9: Synonyms for database links in the Database Expert

Unfortunately, Crystal converts the characters in the field names to all uppercase and replaces the spaces with underscores when using the native driver. As a result, if you choose the synonym from the Database Expert and then add any of its fields to the report, the generated query will fail because the field names are not correct. The Oracle ODBC driver also fails, even though it appears to treat the field names correctly.

Oracle Flashback Query

Oracle Flashback is a database feature that lets you see and report on a version of the database as of a point in the past. There are many potential uses for this feature in a reporting environment. For example, it allows you to write a report that reflects yesterday’s statistics even if there has been a change since yesterday. It is also particularly useful for troubleshooting and checking data integrity. For example, say a user complains that they ran the same report on two different days with the same parameters and got different results (in a situation where this would not be the expected behavior) and were at a loss as to how to diagnose the problem. Oracle Flashback lets you run that report as of the previous date without relying on timestamps or other mechanisms built into the database application.

Note

Flashback Query is a new feature in Oracle 9i.

Configuration

The Flashback mechanism relies on Oracle UNDO data, which is the information that Oracle optionally stores to roll back the state of the database. Your DBA will need to set up the database for storing some amount of UNDO data. To set up automatic UNDO management, the following steps must be accomplished:

Set the UNDO_RETENTION initialization parameter, which is specified in seconds. The value should be determined considering the tradeoff of space requirements versus depth of UNDO data. To set UNDO_RETENTION to seven days, execute the following statement:
```
ALTER SYSTEM SET UNDO_RETENTION = 604800;
```
The initialization parameter UNDO_MANAGEMENT must be set to AUTO. Note that this parameter cannot be set dynamically, so if it needs to be changed, the database will have to be restarted.
An UNDO tablespace with enough space to contain the UNDO data must exist. If no UNDO tablespace exists, create one with the CREATE UNDO TABLESPACE command.

The user who will execute the Flashback query needs the FLASHBACK privilege. This can be granted on individual tables as shown next. You do not need to be granted the FLASHBACK privilege for tables in your own schema.

GRANT FLASHBACK ON xtreme.employee TO ;

Flashback can be granted on a system level for any table in the database:

GRANT FLASHBACK ANY TABLE TO xtreme;

You must also have the EXECUTE privilege on the DBMS_FLASHBACK package if you need to use those procedures:

GRANT EXECUTE ON DBMS_FLASHBACK TO xtreme;

If you desire to enable Flashback capabilities on LOB columns, additional steps are required. Refer to Oracle documentation for further detail.

SELECT AS OF a Point in the Past

The simplest way to take advantage of Flashback queries is to use the AS OF clause in a SELECT statement. First, make sure that some changed data is available. Run the following UPDATE statement and COMMIT it:

UPDATE employee SET Salary = Salary * (1.05);

Execute the following to show the new salaries:

SQL> SELECT employee_id, salary FROM employee; 


EMPLOYEE_ID SALARY
----------- ----------
 1 42000
 2 94500
 3 34650
 4 36750
 5 52500

Then execute the following to show the old salary values:

SQL> SELECT employee_id, salary FROM employee AS OF
 2 TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE); 


EMPLOYEE_ID SALARY
----------- ----------
 1 40000
 2 90000
 3 33000
 4 35000
 5 50000

This query looks at the employee table as it was 60 minutes ago. The AS OF TIMESTAMP clause can be computed as just shown, or a literal or parameter can be used.

Oracle stamps each database change with a System Change Number (SCN). The Flashback query operation always looks at the database as of a certain SCN. To enable the use of the TIMESTAMP option, Oracle keeps track of a TIMESTAMP-to-SCN mapping which it updates every five minutes. Because of this, if you use the TIMESTAMP option, you may be up to five minutes off from the expected database state. In most cases, this is OK. If you are comparing to yesterday, five minutes does not usually matter.

For true precision, you can use the AS OF SCN clause of the SELECT statement. However, to do that, you must know the SCN that you wish to return to. If your reports are embedded in an application, the application can save the SCN as it commits transactions and allow them to be passed to Crystal for reporting. You can then embed reports in applications that show the user the state of the database before the user’s most recent changes.

Note that the TIMESTAMP-to-SCN mapping is only maintained for five days worth of data. If the server is down for some length of time, that time is not included in the mappings and would mean that the mapping exists for further in the past than five days. If you need to go further back than five (uptime) days, you must use SCN numbers instead of TIMESTAMPS (and enough UNDO data must exist).

Reporting Using SELECT AS OF

You can use the AS OF clause for reporting based on SQL Commands, stored procedures, or views. The following example is a report based on a view:

CREATE OR REPLACE VIEW TODAYS_SALARY_CHANGES
AS
SELECT employee_id, Salary_Now, Salary_Yesterday,
 NVL(Salary_Now,0)-NVL(Salary_Yesterday,0) Salary_Change
 FROM
(SELECT employee_id, salary Salary_Now FROM employee) Now
FULL OUTER JOIN
(SELECT employee_id, salary Salary_Yesterday FROM employee AS OF
 TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)) Yesterday
 USING (employee_id)
WHERE NVL(Salary_Now,0)<>NVL(Salary_Yesterday,0);

To see the effect of the FULL OUTER JOIN, add an employee or delete an employee. Open Chapter 9Today’s Salary Changes.rpt and run it. You will see a result similar to Figure 9-10.

click to expand
Figure 9-10: Today’s Salary Changes

To allow a user to select a particular point in the past, create a parameterized stored procedure. The stored procedure is Salary_Change_Report in the Flashback_Reports package and its core logic is the same as the preceding view with the addition of a parameter for the hours in the past. Open Chapter 9Salary Change Since X Hours Ago.rpt and run it. You will see a result similar to Figure 9-11.

click to expand
Figure 9-11: Salary Change Since X Hours Ago

The SELECT AS OF can also be used with a CREATE TABLE AS SELECT statement to populate temporary tables with past state data for comparison purposes.

DBMS_FLASHBACK

The Oracle-supplied PL/SQL package DBMS_FLASHBACK contains procedures that can be used in PL/SQL programs to work with past state data. If you are writing a stored procedure that requires multiple Flashback queries or that cannot modify the SQL Commands to add the AS OF clause, you can use the DBMS_FLASHBACK subprograms.

The ENABLE_AT_TIME and ENABLE_AT_SCN subprograms put the user’s session in Flashback mode for the time or SCN listed. Any queries run after this command are executed and, before DBMS_FLASHBACK.DISABLE is executed, will return data for the time in the past. Cursors can capture the past data; after Flashback is disabled, those cursors will still refer to past data.

The stored procedure Flashback_Reports.Salary_Changes_Report uses the DBMS_FLASHBACK procedures to capture two past salary changes and store them in a temporary table. The temporary table can be created using the following statement:

CREATE GLOBAL TEMPORARY TABLE SALARY_CHANGES_TEMP
 (EMPLOYEE_ID NUMBER(10), 
 SALARY_NOW NUMBER(19, 4),
 SALARY_BEFORE1 NUMBER(19, 4),
 SALARY_BEFORE2 NUMBER(19, 4))
ON COMMIT PRESERVE ROWS;

The package body for the procedure is shown here. One cursor is created and then run for the current state of the database and two past states, with the results stored in the temporary table:

PROCEDURE Salary_Changes_Report
 (Salary_Changes_Cur OUT Salary_Changes_Rpt_Type, 
 Hours_Past1 IN NUMBER, 
 Hours_Past2 IN NUMBER) 
IS
 TYPE C_Rec_Type IS RECORD
 (Employee_ID Employee.Employee_ID%Type, 
 Salary Employee.Salary%Type); 
 CURSOR C IS SELECT Employee_ID, Salary FROM employee; 
 C_Rec C_Rec_Type; 
BEGIN
 DELETE FROM Salary_Changes_Temp; 
--Now
 OPEN C; 
 LOOP
 FETCH C INTO C_Rec; 
 EXIT WHEN C%NOTFOUND; 
 INSERT INTO Salary_Changes_Temp(Employee_ID, Salary_Now) 
 Values(C_Rec.Employee_id, C_Rec.Salary); 
 END LOOP; 
 CLOSE C; 
 COMMIT; 
--t1 
 DBMS_FLASHBACK.ENABLE_AT_TIME
 (SYSTIMESTAMP - NUMTODSINTERVAL(Hours_Past1,'HOUR')); 
 OPEN C; 
 DBMS_FLASHBACK.DISABLE; 
 LOOP
 FETCH C INTO C_Rec; 
 EXIT WHEN C%NOTFOUND; 
 Update Salary_Changes_Temp
 Set Salary_Before1 = C_Rec.Salary
 Where Employee_id = C_Rec.Employee_Id; 
 END LOOP; 
 CLOSE C; 
 COMMIT; 
--t2 
 DBMS_FLASHBACK.ENABLE_AT_TIME
 (SYSTIMESTAMP - NUMTODSINTERVAL(Hours_Past2,'HOUR')); 
 OPEN C; 
 DBMS_FLASHBACK.DISABLE; 
 LOOP
 FETCH C INTO C_Rec; 
 EXIT WHEN C%NOTFOUND; 
 Update Salary_Changes_Temp
 Set Salary_Before2 = C_Rec.Salary
 Where Employee_id = C_Rec.Employee_Id; 
 END LOOP; 
 CLOSE C; 
 COMMIT; 
--Report Cursor 
 OPEN Salary_Changes_Cur FOR
 SELECT Employee_ID, Salary_Now, Salary_Before1, 
 NVL(Salary_Now,0)-NVL(Salary_Before1,0) Salary_Change1, 
 Salary_Before2, 
 NVL(Salary_Before1,0)-NVL(Salary_Before2,0) Salary_Change2, 
 USERENV('SESSIONID') AUDSID
 FROM Salary_Changes_Temp; 
END Salary_Changes_Report;

To visualize the results, you will need to modify the Employee salaries at least twice, at least an hour apart. The salaries have been modified once in the previous section. A script that will make a second modification is shown here:

BEGIN
 FOR r IN (SELECT emp_id, sal_inc FROM raises) LOOP
 UPDATE employee
 SET Salary = (1+(r.sal_inc-2)/100)*Salary
 WHERE employee_id = r.emp_id;
 END LOOP;
END;

Open the Chapter 9Salary Changes.rpt report. Run it using parameter choices that will give you two distinct past salaries for the employees. The result will be similar to Figure 9-12.

click to expand
Figure 9-12: Salary Changes

Oracle Flashback is a wonderful method for querying the past state of the database, as long as you do not need to go very far back and you are not retrieving too much data. Individual users can flash back to different times, and each subquery of a SELECT statement can use a different AS OF date and time. However, you should always be aware of how much of the server resources are being used to implement it for you when using this feature.

Updating Data via Crystal Reports

Updating data via Crystal Reports is possible in two ways, though Crystal Reports is not intended as a database modification tool, merely a reporting tool. You can, of course, make database changes using stored procedures. Therefore, if you are using a stored procedure to back a report, you can make any database modifications you desire. The second possibility is to use an anonymous PL/SQL block in a SQL Command. I do not recommend using Crystal Reports for any database modifications with one exception, and that is automating report execution logging.

Via Stored Procedures

To do report execution logging from a stored procedure, you simply need to add a statement that writes the logging information somewhere in the procedure. You can create a separate procedure for this function and call it from all of your report procedures.

Via SQL Commands

To do report execution logging from a SQL Command, you can write the SQL Command similar to the following:

BEGIN
 Reports.Supplier_Report({?Supplier});
 Insert into Report_Log
 Values('Reports.Supplier_Report',USER,SYSDATE,
 'Supplier={?Supplier}');
END;

Note that there are many restrictions involved in this method. The statement that returns the report data must be a call to a REF Cursor stored procedure; it cannot be a SELECT statement. SELECT statements without INTO clauses are not allowed in PL/SQL blocks. Experimentation has shown any other statements in the block must come after the call to the stored procedure; otherwise, an error results. Finally, such calls cannot be made from the native driver.

This chapter has covered several Oracle features that might be of interest to Crystal Report writers, including materialized views, external tables, heterogeneous services, and Flashback queries. The next chapter will develop a generic Oracle database dictionary report.