[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6112
  • Last Modified:

Carriage return in oracle external table

Hi,

If the user FTPed the flat file in Binary mode; file has  ^M character at the end of each line. When we load into the external table we need to avoid that carriage return. How do we do that?
How to use  a carriage return fucntion in oracle external table?

Belwo syntax tried but in vain:(

thanks
victor
CREATE TABLE diff
    (plan_ctry                      VARCHAR2(3),
    division                       VARCHAR2(2))
  ORGANIZATION EXTERNAL (
   DEFAULT DIRECTORY  DPR_DATA_DIR
    ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
BADFILE DPR_LOG_DIR:'diff.bad'
DISCARDFILE DPR_LOG_DIR:'diff.dsc'
LOGFILE DPR_LOG_DIR:'diff.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
    (plan_ctry CHAR,                     
    division  CHAR(2)  "RTRIM(CHR(13))"
  ))
   LOCATION (
    DPR_DATA_DIR:'test.csv'
   )
  )
   REJECT LIMIT UNLIMITED
/

Open in new window

0
victory_in
Asked:
victory_in
  • 11
  • 9
1 Solution
 
omarfaridCommented:
why not transfer using ascii mode or use command like dos2unix to get rid of ^M
0
 
victory_inAuthor Commented:
Hi,

There are so many users are FTPing the file. Due to the fact that we need  to be an extra security so that our batch job won't fail. This is one of requirement. So... any thoughts:)
0
 
lwadwellCommented:
Hi victory_in,

You would need the field to be rtrim'd specified in the function ... "RTRIM(:division,CHR(13))"

You could also try "REPLACE(:division,CHR(13),null)" instead of the RTRIM.

lwadwell
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
victory_inAuthor Commented:
Hi, when I try with both option as said above it threw the following error

*** SCRIPT START :  Session:NSCDPREP@CI2DE6(1)   22-Apr-2009 23:20:11 ***
Processing ...
SELECT a.plan_ctry, a.division
  FROM diff_test a
SELECT a.plan_ctry, a.division
*
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "double-quoted-string": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01007: at line 9 column 14
ORA-06512: at "SYS.ORACLE_LOADER", line 19
*** Script stopped due to error ***
*** SCRIPT END :  Session:NSCDPREP@CI2DE6(1)   22-Apr-2009 23:20:11 ***


CREATE TABLE diff
    (plan_ctry                      VARCHAR2(3),
    division                       VARCHAR2(2))
  ORGANIZATION EXTERNAL (
   DEFAULT DIRECTORY  DPR_DATA_DIR
    ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
BADFILE DPR_LOG_DIR:'diff.bad'
DISCARDFILE DPR_LOG_DIR:'diff.dsc'
LOGFILE DPR_LOG_DIR:'diff.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
    (plan_ctry,                     
    division "REPLACE(:division,CHR(13),null)"
  ))
   LOCATION (
    DPR_DATA_DIR:'test1.csv'
   )
  )
   REJECT LIMIT UNLIMITED
/

Open in new window

0
 
lwadwellCommented:
victory_in,

OK.  Not nice ... I have done things like that with SQL Loader ... I must admit I have never tried with an organization external table (which uses SQL Loader to materialize).

Will the files always be in DOS format?  I am guessing, but I wonder if you could specify RECORDS DELIMITED BY 0x'0a0d' ... ?

Another alternative ... slightly clumsy ... is to create a VIEW on the loaded table with the ^M chars, and have the RTRIM() function in the VIEW.

lwadwell
0
 
victory_inAuthor Commented:
Hi

RECORDS DELIMITED BY 0x'0a0d' ... ? it didn't work as well.

I already created the view and we are using currently. My boss was not happy the reason being we have 10 external tables, which means we need to create 10 views on the top of that. He thinks it is bad practice.

Trying to find out we can tweak in External tables itself.

Any thoughts?

Thanks
Victor
0
 
lwadwellCommented:
victory_in,

Sorry, DOS format is CR LF which is 0d 0a in hex ... I supplied them in the wrong order
RECORDS DELIMITED BY 0x'0d0a'

