Progress
DataServer
for ORACLE Guide


ORACLE Views

ORACLE schema objects include views. A view is a presentation of data in one or more tables. The DataServer for ORACLE fully supports views that are subsets of a single table. Table 2–2 lists the level of support available for various types of views.

Table 2–2: Supported ORACLE Views
View
Supported 4GL
All columns from a single table
SELECT cust-num, name, zip ...
  FROM customer 
Full support for this view. Use the USE-INDEX option with FIND NEXT statements to get the same results you would expect from Progress.
Some columns from a single table
SELECT cust-num, name
  FROM customer 
Full support for this view. Use the USE-INDEX option with FIND NEXT statements to get the same results you would expect from Progress.
All columns from a single table with an expression
SELECT cust-num, name, zip ...
  WHERE zip < 20000
  FROM customer 
Full support for this view, except that you cannot update the column with the expression (zip, in this example).
All columns from a single table with an aggregate or function
SELECT cust-num, name, zip ... 
  WHERE zip < 20000
  FROM customer
  GROUP BY zip 
The only supported statements are:
FOR EACH ... NO-LOCK|SHARE-LOCK:
FIND ... NO-LOCK|SHARE-LOCK: 
Columns from multiple tables
SELECT cust-num, name, zip, order-num
  FROM customer, order
  WHERE sales-rep = ’HXM’ 
The only supported statements are:
FOR EACH ... NO-LOCK|SHARE-LOCK:
FIND ... NO-LOCK|SHARE-LOCK: 
With aggregates, functions, or joins that contain a LONG column
You cannot access the LONG column if it contains more than 255 bytes of data. The column is truncated, the query stops, and you receive an error message.

ORACLE views appear as tables in the Data Dictionary’s table list for the schema image, not as views. The Data Dictionary’s SQL View Report does not list ORACLE or other non-Progress views. Nor can you access them through the PRO/SQL menu functions.

In addition, Progress does not allow you to undo the deletion of a record with a view name inside a subtransaction block, so you must perform the deletion inside a transaction block. If you delete a view in a subtransaction block and then try to undo the deletion later, Progress returns a run-time error. See the Progress Programming Handbook for information on subtransactions.

Multi-table Views

The DataServer supports direct access to multi-table views. Use the following 4GL syntax to read rows from multi-table views:

FOR EACH view-name { NO-LOCK | SHARE-LOCK } : 

You cannot use other Progress queries, such as the DEFINE QUERY, OPEN QUERY, GET, and DEFINE BROWSE statements, to access multi-table views.

Progress cannot recognize whether a view in an ORACLE database is a multi-table view. Although the DataServer copies multi-table views into the schema image, Progress returns run-time errors if you try to update them with a Progress application.

Views Containing Aggregates

The DataServer supports access to columns in views that contain aggregates or functions only when the affected column has a name associated with it. Assign specific names to the columns when you define an ORACLE view. For example, the following SQL statement names a computed column in a view definition:

CREATE VIEW view-name AS SELECT COUNT(*) cust_count
  FROM CUSTOMER 

Use the following 4GL syntax to read rows from views that contain aggregates or functions:

FOR EACH view-name { NO-LOCK | SHARE-LOCK } : 

You can also access the view by using the RUN STORED-PROC send-sql-statement option to send an SQL statement to select the data from the view. You can access a view by using the send-sql-statement option without adding index definitions for the view in the schema holder. See the "Sending SQL Statements" section for more information.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095