Intersession and Interdatabase Communication from Forms

Before Oracle Developer Forms 6, in order to output a current set of records from a block, developers had to resort to one of the following methods :

• Loop through the block, and insert into a destination table in the same schema.
• Loop through the block, and insert into a destination table in a different schema in the same database, with privileges granted.
• Write to an O/S file using the TEXT_IO API.
• In dynamic SQL, use DBMS_SQL to perform the task.
• Pass data to another form that inserts into a destination table in a different session, but the same schema, using a GLOBAL_SCOPE record group , a packaged PL/SQL table of records, or global variables .

The limitations of these methods are as follows :

• There is no way to open a separate database connection from Forms that enables you to communicate with a different schema on the same server or an altogether different database server, that is, connections to multiple databases.
• The schema and database server names have to be hard-coded inside the code.
• DBMS_SQL executes with creator rights, and dynamic SQL is limited to this restriction.
• To communicate between two sessions, you must rely on the DBMS_PIPE package and/or DBMS_AQ package, each of which has its own limitations.

Oracle Forms 6 facilitates intersession (interschema) and interdatabase connection from Forms using the EXEC_SQL package. EXEC_SQL is asynchronous in the sense that it is independent of pending database changes in the form.

You can use EXEC_SQL as follows:

• To populate a database table in a different database from a Forms block based on a database table in a source connection, such as BLOCK_TO_TABLE.
• To eliminate file I/O for purposes such as Forms error logging.
• To simulate a FORMS PIPING mechanism.
• To execute common application logic in multiple schemas in multiple databases from a single form.

Tip

You can simulate a FORMS PIPING mechanism to send and receive data using EXEC_SQL. Think of the error-logging using DBMS_PIPE or sending priority messages using DBMS_AQ. These applications can be implemented using a source form sending data directly to multiple user connections. A destination form can output this data by simply picking up. In fact, there is no need for a pipe or queue because no delay time is involved in transfer of the data till somebody receives it.

A time-out interval can be set to synchronize transfer of the data to the target data base or schema.

Tip

Using OPEN_FORM with the SESSION parameter specified will create a separate session using the current schema and the current database only. Of course, Forms error logging can be done using OPEN_FORM in a different session.

Similarities between DBMS_SQL and EXEC_SQL are as follows:

• Like DBMS_SQL, SQL statements are constructed at runtime. Also, both DML and DDL can be issued using EXEC_SQL.
• EXEC_SQL has the following procedures in similarity to DBMS_SQL :

  EXEC_SQL.OPEN_CURSOR EXEC_SQL.PARSE EXEC_SQL.DESCRIBE_COLUMN EXEC_SQL.BIND_VARIABLE EXEC_SQL.DEFINE_COLUMN EXEC_SQL.EXECUTE EXEC_SQL.EXECUTE_AND_FETCH EXEC_SQL.FETCH_ROWS EXEC_SQL.COLUMN_VALUE EXEC_SQL.VARIABLE_VALUE EXEC_SQL.IS_OPEN EXEC_SQL.CLOSE_CURSOR

Differences between EXEC_SQL and DBMS_SQL are as follows:

• EXEC_SQL directly executes in the connection specified, so creator and invoker rights do not come into the picture. This means that it doesn't matter whether the owner of the database objects or a different user is executing the EXEC_SQL package.
• There is no concept of array processing in EXEC_SQL, so there are no EXEC_SQL.DEFINE_ARRAY and EXEC_SQL.BIND_ARRAY procedures.
• EXEC_SQL uses bind by value , in contrast to the bind by address used by DBMS_SQL.
• EXEC_SQL requires the use of EXEC_SQL.VARIABLE_VALUE to retrieve the value of a bind parameter that is an OUT parameter.
• EXEC_SQL requires the use of EXEC_SQL.COLUMN_VALUE to retrieve values in a result set.
• EXEC_SQL has no need for indicator variables because NULL s are handled just like PL/SQL variables.
• EXEC_SQL does not support PL/SQL tables and record types in PL/SQL and CHAR, RAW, LONG, and ROWID in SQL. Therefore, there are no EXEC_SQL.DEFINE_COLUMN_LONG and EXEC_SQL.COLUMN_VALUE_LONG procedures.
• EXEC_SQL has the following extra procedures and/or functions defined as part of the package:

  EXEC_SQL.OPEN_CONNECTION EXEC_SQL.CURR_CONNECTION EXEC_SQL.DEFAULT_CONNECTION EXEC_SQL.MORE_RESULT_SETS EXEC_SQL.IS_CONNECTED EXEC_SQL.IS_OCA_CONNECTION EXEC_SQL.CLOSE_CONNECTION EXEC_SQL.LAST_ERROR_CODE EXEC_SQL.LAST_ERROR_MESG