lwadwell
0
 
victory_inAuthor Commented:
Hi,

in that case it won't work for other way round. Please help!!!!.

It should work both the ways (i..e with ^M characters & without ^M characters)

In the above case (ID: 24212914) it worked for ^M is there in the file. But it didn't work if there is no ^M characters.

Thanks
Victor
0
 
lwadwellCommented:
lwadwell,

I am not sure we can get an answer that will work both ways ... with the external table the way it is - it is going to expect consistent file structures, it will not be dynamic based on the file structure.

Do you have option of requesting that the input files have a trailing delimiter ',' on the end of each line?

Can you not, in you scripts ensure the file is converted to Unix structure?

lwadwell
0
 
victory_inAuthor Commented:
Hi,

There are so many end users FTPing. We cannot restrict them or tell them each time. Even after telling they will transfer the file in BINARY mode. Right now we are manually validating by looking everyday after they FTP.

We want to automate this process irrespective whether they FTP in BINARY or ASCII mode, no matter what.

Any further lights on this please?

Victor
0
 
lwadwellCommented:
victory_in,

I wasn't suggesting a change to the behaviour of the users FTP ... I know how hard that is myself.  What I was suggesting was:
a) if the files have an extra delimiter after the last column eg "XXX,YYY,^M" ... the ^M will not be seen as part of the last column as the extra delimiter will restrict that.
and/or
b) in your scripts or a new one do an explicit `dos2unix` on the file(s) anyway ... whether when you run this in your scripts or as a simple cron task every n minutes.  Am I correct in assuming that the users FTP the files to a landing directory and your scripts copy them to the directory they will be loaded from as the external table?


lwadwell
0
 
victory_inAuthor Commented:
Hi,

What is happenning currently is users open an EXCEL sheet and enter all the data, then they convert EXCEL sheet into .CSV file. All is fine till here.

Then users will FTP the file into unix directory. Then we read blindly by using the EXTERNAL TABLE.

We can restrict in EXCEL SHEET itself adding dummy column at the end and hide the column? Any thought?

Thanks
--Victor


0
 
lwadwellCommented:
victory_in,

should work ... the idea is to get Oracle to recognise the end of the value before the ^M, if it exists.

I did a test of an external table with 'extra columns' on the end of the file that are not referenced - it seemed to work fine.

lwadwell
0
 
victory_inAuthor Commented:
Hi,

one issue here is when you hide the column in the EXCEL SHEET, when you open it will show the hidden column again. Any idea how we can hide it permanently so that end users won't get confused when they open it?

Victor
0
 
lwadwellCommented:
victory_in,

never had that problem.  When I hide columns, right click on the column letter and select 'hide' ... they stay hidden.

btw. are the users exporting ("save as") the data themselves as a csv or have you given then a customer script to do it?

lwadwell
0
 
victory_inAuthor Commented:
No,  

We providing them a excel sheet. They convert into .CSV by themself and FTP it.

All we do is read that file thru EXTERNAL TABLE.

Our Batch jobs used fail  due to the fact of ^M

Thanks
Victor
0
 
lwadwellCommented:
victory_in,

OK ... I still don't know how to fix your problem with the column not staying hidden... sorry.



lwadwell
0
 
victory_inAuthor Commented:
mmm......

This has become nightmare for me. Lot of external tables are used here. If we fix this nothing like that.
Anyways.

Let me know any other thoughts, otherwise I'll close this chain.

Finally thanks for your I/p,Do appreciate it.

Victor




0
 
lwadwellCommented:
victory_in,

one last try ... hopefully a "M is seen as whitespace (so return to RECORDS DELIMITED BY newline)

FIELDS TERMINATED BY ',' RTRIM

lwadwell
0
 
victory_inAuthor Commented:
Hi lwadwell:,

It worked. This is the thing we are searching for....
Million Milion thanks. I would have given 5 million points. You are just Genius.
Now they are tesing with the real data and after that I'll accept your solution.

Thanks again
Victor

0
 
victory_inAuthor Commented:
Excellent solution
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now