Oracle OCI Adapter Limitations

• Supported Types
• Limitations Common to All Operations
• Read Table
• Execute Select SQL
• Write Table
• Execute Update SQL
• Execute Stored Procedure
• Write by Loader
• Major Exceptions

Supported Types

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

Limitations Common to All Operations

Specification Limits

• Displaying table list
  • Tables that belong to the schema of the user specified in [User name] in the global resource are displayed.
  • Synonym is not displayed.

Specific to Oracle Client 11g Release 1

• When Read operation and Write operation using the same global resource are executed in PSP, and if a LONG type column exists in the read target table, an error occurs.
  In that case, use separate global resources for Read operation and Write operation.

Notes

• The limitations described in this page are for the following connections. The limitations might not apply when connecting to the other versions of Oracle Database with this adapter.
  
  

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



• Use an Oracle Client library used in OCI connection for JDBC driver.

Read Table

Specification Limits

• When the table name is enclosed with quotation marks, it is case-sensitive.


• Default value of [Fetch size] is 100.

Execute Select SQL

Specification Limits

• NUMBER, FLOAT
  • When the Precision of a query result is 0, the data is handled as BigDecimal. For example, even if Precision is set to the column specified in SUM function, 0 will be returned as a result, so the result of SUM function will be handled as BigDecimal.
• SQL statement with functions
  • SQL statement with SQL functions can be executed.
  • Though the SQL statement with user-defined functions can be executed, the value of OUT parameter defined in the functions cannot be received.


• Default value of [Fetch size] is 100.

Write Table

Specification Limits

• Schema definition key
  • In the case of OCI connection, “Key” of schema definition is not checked even for columns where unique constraint is added. Check manually.
• LONG
  • When LONG type is included in table columns, batch update is not performed.
• LONG RAW
  • LONG RAW type column cannot be written to. When writing, “EXCEPTION_ACCESS_VIOLATION” error occurs and DataSpiderServer operation might abort.
• BLOB
  • When BLOB type is included in table columns, batch update is not performed.
• When the table name is enclosed with quotation marks, it is case-sensitive.

Execute Update SQL

Specification Limits

• LONG
  • When LONG type is included in table columns, batch update is not performed.
• LONG RAW
  • LONG RAW type column cannot be written to. When writing, “EXCEPTION_ACCESS_VIOLATION” error occurs and DataSpiderServer operation might abort.
• BLOB
  • When BLOB type is included in table columns, batch update is not performed.
• SQL statement with functions
  • SQL statement with SQL functions can be executed.
  • Though the SQL statement with user-defined functions can be executed, the value of OUT parameter defined in the functions cannot be received.

Execute Stored Procedure

Specification Limits

• CHAR
  • Unnecessary spaces might be added to an OUT parameter of CHAR type.
• Cannot get the return value of RETURN clause.


• Procedure description
  • Procedures defined in a package are described as [package name].[procedure name].
  • When a package name includes dot (.), enclose the name with double quotations (").
    Example: In the case of “PROCEDURE” procedure that is defined in “SAMPLE.PACKAGE” package
    • "SAMPLE.PACKAGE".PROCEDURE
  • When a procedure name includes dot (.), enclose the name with double quotations (").
    Example: In the case of “SAMPLE.PROCEDURE” procedure that is defined in “PACKAGE” package.
    • PACKAGE."SAMPLE.PROCEDURE"


• PL/SQL types such as BOOLEAN are not supported.


• Synonym is not supported.


• A procedure of a schema that is different from the schema of the user specified in [User name] in the global resource cannot be used.

Specific to Oracle Client 11.2

• When a stored function is specified in [Procedure], there are cases in which the return value of RETURN statement cannot be retrieved as output parameter, and the operation fails with the following error during script execution.
  • [exec_procedure] failed. Cause: [java.sql.SQLException: ORA-06550: '<procedure name>' is not a procedure or is undefined.]

Write by Loader

Major Exceptions

• For more details on other exceptions, refer to the limitations of each adapter versions.