Home » SQL & PL/SQL » SQL & PL/SQL » How to transform data of flat files while loading to external table (oracle 10g)
How to transform data of flat files while loading to external table [message #411339] Fri, 03 July 2009 02:46 Go to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,
I want to load the data from flat file to external table.
The data of flat file is shown below:


CUSTOMERID TRANSACTIONID Date ShopID TotalAmount
767016882 100 07/15/2002 100 aasskdjs
767016882 07/15/2002 fsassd
767016882 102 07/15/2002 102 asfs
103 07/15/2002 103 144735
35029568 104 07/15/2002 104 fsassd
35029568 07/15/2002 105 fsassd
533620529 106 106 25903
533620529 107 07/15/2002 107 fsassd
108 07/15/2002 108 106427
688574423 109 07/15/2002 109 fsassd
688574423 07/15/2002 fsassd
688574423 111 07/15/2002 111 fsassd
112 07/15/2002 112 164837
940695013 113 07/15/2002 113 fsassd
940695013 114 07/15/2002 114 fsassd
234132510 07/15/2002 fsassd
234132510 116 07/15/2002 116 fsassd
117 07/15/2002 117 129684
880158740 118 07/15/2002 118 fsassd
880158740 07/15/2002 fsassd
880158740 120 07/15/2002 120 fsassd
52656 121 07/15/2002 121 149075

Now,My requiremwnt is
1.Here in this file customerid is null for some columns.So i need to put some value for customerid while loading to external table.
2.The date need to be changed to oracle compatiable format.

I know how to do this in sql loader control file.But i dont know in case of external table

Please give me a solution to do these transfi have written the following script for this requirement, but it is not working

CREATE TABLE SRC_Trans_EXT
(
ORDERID NUMBER(20),
TransactionID NUMBER(2),
Date Date,
Shopid VARCHAR2(80),
Amount Varchar2(50)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
( records delimited by newline
fields terminated by '\t'
(
CUSTOMERID "NVL(CUSTOMERID,0)",
TRANSACTIONID,
DATE "to_date(:DATE,'MM/DD/YYYY'),
SHOPID,
AMOUNT
)
LOCATION (EXT_DIR:'ordernode223.txt')
)
REJECT LIMIT UNLIMITED;


Hoping for quick reply.
Thanks in Advance,
Ravindra.
Re: How to transform data of flat files while loading to external table [message #411346 is a reply to message #411339] Fri, 03 July 2009 02:59 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
.Here in this file customerid is null for some columns.So i need to put some value for customerid while loading to external table.
2.The date need to be changed to oracle compatiable format.

You do not load into an external table.
You load into a target table using an external table.
An external table is just a SQL view on your file.
You load the target table using INSERT SELECT (or MERGE), so you can do all what SQL allows you to do to transform the data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Fri, 03 July 2009 03:00]

Report message to a moderator

Previous Topic: Run/execute/compile database object
Next Topic: SQL Query
Goto Forum:
  


Current Time: Sat Dec 10 02:52:49 CST 2016

Total time taken to generate the page: 0.07850 seconds