Oracle LOB and XMLType data types

The Oracle connector supports reading and writing the XMLType data type and the Oracle LOB data types BFILE, BLOB, CLOB, NCLOB, LONG RAW, RAW.

When you configure the Oracle connector to read data from a database table that contains LOB columns, you specify how to produce the LOB field values on the output link. The choices are inline or by reference.

When you use the inline form for LOB field values, the connector produces the actual values. Because the actual values are transferred on the link, use the inline form when the LOB values are relatively small, typically not more than a few hundred KB. To configure the connector to use the inline form, set Enable LOB references to No.

Use the reference form to transfer LOB values that are relatively large, typically more than 1 MB, from the source stage to the target stage. However, when you use the reference form, interim stages cannot process the actual values. For example, if you add a Transformer stage to a job, the Transformer stage cannot perform operations on the actual LOB values because only the reference strings, not the actual values, are transferred through the job.

To configure the Oracle connector to use the reference form, set Enable LOB references to Yes. Then, in the Columns for LOB references property, select the columns to pass by reference. Only link columns of LongVarChar, LongNVarChar and LongVarBinary data types are available for selection.

When a downstream LOB-aware stage receives the reference string on its input link, the stage engages the Oracle connector to retrieve the actual value that the reference string represents. The stage then processes that actual value. The connector outputs these reference strings as the values of the fields. When a downstream LOB-aware stage requires the values, the connector uses the information in the reference strings to retrieve the actual values and then passes them to the downstream stage, which loads the values into the target table. The LOB-aware stages include the Db2® connector, WebSphere® MQ connector, ODBC connector, Teradata connector, and Oracle connector. If you specify a target stage that is not LOB-aware, the target stage cannot recognize the reference string as a special locator value and treats the reference string as ordinary data.

Consider these potential issues when you configure the connector to read and write LOB data:
  • The connector supports both the inline and reference form to transfer BFILE, BLOB, CLOB, NCLOB, and XMLType columns.
  • The connector supports only the inline form to transfer LONG and LONG RAW columns. The length attribute for the column on the link must be set to the maximum expected length for the actual data at run time.
  • If at run time Oracle connector dynamically adds a column to the link that has the Runtime Column Propagation setting enabled and the link column corresponds to a LONG or LONG RAW table column in the database, the connector sets the link column length to the maximum value that meets both of the following conditions:
    • The value does not exceed 999999.
    • When the value is multiplied by the value that is specified in the Array size property for the stage, the product does not exceed 10485760 (the number of bytes in 10 MB).
  • When you configure the Oracle connector to read data from a BFILE column, you can transfer the actual file contents, or you can transfer only a reference to the file location. If you transfer the file contents of a BFILE, set the Transfer BFILE contents property to Yes. By default, Transfer BFILE contents is set to No, and the connector transfers only the reference to the file location.
  • When you configure the connector to read XMLType data and manually create the SELECT statement, you must use an alias to reference the table. Also, the XMLType column must use the Oracle GETCLOBVAL() or GETBLOBVAL() member function to get the actual XML content as BLOB or CLOB. If the column on the output link is defined as LongVarChar or LongNVarChar and passed inline, use the Oracle GETCLOBVAL() member function. If the column is defined as LongVarBinary and passed inline, use the GETBLOBVAL() member function. Do not use the GETCLOBVAL() and GETBLOBVAL() member functions when you pass XMLType columns as LOB references. To read from an XMLType object table or object view, use the OBJECT_VALUE pseudonym for the column name.
  • When you configure the connector to write XMLType data, if the column on the input link is defined as Binary, VarBinary, or LongVarBinary, you must use the Oracle SYS.XMLTYPE.CREATEXML() member function in the SQL statement to create the XML content.

Example: Writing to an XMLType column

The following statement is the table definition:
CREATE TABLE TABLE1 (COL1 NUMBER(10), COL2 XMLTYPE) XMLTYPE COL2 STORE 
  AS BINARY XML;
To write the binary XML value to the XMLType column, enter this INSERT statement in the Insert statement property in the connector:
INSERT INTO TABLE1 (COL1, COL2)	VALUES (ORCHESTRATE.COL1, 
	SYS.XMLTYPE.CREATEXML(ORCHESTRATE.COL2, 1, NULL, 1, 1));

In this example, the second parameter of the SYS.XMLTYPE.CREATEXML function specifies the character set ID for the US7ASCII character set in Oracle. The third parameter is an optional schema URL that forces the input to conform to the specified schema. The fourth parameter is a flag that indicates that the instance is valid according to the specified XML schema. The fifth parameter is a flag that indicates that the input is well formed.

Example: Reading XMLType data from a standard table or view

The following statement is the table definition:
CREATE TABLE TABLE1 (COL1 NUMBER(10), COL2 XMLTYPE)
	XMLTYPE COL2 STORE AS CLOB;
To retrieve the XML value as a CLOB value, enter this SELECT statement in the Select statement property in the connector:
SELECT COL1, T.COL2.GETCLOBVAL() FROM TABLE1 T;
To retrieve the XML value as a BLOB value that uses the character encoding AL32UTF8, enter this SELECT statement in the Select statement property in the connector:
SELECT COL1, T.COL2.GETBLOBVAL(893) FROM TABLE1 T;

The number 893 is the character set ID for the AL32UTF8 character set in Oracle. Oracle defines a character set ID for each character encoding that it supports. For information about the supported character encodings and IDs, see the Oracle documentation.

Example: Reading XMLType data from an object table

The following statement is the table definition:
CREATE TABLE TABLE1 OF XMLTYPE XMLTYPE 
	STORE AS BINARY XML;
To retrieve the XML value as a CLOB value, enter this SELECT statement in the Select statement property in the connector:
SELECT T.OBJECT_VALUE.GETCLOBVAL() FROM TABLE1 T;

To retrieve the XML value as a BLOB value that uses the US7ASCII character encoding, enter this SELECT statement in the Select statement property in the connector:

SELECT T.OBJECT_VALUE.GETBLOBVAL(1) FROM TABLE1 T;

The number 1 is the character set ID for the US7ASCII character set in Oracle.

Example: Reading XMLType data from an object view

This example uses the TABLE1 table, which was defined in the previous example. The following statement is the view definition:

CREATE VIEW VIEW1 AS SELECT * FROM TABLE1;
To retrieve the XML value from the VIEW1 view as a CLOB value, enter this SELECT statement in the Select statement property in the connector:
SELECT V.OBJECT_VALUE.GETCLOBVAL() FROM VIEW1 V;