OraFAQ Forum: SQL & PL/SQL » Oracle Stored Procedure to Load Flat File Data

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Stored Procedure to Load Flat File Data (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Stored Procedure to Load Flat File Data [message #599416]

Thu, 24 October 2013 07:47

developer12
Messages: 88
Registered: July 2013

Member

Hi,
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table.
I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
Can anyone help me with the gudidelines for creating such a SP or any templates available for such?

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599417 is a reply to message #599416]

Thu, 24 October 2013 07:54

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

Have a look at

- utl_file (for files on the server side)

- SQL*Loader (for client side files, and therefore in this case, you don't use PL/SQL and is faster)

[Updated on: Thu, 24 October 2013 07:56]

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599418 is a reply to message #599416]

Thu, 24 October 2013 07:57

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

An external table?
http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CBBBCHFC

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599419 is a reply to message #599417]

Thu, 24 October 2013 07:59

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Also, for server side, look into EXTERNAL TABLES: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#CJAFHIDJ

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599420 is a reply to message #599419]

Thu, 24 October 2013 08:11

developer12
Messages: 88
Registered: July 2013

Member

Can the external table concept be used on a daily basis to load data into a single Oracle table everyday?? Like in case of a Daily Job running?

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599423 is a reply to message #599420]

Thu, 24 October 2013 08:15

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Yes

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599425 is a reply to message #599420]

Thu, 24 October 2013 08:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

developer12 wrote on Thu, 24 October 2013 06:11

Can the external table concept be used on a daily basis to load data into a single Oracle table everyday?? Like in case of a Daily Job running?

yes, with properly coded procedure or script

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599430 is a reply to message #599425]

Thu, 24 October 2013 08:50

Amine
Messages: 375
Registered: March 2010

Senior Member

Take also a look at this

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599432 is a reply to message #599416]

Thu, 24 October 2013 09:06

developer12
Messages: 88
Registered: July 2013

Member

Can we read from a Local flat file onto an external Oracle table??

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599434 is a reply to message #599432]

Thu, 24 October 2013 09:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

developer12 wrote on Thu, 24 October 2013 07:06

Can we read from a Local flat file onto an external Oracle table??

what problem are you actually trying to solve?

what does above mean?

External Table is only an OS file that has been "mapped" into Oracle accessible data space.

who is we?

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599436 is a reply to message #599430]

Thu, 24 October 2013 09:30

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi Frind,

First create the External table and u can select the file for pointing to .csv/.dat/.txt etc using command:


alter table 
external_table_name 
location(<Mention the filename for .csv/.dat/.txt etc >)

Then you will get data into external table and you can insert in any required table.

[Updated on: Thu, 24 October 2013 09:31]

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599438 is a reply to message #599436]

Thu, 24 October 2013 09:42

developer12
Messages: 88
Registered: July 2013

Member

Let me explain what I was looking out for:
Say, Oracle Database server is installed in Unix Box A.
Now, the flat file to be uploaded using the external table has to be in the Oracle directory structure of the Unix Box
or it can be located in a Local client machine also??

Report message to a moderator

Re: Oracle Stored Procedure to Load Flat File Data [message #599440 is a reply to message #599438]

Thu, 24 October 2013 09:48

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

On the server only (or mounted on the server).

Report message to a moderator

Previous Topic:	Unique null and multiple non-null
Next Topic:	get number of seconds between two DATE (min and max) and calculate trans/sec

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 04:47:26 CDT 2024