• EXEC_SQL has the following exceptions as part of its specification:

  EXEC_SQL.PACKAGE_ERROR EXEC_SQL.INVALID_CONNECTION EXEC_SQL.INVALID_COLUMN_NUMB EXEC_SQL.VALUE_ERROR

Tip

The functions EXEC_SQL.LAST_ERROR_CODE and EXEC_SQL.LAST_ERROR_MESG return the error code and error message text of the last occurred error. This error code is different from EXEC_SQL.LAST_SQL_FUCTION_CODE.

Tip

EXEC_SQL.LAST_ERROR_CODE returns on success.

In this section, I will discuss the first of the preceding examples: outputting a current block of records to a different schema in the same database, dynamically creating the destination table.

To do this, follow these steps:

1. Open source connection (defaults to the primary Oracle Developer connection ”the DEFAULT_CONNECTION ). This is done by creating connection handles using EXE_SQL.OPEN_CONNECTION or EXEC_SQL.DEFAULT_CONNECTION.
2. Open a destination connection (defaults to the current connection). You can also do this by using EXEC_SQL.OPEN_CONNECTION or EXEC_SQL.CURR_CONNECTION.
3. Open a source cursor for the source connection. Create cursor handles for the corresponding connection handles using EXEC_SQL.OPEN_CURSOR. No parameters specified to EXEC_SQL.OPEN_CURSOR will default both the source and destination connections to the current connection.
4. Parse the source cursors with the SQL and/or PL/SQL statements, either static or dynamic. Use EXEC_SQL.PARSE, passing the dynamically constructed SQL or PL/SQL statements.
5. Execute the source cursor. Use EXEC_SQL.EXECUTE or EXEC_SQL.EXECUTE_AND_FETCH.
6. Repeat steps 3, 4, and 5 for the destination cursor.
7. For retrieving result sets, it is necessary to define columns , fetch in a loop, and get column values. Use EXEC_SQL.DEFINE_COLUMN for defining columns, EXEC_SQL.FETCH_ROWS in a loop for fetching, and EXEC_SQL.COLUMN_VALUE for getting column values.
8. For other SQL and/or PL/SQL operations, binding variables and retrieving OUT bind variable values are required before and after step 5. Use EXEC_SQL.BIND_VARIABLE and/or EXEC_SQL.VARIABLE_VALUE.
9. Close the destination and source cursors. The order is important. Use EXEC_SQL.CLOSE_CURSOR.
10. Close the destination and source connections. The order is important. Use EXEC_SQL.CLOSE_CONNECTION.

Your source form contains the following elements:

• A DEPT block based on the DEPT table ”After querying using user-specific criteria, which varies from user to user, the records have to be transferred to region-specific DEPT tables, created dynamically as _DEPT_FROM_BLOCK.
• A control block CTRL_BLK with a push button PB_SQLEXEC ”When clicked, the data is transferred to the destination table.

You use a generic procedure BLOCK_TO_TABLE to do this, and you call this procedure in the WHEN-BUTTON-PRESSED trigger of PB_SQLEXEC :

