Hi,
I am reading the data from Oracle database and creating file(same process for around 100 tables).these are 1 to 1 straight mappings,So i am using schema files. i have one table having 3 columns but last column datatype is Varchar2(4000). but when i am trying to write the records in files, record is splitting into more than 1 record. i think it looks because of varcha2(4000), we are facing this issue. that filed having spaces between words and some space at the end also. data not splitting into different record, but new record creating with space. please let me know how to create the record in file without having issue.
Thanks in Advance..
How to handle field data type varchar2(4000)
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 81
- Joined: Thu Nov 30, 2006 7:46 am
- Location: india
Typically, a records "splits" like that when a large text field has record limiters in it. And typically that's because it has either been hand-typed by a user who it trying to make it "all pretty" or it has been pasted from a document into the database. Any chance of that, a stray carriage return perhaps? The EReplace() or Convert() function could be used to remove them before writing to the flat file, if that's the case. Or leverage the Oracle REPLACE function in the source SQL.
All of which invalidates the idea of a straight-thru, schema file based RCP job for this one table, I do believe.
All of which invalidates the idea of a straight-thru, schema file based RCP job for this one table, I do believe.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 81
- Joined: Thu Nov 30, 2006 7:46 am
- Location: india
-
- Participant
- Posts: 81
- Joined: Thu Nov 30, 2006 7:46 am
- Location: india
A transformer stage must know its metadata at compile time, so to use a transform function such as Convert() you would need to operate on a named column. You could surround the transformer stage with a pair of parameterized modify stages to rename the column of interest (the modify stage does not need to know its metadata until run time). Set up a parameter set with a values file per table to assist with the process.
Another alternative is to clean the data in the database before you send it through your generic DataStage job. It would be fairly easy to write some sql that utilizes the database catalog to generate sql to replace newline characters embedded in varchar(4000) fields.
Mike
Another alternative is to clean the data in the database before you send it through your generic DataStage job. It would be fairly easy to write some sql that utilizes the database catalog to generate sql to replace newline characters embedded in varchar(4000) fields.
Mike