Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, V sekhar.

Asked: December 28, 2000 - 11:43 am UTC

Last updated: February 02, 2004 - 1:45 pm UTC

Version: 8.0.6.0.0

Viewed 1000+ times

You Asked

I'm sure, the data will NOT have any PIPE characters. Please show the way to convert 13d into Pipe and back while loading.
Is there any other way to the same, since this method is a data dependent.

Thanks

-----------------------
Hi Tom,

Additional information is here:

The data contains carriage return ascci value 13.

eg.
DIRSV 172 AMER RESIN SLK CONV 4380550 REV 1 1 18129 172 NORYL 14-AUG-00 P016878 NN1723
0 UTIL-C
DIRSV 172 AMER RESIN SLK CONV 4380526 ORIG 1 18129 172 NORYL 11-AUG-00 P016878 NN1723
0 UTIL-C

here after values NN1723, carriage return eists, which I observed in the SQL plus. In this case what shall I do, while creting the .dat file, or loading through SQL*Loader.

the lines 400 is enough. the max. lenth is les than 100.

Thanks,
Sekhar

---------------------
Hi tom,

I'm creating a .dat file from sql select statement. the total rows aroung 160K. in the .dat file, some new line character inserted. Due to this when I load a warehouse with this .dat file using SQL Loader, such rows are not inserted. Please give me the suggestion to avoid this new line character in the .dat file.

the sql code is here:

set pause off
set verify off
set feedback off
set heading off
set termout off
set tab off
set trimout on
set timing off
set pages 8000
SET LINES 400
SPOOL /admin2/g034p/prod/scripts/qallocyear.dat
SELECT
col1||'~'||
COL2 ||'~'||
COL3 ||'~'||
COL4 ||'~'||
nvl(center,'N/A') ||'~'||
COL6 ||'~'||
COL7 ||'~'||
COL8 ||'~'||
COL9 ||'~'||
COL10 ||'~'||
COL11 ||'~'||
COL12 ||'~'||
site ||'~'||
col13||'~'||
col14||'~'
from
TAB_A;
SPOOL OFF
SET HEAD ON
SET FEEDBACK ON
SET TERMOUT ON
SET VERIFY ON

Some sample rows which have newline character:

18129~REV 1~DIRSV~9130521~NN1723
~CONV ~N/A~2000~0~0~172~0~P016878 ~14-AUG-00~SLK~NORYL~4380550~

the .log file as follows:

Record 86315: Rejected - Error on table TAB_A, column CENTER.
Field in data file exceeded maximum specified length
Record 86316: Rejected - Error on table TAB_A, column SITE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 86402: Rejected - Error on table TAB_A, column CENTER.
Field in data file exceeded maximum specified length

The .bad file is as follows:

14805~ORIG ~DIRSV~9093304~TS1710^M



~CONV ~N/A~2001~0~1~0~0~P5SR032 ~1999-12-21~MGT~STABL~4375698~

the .ctl file is:

LOAD DATA
CHARACTERSET 'US7ASCII'
INFILE 'extract.dat'
INTO TABLE wh_tab_a
FIELDS TERMINATED BY "~" OPTIONALLY ENCLOSED BY '"'
( col1 INTEGER EXTERNAL,
col2 CHAR ,
col3 CHAR ,
col4 INTEGER EXTERNAL,
CENTER CHAR,
col5 CHAR,
col6 CHAR,
col7 INTEGER EXTERNAL,
col8 DECIMAL EXTERNAL,
col9 DECIMAL EXTERNAL,
col10 DECIMAL EXTERNAL,
col11 DECIMAL EXTERNAL,
col12 CHAR,
col13 DATE(20) "YYYY-MM-DD",
SITE CHAR
)

Thanks,
Sekhar




and Tom said...

In Oracle8i, we can do this in another way without converting the characters. In Oracle8.0 and before, we have a really hard time with this. We could use a FIXED length record (you would rpad() each column on the way out and make sure each line was 400 bytes long with a newline at the end) in 8.0 but its a pain and makes for really big input files.

What I suggest is: on the way out, you want to select


.... replace(colN,chr(13),'|') ....

instead of just

.... colN ....

that'll change the 13d's into |'s on the way out. you need do this ONLY for columns that might have newlines.

In the ctl file, you would code:

colN char "replace(:colN,'|',chr(13))",


to change them back into carriage returns.



In Oracle8i, release 8.1 and up, you'll be able to use something like this:

load data
infile str.dat "str X'7c0a'"
into table T
TRUNCATE
fields terminated by ',' optionally enclosed by '"'
......


the str attribute says that a record is terminated by a pipe character (7c) followed by a newline (0a). So, I would just concatenate a | onto the end of the query above at the end of the row and we'd be done -- it would load right up without converting the in-field carriage returns.




Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to detect and remove Carriage returns

