| External table [message #429140] |
Mon, 02 November 2009 05:35  |
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI |
Member |
|
|
Hi,
I will be getting data from a flat file which is inserted into the table through External table.
I have a problem in this.
The number of columns in the flat file differs each time.
Number of columns in the external table has to be created based on this dynamically.
can anyone help me how to do this.
Thanks in advance
|
|
|
|
| Re: External table [message #429143 is a reply to message #429140] |
Mon, 02 November 2009 05:43  |
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI |
Member |
|
|
I mean External table to be build dynamically based on the flat file.
Can the flat file data(all the columns) stored in a single CLOB column through external table
Suggestion pls.
[Updated on: Mon, 02 November 2009 05:43]
|
|
|
| Re: External table [message #429150 is a reply to message #429143] |
Mon, 02 November 2009 06:02 |
Michel Cadot
Messages: 29436
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
I gave you a suggestion.
I bet you have not an infinite number of different tables.
The other solution is to have only one external table with one column of VARCHAR2(4000).
Regards
Michel
|
|
|
| Re: External table [message #429171 is a reply to message #429140] |
Mon, 02 November 2009 07:04 |
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI |
Member |
|
|
Hi
execute immediate 'CREATE TABLE TEST
(
DAT CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY TMP_DIR
ACCESS PARAMETERS
(
)
LOCATION (TMP_DIR:''XYZ.CSV'')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING';
Data in the external file
Name,age,salary
aaaa,20,3232
bbb,21,2322
In the external table only the first column data is stored ie.Name.
I need all the columns as it is in the external file to be inserted in the table
i.e as a record.
kindly advise me what code changes i have to do in the external table code to get this.
|
|
|
|
| Re: External table [message #429494 is a reply to message #429140] |
Wed, 04 November 2009 04:08 |
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI |
Member |
|
|
Hi,
Thank you for your help.
That is what i need.
can you tell me how to extract first 4 rows from the file through external table.
|
|
|
|
|
|
|
| Re: External table [message #429731 is a reply to message #429584] |
Thu, 05 November 2009 08:45 |
JRowbottom
Messages: 5371
Registered: June 2006
Location: Sunny North Yorkshire, ho... |
Senior Member |
|
|
Generally I would slap my head and go "D'oh, silly me", but I'm pretty sure that for a external table, the row order is defined by the order in the text file.
My argument is: External Tables are built on Sql*Loader, and Sql Loader simply reads the file one line at a time and processes it.
And, if I were to don my Gauntlets of Pedentry, I'd say that 'WHERE rownum <= 4' does return the first four rows of the query, but that these are not neccessarily the first four rows in the file.
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
