Skip to Main Content
  • Questions
  • Want to skip record if it's length not matching with required length while loading data in oracle external table

Breadcrumb

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.

Question and Answer

Chris Saxon

Thanks for the question, MANOJ.

Asked: June 23, 2016 - 7:56 am UTC

Answered by: Chris Saxon - Last updated: June 29, 2016 - 3:23 am UTC

Category: Developer - Version: oracle 11 g

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: How big is my offline datafile?

You Asked

Hi Tom,

I want to load data from fixed length file to oracle external table.
I have specified length for each column while creating external table so data for most records getting loaded correctly. But if record length dosent match then data gets shifted to another columns. want to skip that records.

eg.
My file is having data like.
EMPNO 1-5,
FIRSTNAME 6-15
LASTNAME 16-25

EMPNO FIRSTNAME LASTNAME
12345 MANOJ JADHAV --(Total record length expected is 25 and this record will get loaded, if column is having less data it is padded with blank space)
23456 ABCDEFGHIJK ABCDEFGHIJ --(But for second record we get extra 2 characters(Total 27) so last two characters will not get loaded correctly)

I need to skip the record if total record length dosent match with specified length.

In above case, i want to load first record because it is having required length i.e. 25 and skip 2nd record because it is having length as 27.

Can we skip records like this?


Thanks,
Manoj

and we said...

"But if record length dosen't match then data gets shifted to another columns"

Sounds to me like you need to change the field terminator?

If the contents of your file is:

12345 MANOJ JADHAV
23456 ABCDEFGHIJK ABCDEFGHIJ


Then you can make " " (space) the field separator:

create table t (
  x int,
  y varchar2(20),
  z varchar2(20)
) organization external (
  type oracle_loader
  default directory tmp
  access parameters (
    records delimited by newline
    fields terminated by " " 
  )
  location ('emps.dat')
) reject limit unlimited;

select * from t;

         X Y                    Z                  
---------- -------------------- --------------------
     12345 MANOJ                JADHAV              
     23456 ABCDEFGHIJK          ABCDEFGHIJ  


If you do need to limit the total length there are a couple of options:

1. Leave the external table as-is. Add a where clause when loading to bypass the unwanted rows:

select * from t
where  length(to_char(x) || y || z) <= 25;

         X Y                    Z                  
---------- -------------------- --------------------
     12345 MANOJ                JADHAV


2. Update the external table so records too long are rejected. You can do this by adding a computed column with a max length of 25. Use the "column transforms" clause to define it as the concatenation of the fields:

drop table t purge;

create table t (
  x int,
  y varchar2(20),
  z varchar2(20),
  cols varchar2(25)
) organization external (
  type oracle_loader
  default directory tmp
  access parameters (
    records delimited by newline
    fields terminated by " " (
      x char(27), 
      y char(27), 
      z char(27)
    )
    column transforms (
      cols from concat(x, y, z)
    )
  )
  location ('emps.dat')
) reject limit unlimited;

select * from t;

         X Y                    Z                    COLS                    
---------- -------------------- -------------------- -------------------------
     12345 MANOJ                JADHAV               12345MANOJJADHAV         

and you rated our response

  (2 ratings)

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

Reviews

Thanks for your help

June 24, 2016 - 1:46 pm UTC

Reviewer: MANOJ JADHAV from india

Hi Chris,

Thanks for your reply, it helped. But i am referring to a fixed width file, and there is no space delimiter between two records. Blank space is part of the string. Also i need to skip those records before loading that to external table.

Really appreciate for your help.

Thanks,
Manoj
Chris Saxon

Followup  

June 24, 2016 - 2:58 pm UTC

Thanks

"Also i need to skip those records before loading that to external table. "

You need to skip which records?

Need to skip records which are not of required length

June 28, 2016 - 9:17 am UTC

Reviewer: MANOJ JADHAV from India

Hi,

If i am expecting records of length 1000 character. Then i want to reject all records which does not match this length.

i.e. If record length is <=999 or record lenght>=1001 i need to exclude all those records before loading.

Thanks,
Manoj
Chris Saxon

Followup  

June 29, 2016 - 3:23 am UTC

An easy way would be with a pre-processor script for your external table. For example, awk script:

awk '{if ( length($0) == 1000 ) {print}}'

Hope this helps.