pawan, March 19, 2002 - 1:59 pm UTC

Tom,
In our application the users sometimes use a carriage return or Line feed in the addres field and thus while spooling this data and then loading into a different database the record shows up as two records and fails to load. Here is what I mean :
1 select acctid,territoryid,addr1,addr2
2 from voyager_org
3* where acctid='-1711537024'
CCW@STG> /

ACCTID TERRITORYID ADDR1 ADDR2
---------------- ------------ ------------------------------ -------------------
-----------
-1711537024 0110106A 300 East
efferson Street

-1711537024 01103A5A 300 East
efferson Street

-1711537024 06101111 300 East
efferson Street

ANd using yr query (dump)
CCW@STG> l
1 select acctid,addr1,instr(addr1,chr(10)), dump(addr1) dumpaddr1,addr2,inst
r(addr2,chr(10)), dump(addr2) dumpaddr2
2 from voyager_org
3* where acctid='-1711537024'
CCW@STG> /

ACCTID ADDR1 INSTR(ADDR1,CHR(10))
---------------- ------------------------------ --------------------
DUMPADDR1
--------------------------------------------------------------------------------
--------------------
ADDR2 INSTR(ADDR2,CHR(10))
------------------------------ --------------------
DUMPADDR2
--------------------------------------------------------------------------------
--------------------
-1711537024 300 East 10
efferson Street
Typ=1 Len=25: 51,48,48,32,69,97,115,116,32,10,101,102,102,101,114,115,111,110,32
,83,116,114,101,101,
116

NULL

-1711537024 300 East 10
efferson Street
Typ=1 Len=25: 51,48,48,32,69,97,115,116,32,10,101,102,102,101,114,115,111,110,32
,83,116,114,101,101,
116

NULL

-1711537024 300 East 10
efferson Street
Typ=1 Len=25: 51,48,48,32,69,97,115,116,32,10,101,102,102,101,114,115,111,110,32
,83,116,114,101,101,
116

NULL

----------------------
My question is how to detect and remove this hard carriage return or line feed and correct the record before sending it to other systems. Data is available on both Oracle 7.3.4.5 and 8.1.7.0

Thanks




Tom Kyte
March 19, 2002 - 7:24 pm UTC

use replace( field, chr(10), '' ) to replace the linefeed with nothing (or blank or whatever you want)

Update

pawan, March 19, 2002 - 3:19 pm UTC

Tom
I ran the folllowing query and am able to correct the problem (remove linefeed ) But my question is how can I run a query against the database and say I want to replace these LINEFEEDS from two fields (addr1 and addr2) if they Exist. I can replace linefeed when I know the record that contains it but how to find and replace.
Thanks
---
select acctid,territoryid,replace(addr1,chr(10),',')addr1 ,addr2
2 from voyager_org where acctid='-1711537024';

ACCTID TERRITORYID ADDR1 ADDR2
---------------- ------------ ------------------------------ -------------------
-----------
-1711537024 0110106A 300 East ,efferson Street
-1711537024 01103A5A 300 East ,efferson Street
-1711537024 06101111 300 East ,efferson Street

Tom Kyte
March 19, 2002 - 7:31 pm UTC

You'll have to call replace on EVERY column that you suspect might have a linefeed in it.

Thanks

Pawan, March 20, 2002 - 7:31 am UTC


Blank Line creating problem

Sri, April 03, 2003 - 1:13 pm UTC

Dear Tom,

I am using Oracle 9.0.1. Why is UTL_File creating a blank line at the end? For example, I am using the following procedure to write to a .dat file. The output I am expecting is a single line with 'abcd'. But the output I am getting is having two lines, the first with 'abcd' and the second a blank line. How can I avoid this blank line from occuring?

CREATE OR REPLACE PROCEDURE UtlTest
IS
v_thefile UTL_FILE.FILE_TYPE;
BEGIN

v_thefile := UTL_FILE.FOPEN( 'C:\CofAData\Out', 'Test.Dat', 'w' );

UTL_FILE.PUT( v_thefile, 'abcd<NEXTLINE>' );

UTL_FILE.FCLOSE( v_thefile );

END UtlTest;
/

My problem is that I am using this .dat file as an external table where I am giving "RECORDS DELIMITED BY '<NEXTLINE>'". Because of the blank line at the end, I am getting the ORA-30653: reject limit reached error. I can't give NEWLINE as record delimiters as some of the records in my table has got multi-line data. What is the work around? Please advice.

Thank you.


Tom Kyte
April 03, 2003 - 2:12 pm UTC

rtrim newlines and use utl_file.put_line instead.

How to RTrim in external table?

