Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## ORACLE views

> ORACLE schema objects include views. A view is a presentation of data in one or more tables. 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 the Progress 4GL.
> 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 the Progress 4GL.
> All columns from a single table with an expression
>     SELECT cust-num, UPPER(name), zip ...
>       WHERE zip < 20000
>       FROM customer
> | Full support for this view, except that you cannot update the column with the expression (`name`, 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:

> 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-OpenEdge views. Nor can you access them through the PRO/SQL menu functions.

> In addition, the Progress 4GL 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, the Progress 4GL returns a run-time error. See OpenEdge Development: Progress 4GL 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 4GL queries, such as the `DEFINE` `QUERY`, `OPEN` `QUERY`, `GET`, and `DEFINE` `BROWSE` statements, to access multi-table views.

> The Progress 4GL 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, the Progress 4GL returns run-time errors if you try to update them with an OpenEdge 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 Chapter 2, "Initial Programming Considerations," for more information.

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