Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

How convert a flat file to Oracle with .ksh script ?

I'd like to convert a flat file to insert/update record in Oracle base using a script .sh file ?

Thanks in advance.

1 Solution
Bruce CadizQuality SpecialistCommented:
Do you have sqlloader?
what is the file format?
Mark GeerlingsDatabase AdministratorCommented:
If the flat file is just ASCII text data and it is either in fixed-length format, or delimitted, and if you have a table in Oracle you want the data loaded into, then Oracle's SQL*Loader is the tool to use.  You will need to write a control file for SQL*Loader (to map the fields in the text file to columns in the table) then you can write a shell script to call SQL*Loader and use the control file you wrote.

If the data is not clean or straitforward enough for SQL*Loader to handle directly into your target table, you will have to either laod into into a "work" table, then use SQL and/or PL\SQL to clean it up, then move it into your target table.  Or you can write a PL\SQL procedure that uses UTL_FILE to read the text file, clean up, transform or manipulate each record as needed, then insert it.

Either way, the table must exist in Oracle first.  Oracle does not provide a tool to create a table based on a plan text data file.
SqlLoader is normally the best bet for this type of load. However, if your flat file is not structured to suit, or requires a fair bit of manipulation, then I tend to use Perl rather than PL/SQL which tends to be a bit clumsy (or maybe thats just the way I write code!)


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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