0
ASCII control characters
ASCII printable
characters
Extended ASCII
characters
REGEXP_REPLACE (COLUMN,'[^' || CHR (32) || '-' || CHR (127) || ']', ' '))
ASCII printable
Extended ASCII
0
GRANT ALL on Table TO testuser;
ALTER DATABASE MPDPD01 SET DEFAULT SCHEMA testschema
select database, defschema from _v_database where database='TESTDB'
0
"MM/dd/yyyy HH:mm:ss"
"MM/dd/yyyy HH:mm:ss"
"yyyy-MM-dd HH:mm:ss"
"yyyy-MM-dd HH:mm:ss.SSS"
Talend downloading data from a big table into multiple flat files and reloading it back to DB using tFile
0
Problem: Downloading data from a big table into multiple flat files and reloading back to DB.
Solution: – Different options are available – I have used following components
One db input component – tjdbcinput to read from source table.
tfileOutputdelimited to write to file – with options of split into multiple files and each file to contain some x number of rows. and subsequently tFileInputdelimited and tjdbcoutput to read these flat files and insert back to DB
Talend can generate multiple files from same tfileoutputdelimited component by appending 0,1,...to file name - for example if you filename is input_file.csv - talend will generate output files as input_file0.csv, input_file1.csv and so on. This will happen if you check split into several file option.
Also while loading these files back to DB we need to read them in a loop in a directory by using wildcard search for that i have used tFileList with filemask to get list of files starting with a particular name.
Other important settings are case sensitive (in file name) YES/NO
Filemask - input_file* - to get list of all files with input_file as starting name.
Filename - for tFileInputDelimited component - this should come from variable tFileList*_CURRENT_FILEPATH
 |
| part of job for reading data from Db and writing to flat file |
 |
| Complete job for inserting data back to DB |
 |
| tFileList settings |
 |
| tFileInputDelimited settings |
0
Error : ORA-31061: XDB error: special char to escaped char conversion failed.
Cause : This is error is due to the presence of control characters in the data
Solution : use Regexp_replace(Data,''[[:cntrl:]]'','') to cleanse the data
Example:
select xmlelement("a", test) from (select regexp_replace(unistr('a\0013b'), '[[:cntrl:]]', '') test from dual);
Control Characters : https://www.joecolantonio.com/2011/07/26/qtp-ascii-chr-code-chart/
