Home » SQL & PL/SQL » SQL & PL/SQL » External table (oracle10g)
External table [message #429140] Mon, 02 November 2009 05:35 Go to next message
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 #429141 is a reply to message #429140] Mon, 02 November 2009 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create an external table for each type of data you can have.

Regards
Michel
Re: External table [message #429143 is a reply to message #429140] Mon, 02 November 2009 05:43 Go to previous messageGo to next message
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]

Report message to a moderator

Re: External table [message #429150 is a reply to message #429143] Mon, 02 November 2009 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
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 #429179 is a reply to message #429171] Mon, 02 November 2009 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
fields terminated by x'10'

Regards
Michel
Re: External table [message #429494 is a reply to message #429140] Wed, 04 November 2009 04:08 Go to previous messageGo to next message
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 #429495 is a reply to message #429494] Wed, 04 November 2009 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Select * from <table> where rownum <= 4
Re: External table [message #429516 is a reply to message #429140] Wed, 04 November 2009 05:15 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I need only 4 rows from the file to be loaded in my table through external table concept.
Re: External table [message #429521 is a reply to message #429516] Wed, 04 November 2009 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And my answer doesn't work because.....
Re: External table [message #429584 is a reply to message #429521] Wed, 04 November 2009 12:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Wed, 04 November 2009 12:34
And my answer doesn't work because.....

Ha!
Your query did not return the first four rows...
Razz
Re: External table [message #429731 is a reply to message #429584] Thu, 05 November 2009 08:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
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.
Re: External table [message #429734 is a reply to message #429731] Thu, 05 November 2009 09:20 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Adding a column to the external table with the RECNUM parameter ?
Re: External table [message #429789 is a reply to message #429140] Thu, 05 November 2009 16:17 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Do you mean

ped·ant·ry (pdn-tr)
n. pl. ped·ant·ries
1. Pedantic attention to detail or rules.
2. An instance of pedantic behavior.
3. The habit of mind or manner characteristic of a pedant.

For all, please note the above is one really crappy defintion. How many ways can one say PEDANTRY definition=PEDANTRY.

free online dictionary

Gauntlets of Pedantry... hehehe... I am going to start using that at work.

Here is a better definition although it too is lacking since it may require additional definition to comprehend:
pedantry definition ped·antry (ped′'n trē)

noun pl. pedantries -·ries

1.the qualities, practices, etc. of a pedant; ostentatious display of knowledge, or an instance of this
2.an arbitrary adherence to rules and forms
Kevin
Previous Topic: execute procedure after database startup.
Next Topic: PL/SQL writing log during PL/SQL execution
Goto Forum:
  


Current Time: Thu Dec 08 22:10:38 CST 2016

Total time taken to generate the page: 0.23021 seconds