Oracle OCI Adapter Limitations

• Supported Version
• Common to all processing
• Read Table
• Execute Select SQL
• Write table
• Execute Update SQL
• Execute Stored Procedure
• Write by Loader
• Main exceptions

Supported Types

• Yes: Support.
• Limited: Support with limitations.
• No: Not supported.

Common to all processing

Specification Limits

• Table List View
  • Tables which belong to the schema of the user specified in the [User name] of Global Resource are displayed.
  • Synonyms are not displayed.

Specific to Oracle Client 11g Release 1

• When used in PSP, performing both read and write operations on the same Global Resource, whose associated database table contains Long type columns, will cause an error.
  To prevent this issue, use different Global Resource for read and write operations.

Notes

• Limitations listed in this page is for the following connections. When connecting this connector to other versions of Oracle, there may be other limitations.
  
  

  Oracle Database Client	Target Oracle Database
  11g Release 2(11.2)	11g Release 2(11.2)



• Use the library of Oracle client used in OCI connection for jdbc OCI driver.

Read Table

Specification Limits

• If the table name is enclosed by quotation marks, the table names are case-sensitive.


• Default value of [Fetch size] is "100".

Execute Select SQL

Specification Limits

• NUMBER, FLOAT type
  • When the Precision of query result is 0, handle data as BigDecimal. For example, Precision is set in column specified with SUM function and the result is returned as 0, so the result of the SUM function is handled as BigDecimal.
• SQL statements using function
  • SQL statements using SQL functions can be executed.
  • SQL statements using user-defined functions can be executed, but the value of OUT parameters defined by function cannot be received.
• Default value of [Fetch size] is "100".

Write table

Specification Limits

• The key of the schema definition
  • In case of OCI connection, in a column that has been granted a unique constraint, "key" in definition schema is not checked. It must be checked manually.
• LONG
  • When table column contains LONG type, batch update process is not performed.
• LONG RAW
  • LONG RAW type column can not be written to. When writing, DataSpiderServer process may fall due to "EXCEPTION_ACCESS_VIOLATION" error.
• BLOB
  • When table column contains BLOB type, batch update process is not performed.
• If the table name is enclosed by quotation marks, the table names are case-sensitive.

Execute Update SQL

Specification Limits

• LONG
  • When table column contains LONG type, batch update process is not performed.
• LONG RAW
  • LONG RAW type column can not be written to. When writing, DataSpiderServer process may fall due to "EXCEPTION_ACCESS_VIOLATION" error.
• BLOB
  • When table column contains BLOB type, batch update process is not performed.
• SQL statements using the function
  • SQL statements using SQL functions can be executed.
  • SQL statements using user-defined functions can be executed, but the value of OUT parameters defined by function cannot be received.
• When Oracle OCI connection settings with [XA connection] checked is specified in [Destination], the following error may occur and lead to processing failure when executing DDL.
  • [Exec_update] process failed. Cause: [java.sql.SQLException: ORA-02089: COMMIT is not available in the lower session. ]

Execute Stored Procedure

Specification Limits

• CHAR
  • OUT parameter of type CHAR, additional space may may be additional space is unnecessary.
• Return value of RETURN clause can not be retrieved.


• Procedure Notation
  • Procedure defined in the package is described as [package name]. [procedure name].
  • When dot (.) is contained in the package name, enclose it with double quotation marks (").
    For example: "SAMPLE.PACKAGE" defined in package "PACKAGE"
    • "SAMPLE.PACKAGE".PROCEDURE
  • When dot (.) is contained in the procedure name, enclose it with double quotation marks (").
    For example: "SAMPLE.PROCEDURE" defined in package "PACKAGE"
    • PACKAGE. "SAMPLE.PROCEDURE"


• PL/SQL specific types such as BOOLEAN is not supported.


• Synonyms are not supported.


• Stored procedures defined in the database schema which does not belong to the user specified in the [User name] cannot be used.

Oracle Client 11.2 specific

• When stored function is specified in the [Procedure], the following error may occur due to the fact that the value returned from the function invoked cannot be handled properly.
  • Failed to process [exec_procedure]. Cause:[java.sql.SQLException: ORA-06550: '<procedure name>' is not a procedure or is undefined ]

Write by Loader

Main exceptions

• For other exceptions, please refer to the restriction matters of adapters for each version.