Thanks for the question, Sivaperumal.
Asked: September 07, 2017 - 3:10 am UTC
Last updated: September 08, 2017 - 7:52 am UTC
Version: Oracle 12c
Viewed 10K+ times! This question is
You Asked
Hi team,
We are migrating DB2 Z/OS (Mainframe) to Oracle 12c R1. We are using UNLOAD utility in DB2 and SQL LOADER to load in Oracle end. We face issues in CLOB filed.
A table has one CLOB field which are unloaded with dedicated text files for each row. The data file contains the absolute path of the clob file as show blow:
We wanted to replace the " for the first column which is CLOB in datatype. We are unable to handle the replace function in control file for the clob COLUMN as there is a limitation that SQL strings cannot be used for CLOB columns. Is there a way to make the sql loader to remove/replace the " while reading the data file.
We don't want to manually replace the " in the data file.
Dada file :
"c:\users\abc\file_row1.txt"|value_field2|value_field3
"c:\users\abc\file_row2.txt"|value_field2|value_field3
Expected is :
c:\users\abc\file_row1.txt|value_field2|value_field3
c:\users\abc\file_row2.txt|value_field2|value_field3
sample Control file:
</>
LOAD DATA
INFILE 'lob_test.txt'
INTO TABLE table_test
FIELDS TERMINATED BY '|'
(clob_filename FILLER CHAR(100),
clob_content LOBFILE(clob_filename) TERMINATED BY EOF replace(:clob_filename,'"',''),
col2 ,
col3
)
</>
Thanks
Sivaperumal R
and Connor said...
Perhaps something like this:
c:\>cat c:\temp\c.ctl
load data
infile *
replace
into table t
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(
id integer external,
fname filler,
c LOBFILE(fname) TERMINATED BY EOF
)
begindata
1,"c:\temp\demo.out"
2,"c:\temp\load_emoji.sql"
SQL> create table t ( id int, c clob );
Table created.
C:\temp>sqlldr userid=scott/tigercontrol=c:\temp\c.ctl
SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 8 15:49:55 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Table T:
2 Rows successfully loaded.
Check the log file:
c.log
for more information about the load.
SQL> select id, length(c) from t;
ID LENGTH(C)
---------- ----------
1 17640
2 1418
Is this answer out of date? If it is, please let us know via a Comment