Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.