Sri, April 03, 2003 - 2:53 pm UTC

Tom,

As I have told, I am using an external table to read from this file. The "select * from <external table>" itself fails because of this newline.

Did I understand you wrongly?



Tom Kyte
April 03, 2003 - 3:02 pm UTC

utl_file is going to end each line with a newline (until 9ir2 when you can do binary IO). The close is going to put a newline out there when it flushes. I was suggesting you strip the newline off of the line you are putting out and use PUT_LINE itself.




Why I am getting ONE blank line at the beginning

Pawan, January 30, 2004 - 4:54 pm UTC

Tom,
I am just using SPOOL to get data to a flat file but am getting a blank line at the beinning. My script to SPOOL is

set pages 500 lines 250 heading off feedback off
spool load_recs_in_monthly_invoice.dat
select col1||'|'||to_date(trunc(ADJUSTMENT_DATE,'MM'))||'|'||REASON_CODE||'|'||LEGACY_ID||'|'||PRODUCT_ID|
|'|'||LEGACY_ID||'|'||BUSINESS_UNIT_ID||'|'|| sum(PIECES_QTY)||'|'||sum(DOZENS_QTY)||'|'||sum(_CASES_QTY)||'|'||sum(FRD_AMT)||'|'|| sum(NET_AMT)||'|'
from cso.daily_invoice WHERE ....

and the data file looks like
---------

1015245|1999-04-01|01|000010319810087338|00873300||BMP|0|0|0|0|0|

1016558|2001-05-01|15|000000319810024015|00240100||BMP|-28.009|-.131|-1.569|-158.88|-158.88|

1019461|1998-04-01|51|000010319810080674|00806700||BMP|-1572.024|-2.752|-33.013|-4683.3|-4683.3|
---------------------------
Why is the first line blank? What SET command should I use. Thanks

Tom Kyte
January 30, 2004 - 8:11 pm UTC

set embedded on


Still getting the first line as blanks

pawan, February 02, 2004 - 11:48 am UTC

Tom,
After setting the SET EMBEDDED ON I am still getting the first line as blanks. How do I get rid of the blank line.As you can see that the load_recs_in_monthly_invoice.dat has 4 lines with only 3 lines of data.
Thanks
My script to SPOOL is

set pages 500 lines 250 heading off feedback off embedded on
spool load_recs_in_monthly_invoice.dat
select
col1||'|'||to_date(trunc(ADJUSTMENT_DATE,'MM'))||'|'||REASON_CODE||'|'||LEGACY_ID
||'|'||PRODUCT_ID|
|'|'||LEGACY_ID||'|'||BUSINESS_UNIT_ID||'|'||
sum(PIECES_QTY)||'|'||sum(DOZENS_QTY)||'|'||sum(_CASES_QTY)||'|'||sum(FRD_AMT)||'
|'|| sum(NET_AMT)||'|'
from cso.daily_invoice WHERE ....

and the data file looks like
---------
1
2 1015245|1999-04-01|01|000010319810087338|00873300||BMP|0|0|0|0|0|

3 1016558|2001-05-01|15|000000319810024015|00240100||BMP|-28.009|-.131|-1.569|-158.
88|-158.88|

4 1019461|1998-04-01|51|000010319810080674|00806700||BMP|-1572.024|-2.752|-33.013|-
4683.3|-4683.3|
---------------------------


Tom Kyte
February 02, 2004 - 12:34 pm UTC 

I do not -- check again.

I mv'ed my login.sql (so I have defaults) and ran this:


set pages 500 lines 250 heading off feedback off
set trimspool on
set embedded on
spool x
select ename || '|' || empno from emp;
spool off


as a script.  results are:


SQL> @test
SMITH|7369
ALLEN|7499
WARD|7521
JONES|7566
MARTIN|7654
BLAKE|7698
CLARK|7782
SCOTT|7788
KING|7839
TURNER|7844
ADAMS|7876
JAMES|7900
FORD|7902
MILLER|7934
SQL> !cat x.lst
SMITH|7369
ALLEN|7499
WARD|7521
JONES|7566
MARTIN|7654
BLAKE|7698
CLARK|7782
SCOTT|7788
KING|7839
TURNER|7844
ADAMS|7876
JAMES|7900
FORD|7902
MILLER|7934


No blanks -- been doing this since version 7.0 at least -- you must have missed something.

with word wrapping -- i see "nothing" here really.  perhaps your linesize isn't big enough, try the small example and scale it up to see where it "stops" -- look at trimspool, linesize and others.

the first line is not blank in your example either.  I see a #1 first?

The number

pawan, February 02, 2004 - 1:45 pm UTC

# 1 in the first line is the result of :se nu which I did to see if the first line is blank. I will try your suggestion.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.