Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## Defining a view to use as a buffer

> As an alternative to using the buffer `proc–text–buffer` defined by Progress, you can define a view in the data source that can serve as a buffer allowing you to retrieve database results in their original data types. However, keep in mind that using views creates a database dependency beyond the stored procedure itself.

> ##### Technique to define a view to use as a buffer

> While a stored procedure can include multiple SQL statements, a buffer that you define contains the format of only a single result set. You need to define multiple views to accommodate multiple result sets.

> | To define a buffer:

> 1. Define a view in the ORACLE data source with the following characteristics:

> - The naming convention `BUFFER_buffername`.
> - The same number of columns and data types that the stored procedure returns in the result set.
> - The columns in the order that the stored procedure returns them.

> For example, to return two columns with two types of values, an integer and a character string, use an SQL utility to define the following view in the data source:

>     CREATE VIEW BUFFER_custlist AS SELECT customer.cust_num,
>     customer.name FROM customer WHERE 1 = 0

> Notice that these views are defined to ensure that they never return any results. This helps to indicate that the purpose of the view is its buffer content and not its SQL capabilities. It is not necessary to define views that you will use as buffers this way, but it does allow you to distinguish quickly between views and buffers.

> 2. Update your schema image using the Update/Add Table Definitions DataServer utility. The utility adds the view to the list of accessible objects in the schema holder. The DataServer defines the view as a buffer that Progress can use. See the "Updating a schema image" section for instructions on using this utility.

> ##### Assessing result sets obtained by defining a view as buffer technique

> The buffer in the previous procedure defines two returned values for a stored procedure—an `INTEGER` and a `CHARACTER` value—in that order. If the data types do not match those returned by the stored procedure, the procedure returns more than two values, or returns the values in a different order than you specified, you receive a run-time error.

> The easiest way to create a buffer that accepts data from stored procedures is to use the text of the SQL `SELECT` statement from the stored procedure. This ensures that you define your data types correctly and in the correct order.

> ##### Examples based on views created in the data source

> The examples in this section do not use the supplied `proc-text-buffer` buffer. Instead, they show how to define formatted buffers by creating views in the data source, using the following syntax:

> Syntax
>     CREATE VIEW BUFFER_buffer-name

> The following two examples show the views created in your ORACLE data source that you can use as buffers to store the results from the stored procedure `pcust`:

>     CREATE VIEW BUFFER_pcust_orders AS SELECT customer.cust_num,
>        customer.name, order_num FROM customer, order_ WHERE 1 = 0

>     CREATE VIEW BUFFER_pcust_states AS SELECT cust_num, state.state
>     FROM customer, state WHERE 1 = 0

> Example 3–7 runs the `send-sql-statement` option twice; procedure handles (through the `PROC-HANDLE` function) identify the different results from the ORACLE database.

> Note: The following example is not intended to illustrate the use of the previous syntax.

>     /* Procedure handles */

>     DEFINE VAR handle1 AS integer.
>     DEFINE VAR handle2 AS integer.
>     DEFINE VAR x AS character.
>     RUN STORED-PROC send-sql-statement handle1 =
>      PROC-HANDLE ("SELECT cust_num, state FROM customer").

>     FOR EACH cust-buf WHERE PROC-HANDLE = handle1:
>         x = "SELECT state, state-name, region FROM state
>           WHERE state = ’" + cust-buf.state + "’".

>       RUN STORED-PROC send-sql-statement handle2 = PROC-HANDLE (x).
>       FOR EACH state-buf WHERE PROC-HANDLE = handle2:
>         DISPLAY state-buf.
>       END.
>       CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle2.
>     END.
>     CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

> Example 3–7: Running the send-sql-statement option twice

> If you use more than one `send-sql-statement` at a time to send `SELECT` statements, you must explicitly define procedure handles for each.

> Example 3–8 is a two-part example. The first code shows a stored procedure. The second code shows the Progress 4GL procedure that is associated with the stored procedure.

>     /* First part of example - Stored Procedure Code*/

>     create or replace package cv_types as
>       type GenericCurType is ref cursor;
>     End cv_types;
>     /
>     CREATE or replace PROCEDURE pcustorder (num IN INT, c1 out cv_types.
>       GenericCurType, c2 out cv_types.GenericCurType)
>     AS BEGIN
>     IF num IS NULL THEN
>     raise_application_error (-20101, ‘Cust Num is missing’);
>     ELSE
>     open c1 for
>     SELECT cust_num,name FROM customer WHERE cust_num > num;
>     open c2 for
>     SELECT order_num,odate FROM order_WHERE order_.Cust_num > num;
>     END IF;
>     END;
>     /

> The following 4GL procedure code assumes that the `pcust_buffer` and `porder_buffer` exist in the schema and that they match the result-sets schema.

>     /* Second part of example - 4GL procedure code*/

>     DEFINE VARIABLE h1 AS HANDLE.
>     RUN STORED-PROC pcustorder (INPUT 10, OUTPUT 10, OUTPUT 10, OUTPUT 10, OUTPUT
>     10).
>     FOR EACH pcust_buffer WHERE PROC-HANDLE = h1 AND CUSTOR = pcustorder.c1:
>     DISPLAY pcust_buffer.
>     END.
>     FOR EACH porder_buffer WHERE PROC-HANDLE = h1 AND CUSTOR = pcustorder.c2:
>     DISPLAY porder_buffer.
>     END.
>     CLOSE STORED-PROC pcustorder.

> Example 3–8: Illustrating how a stored procedure returns multiple result sets and how to access the result sets using cursor arguments and buffers

> As Example 3–8 shows, the stored procedure `pcustorder` returns multiple result-sets. The second code box shows the procedure used to access the result sets; it uses cursor arguments and buffers.

> Because two different buffers have been defined, the returned values maintain their data types instead of being converted to character strings and stored in the Progress-defined buffer `proc–text–buffer`. You can then use the returned values in calculations without first converting them back to their original data types. In addition, the two separate buffers make your output look cleaner, allowing the 4GL to build a new default frame for the two different types of output. Reading your results into an explicitly defined buffer also allows you to manipulate the data just as you would manipulate data from an OpenEdge database; for example, with Frame phrases and `FORM` statements.

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