| Oracle® Transparent Gateway for Ingres II Administrator's Guide 10g Release 2 (10.2) for Solaris Operating System (SPARC) Part Number B14278-01 |
|
|
View PDF |
| Oracle® Transparent Gateway for Ingres II Administrator's Guide 10g Release 2 (10.2) for Solaris Operating System (SPARC) Part Number B14278-01 |
|
|
View PDF |
After the gateway is installed and configured, you can use the gateway to access Ingres II data, pass Ingres II commands from applications to the Ingres II database, perform distributed queries, and copy data.
This chapter contains the following sections:
The gateway can pass Ingres II commands or statements from the application to the Ingres II 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 Ingres II database, as follows:
DECLARE
num_rows INTEGER;
BEGIN
num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@INGR('command');
END;
/
Where command cannot be one of the following:
COMMIT
ROLLBACK
SAVEPOINT
The DBMS_HS_PASSTHROUGH package supports passing bind values and executing SELECT statements.
See Also:
PL/SQL Packages and Types Reference and Chapter 3 of Oracle Heterogeneous Connectivity Administrator's Guide for more information about the DBMS_HS_PASSTHROUGH package.Ingres II and Oracle databases function differently in some areas, causing compatibility problems. The following compatibility issues are described in this section:
Naming rule issues include the following:
Oracle and Ingres II use different database object naming rules. For example, the maximum number of characters allowed for each object name can be different. Also, the use of single and double quotation marks, case sensitivity, and the use of alphanumeric characters can all be different.
See Also:
Oracle Database Reference and Ingres II documentation.Names of Ingres II database objects are limited to a maximum of 32 characters. An object name can be composed of these characters:
Numbers 0 to 9
Lowercase letters a to z
Uppercase letters A to Z
Underscore character (_)
Special characters "#". "@", and "$"
Ingres II handles letter case differently from Oracle. Ingres II uses these rules:
Table owner names default to uppercase letters, unless the name is surrounded by double quote characters
Column names, table names, view names, and so on, are always treated as lowercase letters
The Oracle database server defaults to uppercase unless you surround identifiers with double quote characters. For example, to refer to the Ingres II table called emp, enter the name with double quote characters, as follows:
SQL> SELECT * FROM "emp"@INGR;
However, to refer to the Ingres II table called emp owned by Scott from an Oracle application, enter the following:
SQL> SELECT * FROM "Scott"."emp"@INGR;
If the Ingres II table called emp is owned by SCOTT, a table owner name in uppercase letters, you can enter the owner name without double quote characters, as follows:
SQL> SELECT * FROM SCOTT."emp"@INGR;
Or
SQL> SELECT * FROM scott."emp"@INGR;
Oracle recommends that you surround all Ingres II object names with double quote characters and use the exact letter case for the object names as they appear in the Ingres II data dictionary. This convention is not required when referring to the supported Oracle data dictionary tables or views listed in Appendix C, "Data Dictionary".
If existing applications cannot be changed according to these conventions, create views in Oracle to associate Ingres II names to the correct letter case. For example, to refer to the Ingres II table emp from an existing Oracle application by using only uppercase names, define the following view:
SQL> CREATE VIEW EMP (EMPNO, ENAME, SAL, HIREDATE) AS SELECT "empno", "ename", "sal", "hiredate" FROM "emp"@INGR;
With this view, the application can issue statements such as the following:
SQL> SELECT EMPNO, ENAME FROM EMP;
Using views is a workaround solution that duplicates data dictionary information originating in the Ingres II data dictionary. You must be prepared to update the Oracle view definitions whenever the data definitions for the corresponding tables are changed in the Ingres II database.
Data type issues include the following:
The Oracle database server supports dates through December 31, 4712. Ingres II supports dates in the range of January 1, 1582 through December 31, 2382. If a date beyond the range of Ingres II is passed to the Ingres II database, an error is returned.
When only a time value is given for a DATE data type field, Ingres II adds the current date to the time value, while the Oracle database server adds the date of the first day.
Ingres II does not support implicit conversions between character data types and numeric data types. Such conversions must be explicit.
See Also:
Appendix A, "Data Type Conversion" for more information about restrictions on data types.Query issues include the following:
The gateway processes an empty string in a SQL statement as a null value. Ingres II processes an empty string as an empty string.
Comparing to an empty string
The Gateway passes literal empty strings to the Ingres II Server database without any conversion. If you intended an empty string to represent a null value, Informix does not process the statement that way; it uses the empty string.
You can avoid this problem by using NULL or IS NULL in the SQL statement instead of the empty string syntax, as in the following example:
SELECT * from "emp"@INGR where "ename" IS NULL;
Selecting an empty string:
For VARCHAR columns, the gateway returns an empty string to the Oracle database server as NULL value.
For CHAR columns, the gateway returns the full size of the column with each character as empty space (' ').
The locking model for an Ingres II database differs significantly from the Oracle model. The gateway depends on the underlying Ingres II behavior, so Oracle applications that access Ingres II through the gateway can be affected by the following possible scenarios:
Read access might block write access.
Write access might block read access.
Statement-level read consistency is not guaranteed.
See Also:
Ingres II documentation for information about the Ingres II locking model.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:
The following restriction also applies:
When negative numbers are used in a SUBSTR function, incorrect results are returned. This is due to the way the Gateway compensates for the lack of a SUBSTR function in Ingres II.
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 strongly recommends the following:If you know that executing a particular statement causes an implicit commit in the target database, then ensure that this statement is executed in its own transaction.
Do not configure the target database to work in autocommit mode.
The gateway does not support savepoints. If a distributed update transaction is under way involving the gateway and a user attempts to create a savepoint, the following error occurs:
ORA-02070: database dblink does not support savepoint in this context
By default, the gateway is configured as COMMIT_CONFIRM.
See Also:
Appendix D, "Heterogeneous Services Initialization Parameters" and the Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about customizing the initialization parameter file.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.
If the SQL statements being passed through the gateway result in an implicit commit at the Ingres II database, the Oracle transaction manager is unaware of the commit and an Oracle ROLLBACK command cannot be used to roll back the transaction.
The gateway supports only INSERT and UPDATE of LONG BYTE and LONG VARCHAR data up to 31,900 bytes and 61,900 bytes, respectively, when using bind variables.
An unsupported SQL function cannot be used in an SQL statement which accesses a column defined as Ingres II data type LONG BYTE or LONG VARCHAR. See Appendix B, "Supported SQL Syntax and Functions" for more information.
You cannot use SQL*Plus to select data from a column defined as Ingres II data type LONG BYTE or LONG VARCHAR when the data is greater than 80 characters in length. Oracle recommends using Pro*C or Oracle Call Interface to access such data in a Ingres II database.
The gateway does not support the PL/SQL function COLUMN_VALUE_LONG of the DBMS_SQL package.
This section lists restrictions on the following SQL syntax:
See Also:
Appendix B, "Supported SQL Syntax and Functions" for more information about restrictions on SQL syntax.UPDATE and DELETE statements with the WHERE CURRENT OF clause are not supported by the gateway because they rely on the Oracle ROWID implementation. To update or delete a row through the gateway, a condition style WHERE clause must be used.
You need to use double quotes to wrap around lower case table names.
For example:
copy from tkhouser/tkhouser@inst1 insert loc_tkhodept using select * from "tkhodept"@holink2;
The gateway is not a shared server process 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 office for information about accessing the list.
The following known problems are described in this section:
The Oracle database server no longer supports the DBLINK_ENCRYPT_LOGIN initialization parameter. Up to version 7.3, this parameter default TRUE value prevented the password for the login user ID from being sent over the network in the clear. Later versions automatically encrypt the password.
The following SQL expressions do not function correctly with the gateway:
date + numbernumber + datedate - numberdate1 - date2
Statements with the preceding expressions are sent to the Ingres II database without any translation. Since Ingres II does not support these date arithmetic functions, the statements return an error.
Incorrect negative values might be returned from a SELECT statement that retrieves data from columns defined as MONEY that contain negative values near the precision limit.
If you concatenate numeric literals using the "||" or CONCAT operator when using the gateway to query a Ingres II database, the result is an arithmetic addition. For example, the result of the following statement is 18:
SQL> SELECT 9 || 9 FROM DUAL@INGR;
The result is 99 when using Oracle to query an Oracle database.
The POSITION function is translated by the Oracle Transparent Gateway for Ingres II into the Ingres II LOCATE function. The Ingres II LOCATE function returns the first position of the specified string, unless the string is not found, in which case the maximum size of the field plus one is returned.
When the POSITION function is used with the MAX function, the result returned is the size of the field plus one when the string specified is not found in all the rows.
When the POSITION function is used with the MIN function, the result returned is the size of the field plus one when the string specified is not found in any of the rows.
If you do not prefix an Ingres II database object with its schema name in a SQL statement within a PL/SQL block, the following error message occurs:
ORA-6550 PLS-201 Identifier table_name must be declared.
Change the SQL statement to include the schema name of the object.
