Oracle database extract of a CLOB
Posted: Fri Dec 09, 2011 11:24 am
created a simple table on an existing database in our environment and populate it with one record like so:
CREATE TABLE R_EXTRACT (
ID INTEGER NOT NULL,
TIME TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
CONTENTS CLOB NOT NULL,
PRIMARY KEY(ID)
) TABLESPACE "LOG";
INSERT INTO R_EXTRACT (ID, CONTENTS) VALUES (1,'This is a test extract file');
I then configured a project within Director with a database task that executes the following sql against that database:
SELECT CONTENTS FROM RTI_EXTRACT WHERE ID = 1
I then added a second task which writes the resultset (which should only be the contents of one column) out to a file.
I tried using both a ‘Write Fixed-Width’ and ‘Write CSV’ tasks, however both resulted in the following error when executing the project.
“Data ttype 'CLOB' is currently not supported by this task”
Can Linoma advise what datatypes are supported for what write tasks? Is there a better way within Director of doing what I have tried to achieve?
CREATE TABLE R_EXTRACT (
ID INTEGER NOT NULL,
TIME TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
CONTENTS CLOB NOT NULL,
PRIMARY KEY(ID)
) TABLESPACE "LOG";
INSERT INTO R_EXTRACT (ID, CONTENTS) VALUES (1,'This is a test extract file');
I then configured a project within Director with a database task that executes the following sql against that database:
SELECT CONTENTS FROM RTI_EXTRACT WHERE ID = 1
I then added a second task which writes the resultset (which should only be the contents of one column) out to a file.
I tried using both a ‘Write Fixed-Width’ and ‘Write CSV’ tasks, however both resulted in the following error when executing the project.
“Data ttype 'CLOB' is currently not supported by this task”
Can Linoma advise what datatypes are supported for what write tasks? Is there a better way within Director of doing what I have tried to achieve?