Home » RDBMS Server » Server Utilities » External table
External table [message #356636] Fri, 31 October 2008 11:13 Go to next message
Messages: 18
Registered: August 2005
Junior Member

Is it possible to have columns in an external table which have no corresponding data in the source(text)file but we insert some default data into such columns. let me explain in detail...

Source file has fixed length data described as
record_type position(1:2),
client_code position(3:6),
zip_code position(10:14),
zip_outlet position(15:17),
outlet_na position(19:30),
outlet_st position(31:32),
sublet_code position(33:34),
txn_code position(35:35) and looks like following,
CL060 07001123 cvspharmacy NJPRx
CL060 10040456 walgreens NJGHA
CL060 20054678 pathmark MAPRD
CL060 07932911 savondrugs CADSI
CL060 07932765 rauss CASDR where as I need to create external table with columns,
(record_type char(2),
client_code varchar2(4),
zip_code varchar2(5),
zip_outlet varchar2(3),
outlet_na varchar2(12),
outlet_st varchar2(2),
sublet_code varchar2(2),
txn_code varchar2(1),
add_dt date sysdate
Here add_dt is the extra column in external table where I need to insert sysdate.
Please suggest me.

Re: External table [message #356638 is a reply to message #356636] Fri, 31 October 2008 11:33 Go to previous message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An external table is just a view on your file.
It does not matter which columns are there, the query you will use on it will define the output columns.

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.
Use the "Preview Message" button to verify.

Previous Topic: sqlldr - Skip header in all Infiles
Next Topic: export a database which has no entry in tnsnames.ora
Goto Forum:

Current Time: Fri Oct 21 16:07:55 CDT 2016

Total time taken to generate the page: 0.12980 seconds