Oracle® Transparent Gateway for Rdb Administrator's Guide
10g Release 1 (10.1) for HP OpenVMS
Part No. B10537-01
After the gateway is installed and configured, you can use the gateway to access Rdb data, pass Rdb commands from applications to the Rdb database, perform distributed queries, and copy data.
This chapter contains the following sections:
The gateway can pass Rdb commands or statements from the application to the Rdb database using the DBMS_HS_PASSTHROUGH package.
Use the DBMS_HS_PASSTHROUGH package in a PL/SQL block to specify the statement to be passed to the Rdb database, as follows:
DECLARE num_rows INTEGER; BEGIN num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@RDB('command''); END; /
Where command cannot be one of the following:
Rdb tool commands
Any transaction control statement (such as COMMIT and ROLLBACK)
The DBMS_HS_PASSTHROUGH package supports passing bind values and executing SELECT statements.
Note:Prefix all tables names with the gateway SID name. For example,
See Also:Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about the DBMS_HS_PASSTHROUGH package and the different functions supported by this package.
The gateway can control the constraints mode for the Rdb database.
To set the constraints mode, set the HS_FDS_CONSTRAINTS_MODE parameter in the initialization parameter file.
See Also:Appendix D, "Heterogeneous Services Initialization Parameters" for more information about both editing the initialization parameter file and the HS_FDS_CONSTRAINTS_MODE parameter.
This parameter accepts a user defined string which is a valid Rdb statement to set the constraints mode in the Rdb database. For example, to set the constraints mode of the Rdb database to immediate, the parameter is set as follows:
HS_FDS_CONSTRAINTS_MODE="set all constraints on"
If an invalid string is passed to the Rdb database, the gateway returns the error received from the Rdb database.
If this parameter is not set, the default Rdb constraints mode is deferred.
See Also:Constraint Handling for more information.
Rdb and Oracle databases function differently in some areas, causing compatibility problems. The following compatibility issues are described in this section:
Rdb date and time values that do not contain any time information, for example, DATE ANSI, are given the time value 00:00:00 in case the requested Oracle DATE format contains time information.
In addition, the hundredths of a second information is always removed when selecting TIMESTAMP or DATE VMS values.
Query issues include the following:
The gateway passes the empty string to the Rdb database without any conversion.
The gateway returns an empty string to the Oracle database server as a NULL value. This applies only to columns defined with a VARCHAR data type.
Rdb constraints, database structures which limit column values, are evaluated at commit time. Oracle evaluates constraints as soon as a statement is executed. For example, the DEPT table in an Rdb database can have the constraint that the DEPTNO column cannot contain null values; you use Rdb interactive SQL to issue the statement:
SQL> INSERT INTO DEPT@RDB VALUES (NULL, 'TEST', 'TEST');
The row is inserted and Rdb would not return an error. The constraint is not evaluated until the transaction is committed, at which time you see something like the following:
SQL> COMMIT; %RDB-E-INTEG_FAIL, violation of constraint DEPT_DEPTNO_NOT_NULL caused operation to fail
You can use the HS_FDS_CONSTRAINTS_MODE initialization parameter to set the constraint mode for handling rdb constraints. You can also change the constraint mode handling for a specific transaction by executing the following pl/sql block at the beginning of the transaction.
See Also:Controlling the Rdb Database Constraints Mode for more information about constraint handling. See Appendix D, "Heterogeneous Services Initialization Parameters" for more information about both editing the initialization parameter file and the HS_FDS_CONSTRAINTS_MODE parameter.
The following pl/sql block can be used to set Rdb constraint handling to immediate for the gateway session:
SQL> declare 2 ret number; 3 begin 4 ret := dbms_hs_passthrough.execute_immediate@rdb 5 ('set all constraints immediate'); 6 end; 7 /
When the preceding block is executed before the same INSERT statement, the following error is returned:
SQL> INSERT INTO DEPT@RDB VALUES (NULL, 'TEST', 'TEST'); INSERT INTO DEPT@RDB VALUES (NULL, 'TEST', 'TEST'); * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for RDB]DRV_Execute: %RDB-E-INTEG_FAIL, violation of constraint T1_C1_NOT_NULL caused operation to fail -RDB-F-ON_DB, on database DISK$GTW1:[ADMIN.GTWSRC.RDB]RDB;1 ORA-02063: preceding 3 lines from RDB
If you encounter incompatibility problems not listed in this section or in "Known Problems", please contact Oracle Support Services. The following section describes the known restrictions and includes suggestions for dealing with them when possible:
Note:If you have any questions or concerns about the restrictions, contact Oracle Support Services.
The gateway cannot guarantee transactional integrity in the following cases:
When a statement that is processed by the gateway causes an implicit commit in the target database
When the target database is configured to work in autocommit mode
Note:Oracle corporation strongly recommends the following:
Any COMMIT or ROLLBACK issued in a PL/SQL cursor loop closes all open cursors, which can result in the following error:
ORA-1002: fetch out of sequence
To prevent this error, move the COMMIT or ROLLBACK statement outside the cursor loop.
When using the pass-through feature, to pass an Rdb command or statement to an Rdb database, prefix all tables names with the gateway SID name.
When executing a DDL statement with the pass-through feature, Oracle Corporation recommends that you place the statement in its own transaction. An explicit COMMIT must be issued after the DDL statement. If the SQL statements being passed through the gateway result in an implicit commit at the Rdb database, the Oracle transaction manager is unaware of the commit and an Oracle ROLLBACK command cannot be used to roll back the transaction.
Rdb provides two methods to create a database: single-schema or multischema. The gateway cannot determine whether an Rdb database is a single-schema or multischema database. Because of this, the gateway must attach to the Rdb database in single-schema mode which means that objects in the Rdb database only consist of object names.
This behavior of the gateway causes a problem when referencing objects duplicated across schemas in a multischema Rdb database. To solve the problem, SQL statements originating at the Oracle server must reference Rdb objects by their external name to prevent naming conflicts.
Subqueries in the SET clause of an UPDATE statement are not supported.
Subqueries can be specified in the WHERE clause of an UPDATE statement. Each subquery, however, must reference a Rdb table. For example, using the table GTW_EMP, the following statement results in a 10% salary increase for all employees working in the RESEARCH department:
SQL> UPDATE GTW_EMP@RDB SET SAL=SAL * 1.1 2 WHERE DEPTNO=(SELECT DEPTNO FROM GTW_EMP@RDB 3 WHERE DNAME='RESEARCH');
If GTW_DEPT@RDB is replaced by DEPT in the subquery where DEPT is the same table but located in the Oracle database, the following Oracle error results after the statement is issued:
These restrictions apply when using the ROWID feature:
The maximum number of referenced tables supported by the gateway in a view is 8. This means the string returned by ROWID has a maximum length of 88 bytes.
Ensure that you have memory when you retrieve row IDs with ROWID.
The value of a retrieved row ID in one transaction might change in a subsequent transaction. That is, the value returned by ROWID only lasts as long as the transaction it is associated with. When the Rdb database is declared with the DBKEY SCOPE IS ATTACH clause, the values retrieved by ROWID are consistent for the duration of a session. Ask your Rdb database administrator if this clause is enabled for the Rdb database you want to access.
Developer/2000, Version 3.0 and lower, are not supported because of an unsupported data type limitation with ROWID.
You cannot reference ROWID in a SQL statement which selects data from views based on tables in Rdb when these views also reference SQL functions that are not supported by the gateway.
Rdb allows column names to be 31 characters in length, but the Oracle database server limits column names to 30 characters. Because of this difference you cannot access an Rdb column with a name that is 31 characters long. If you issue a statement referencing a table that contains a column name of 31 characters, the following Oracle error message is returned, even if you do not reference the column itself:
If you issue a SELECT * statement or DESCRIBE statement against an Rdb table that contains one or more columns with names that are 31 characters long, the following Oracle errors are returned:
ORA-00972: Identifier is too long [Transparent gateway for RDB][H00C]Attempt to access a column <column> with unsupported name length (greater than 30 characters)
A way to avoid this incompatibility in column names is to define a view in the Rdb database for the table that translates all column names of 31 characters to names of 30 characters or less.
Data types that are modified by a CHARACTER SET clause are converted to a relevant data type, such as a CHAR data type.
The gateway is not multithreaded and cannot support shared database links. Each gateway session spawns a separate gateway process and connections cannot be shared.
This section describes known problems and includes suggestions for correcting them when possible. If you have any questions or concerns about the problems, contact Oracle Support Services. A current list of problems is available online. Contact your local Oracle Corporation office for information about accessing the list.
The following known problems are described in this section:
The Oracle database server supports an Oracle initialization parameter, DBLINK_ENCRYPT_LOGIN. When this parameter is set to TRUE, the password for the login user ID is not sent over the network.
If this parameter is set to TRUE in the initialization parameter file used by the Oracle database server, you must change the setting to FALSE to allow Oracle to communicate with the gateway.
Use the pass-through feature of the gateway if you need to use Rdb DDL statements against an Rdb database.
See Also:Using the Pass-Through Feature for more information.
The gateway does not support addition and subtraction of dates in a SELECT list or in a WHERE clause.
For example, the column DATE_COL in table DATE_TAB is defined as data type DATE VMS:
SQL> SELECT DATE_COL + 3 FROM DATE_TAB@RDB;
The following Oracle error is returned:
Date and time values retrieved by the gateway are translated to the Oracle date format by the Oracle database server. You can lose the time portion of a column defined as DATE VMS in this translation process.
In the following example, BIRTHDATE is defined as data type DATE VMS, and the results are translated to the Oracle default date format:
SQL> SELECT * FROM BIRTHS@RDB; ENAME BIRTHDATE ------ --------- ALLEN 01-JAN-45 CHAN 31-MAR-61 JONES 12-OCT-65 MARTIN 03-FEB-58