PROCEDURE BLOCK_TO_TABLE PROCEDURE block_to_table(source_block_name IN VARCHAR2, destination_table_name IN VARCHAR2, destination_connection IN VARCHAR2, retcd OUT NUMBER) IS deptno NUMBER; dname VARCHAR2(20); loc VARCHAR2(20); destination_connid EXEC_SQL.CONNTYPE; destination_cursor EXEC_SQL.CURRTYPE; ret_cd PLS_INTEGER; BEGIN destination_connid := EXEC_SQL.OPEN_CONNECTION(destination_connection); destination_cursor := ExEC_SQL.OPEN_CURSOR(destination_connid); destination_cursor := EXEC_SQL.OPEN_CURSOR(destination_connid); EXEC_SQL.PARSE(destination_connid, destination_cursor, 'CREATE TABLE 'destination_table_name'( DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13))'); ret_cd := EXEC_SQL.EXECUTE(destination_connid, destination_cursor); EXEC_SQL.PARSE(destination_connid, destination_cursor, 'INSERT INTO 'destination_table_name '(deptno,dname,loc) VALUES (:deptno, :dname, :loc)'); GO_BLOCK(source_block_name); FIRST_RECORD; LOOP deptno := :dept.deptno; dname := :dept.dname; loc := :dept.loc; EXEC_SQL.BIND_VARIABLE(destination_connid, destination_cursor, ':deptno', deptno); EXEC_SQL.BIND_VARIABLE(destination_connid, destination_cursor, ':dname', dname); EXEC_SQL.BIND_VARIABLE(destination_connid, destination_cursor, ':loc', loc); ret_cd := EXEC_SQL.EXECUTE(destination_connid, destination_cursor); IF :SYSTEM.LAST_RECORD = 'TRUE'THEN EXIT; ELSE NEXT_RECORD; END IF; END LOOP; EXEC_SQL.PARSE(destination_connid, destination_cursor, 'COMMIT'); ret_cd := EXEC_SQL.EXECUTE(destination_connid, destination_cursor); EXEC_SQL.CLOSE_CURSOR(destination_cursor); EXEC_SQL.CLOSE_CONNECTION(destination_connid); retcd := 0; EXCEPTION WHEN EXEC_SQL.PACKAGE_ERROR THEN MESSAGE('ERROR (' TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(destination_connid)) '): ' EXEC_SQL.LAST_ERROR_MESG(destination_connid)); IF EXEC_SQL.IS_CONNECTED(destination_connid) THEN IF EXEC_SQL.IS_OPEN(destination_connid, destination_cursor) THEN EXEC_SQL.CLOSE_CURSOR(destination_connid, destination_cursor); END IF; EXEC_SQL.CLOSE_CONNECTION(destination_connid); END IF; retcd := EXEC_SQL.LAST_ERROR_CODE(destination_connid); END; WHEN-BUTTON-PRESSED declare retcd number; destination_block_name VARCHAR2(100); region_name VARCHAR2(20) := 'REGION1'; begin destination_block_name := region_name'_DEPT_FROM_BLOCK'; block_to_table('DEPT',destination_block_name, 'user1/user1', retcd); IF (retcd <> 0) THEN MESSAGE('ERR: BLOCK to TABLE failed with return code'to_char(retcd)); RAISE FORM_TRIGGER_FAILURE; END IF; end;

Tip

EXEC_SQL is an Oracle Forms package. Thus, the direct referencing of the package variables like EXEC_SQL.ConnType and EXEC_SQL.CursType is possible in Forms. This is in contrast to database packages, including DBMS_SQL.

Always include the following exception-handling section when using EXEC_SQL. Repeat the IF statements for each connection opened, either by default (either unspecified or using EXEC_SQL.DEFAULT_CONNECTION or EXEC_SQL.CURR_CONNECTION ) or explicitly, using EXEC_SQL.OPEN_CONNECTION :

EXCEPTION WHEN EXEC_SQL.PACKAGE_ERROR THEN IF (EXEC_SQL.LAST_ERROR_CODE(connection_handle>) <> 0) THEN MESSAGE('ERROR (' TO_CHAR(EXEC_SQL.LAST_ERROR_CODE() '): ' EXEC_SQL.LAST_ERROR_MESG()); END IF; IF EXEC_SQL.IS_CONNECTED(connection_handle>) THEN IF EXEC_SQL.IS_OPEN(, ) THEN EXEC_SQL.CLOSE_CURSOR(, ); END IF; EXEC_SQL.CLOSE_CONNECTION(); END IF;