Home » SQL & PL/SQL » SQL & PL/SQL » LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL (ORACLE 10G)
LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427937] Mon, 26 October 2009 08:31 Go to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

Hi,
I want to load data from ASCII file delimited by semi colon(;) with 10 field stucture. Pls i need your help.
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427938 is a reply to message #427937] Mon, 26 October 2009 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
use sqlloader
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427941 is a reply to message #427937] Mon, 26 October 2009 08:52 Go to previous messageGo to next message
rkolli
Messages: 2
Registered: October 2009
Junior Member
you can also do with
EXTERNAL TABLE concept
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427944 is a reply to message #427938] Mon, 26 October 2009 09:14 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

i need to load the data using a pl/sql script. Sad pls do u have any ideas/can u write a sample script?
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427946 is a reply to message #427944] Mon, 26 October 2009 09:17 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why do you *need* to use PL/SQL?

That's like using a saw to hammer in a nail while there is a perfectly good hammer available.
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427965 is a reply to message #427946] Mon, 26 October 2009 09:52 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

Laughing thanks Thomas G! i know it's fastest and best to use SQL LOADER but its a given task that i need to accomplish. would be glad if you can help Smile
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427969 is a reply to message #427965] Mon, 26 October 2009 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So use EXTERNAL TABLE as already said.

Regards
Michel
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #427971 is a reply to message #427969] Mon, 26 October 2009 09:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Documentation for external tables.

If the argument against external tables is the usual "I'm not allowed to create objects", then it can't be done in PL/SQL either, since for that you would have to create a directory object, too.
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428435 is a reply to message #427971] Wed, 28 October 2009 06:21 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

thanks ThomasG, your response was quite useful but i still have a few challenges.

HERE'S THE STEPS I'VE TAKEN SO FAR:
i created a directory as below

--step1
create or replace directory
ext_tab_dir as 'c:\staff.csv';


--step2(source being my schema)
grant read on directory ext_tab_dir to source;

--step3
create table staff
(
staff_id char(5),
first_name char(15),
last_name char(15),
staff_sex char(2),
staff_status char(10),
staff_phone char(11),
staff_email char(40),
staff_address char(50),
staff_region char(6),
staff_state char(15)
);

--step4
create table staff_load
(
staff_id char(5),
first_name char(15),
last_name char(15),
staff_sex char(2),
staff_status char(10),
staff_phone char(11),
staff_email char(40),
staff_address char(50),
staff_region char(6),
staff_state char(15)
)
organization external
(type oracle_loader
DEFAULT directory ext_tab_dir
access parameters
(records delimited by ';'
fields (
staff_id char(5),
first_name char(15),
last_name char(15),
staff_sex char(2),
staff_status char(10),
staff_phone char(11),
staff_email char(40),
staff_address char(50),
staff_region char(6),
staff_state char(15)
)
)
location ('c:/staff.csv')
);


--step5
insert into staff
(
staff_id ,
first_name,
last_name ,
staff_sex ,
staff_status,
staff_phone ,
staff_email ,
staff_address,
staff_region,
staff_state
)
(
select staff_id ,
first_name,
last_name ,
staff_sex ,
staff_status,
staff_phone ,
staff_email ,
staff_address,
staff_region,
staff_state
from staff_load
);

but then this error

12:10:07 ORA-29913: error in executing ODCIEXTTABLEOPEN callout
12:10:07 ORA-29400: data cartridge error
12:10:07 KUP-04063: unable to open log file STAFF_LOAD_3412_6008.log
12:10:07 OS error The system cannot find the file specified.
12:10:07 ORA-06512: at "SYS.ORACLE_LOADER", line 19

what do u think? Confused





Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428436 is a reply to message #428435] Wed, 28 October 2009 06:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think "c:\staff.csv" is not a directory.
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428438 is a reply to message #428435] Wed, 28 October 2009 06:27 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

pls find below the asii file(raw data)
table name staff
saved as staff.csv


staff_id first_name last_name staff_sex staff_status staff_phone customer_e-mail customer_address staff_region staff_state
20 Uche Jombo f married 8023456789 uche_jombo@yahoo.com 10,Ereko street Idumota South Lagos
25 Deola Amber f single 8028483807 deola_ambr@yahoo.com 12,Bode thomas surulere South Lagos
30 Abbey Lincoln m married 8099977722 abbey01@yahoo.com 10,Garki rd Garna North Sokoto
35 Amaka Uche f married 807775552 amaka_uche@gmail.com 10,Iwenka rd Onitsha South Anambra
40 Danladi Bako m single 8023455659 dladi_bako@yahoo.com Blk 10 Yanyan road suleja North Abuja
45 Deola Bolaji m married 7037988357 deolabj@yahoo.co.uk 27,Adenugba street Ago-iwoye South Ogun
50 Bako Ibrahim m married 8022234455 ibrhim uwu@gmail.com 10,Dankuwu street Gombe North Gombe
55 Sheik Duru m single 8023465678 sheik_duru@yahoo.com 2,Sheik street Kastina North Kastina
60 Micheal Ekum m married 8033334679 mikel_ekum@yahoo.com 21,Dende street ikota South Ebonyi
65 John Smith m married 8033994679 john_smith@yahoo.com 23,Unity street ikota South Lagos
70 Kanye West m single 8032211679 kanye_west@yahoo.com 22,Association Avenue Ketu South Lagos
75 Mobolaji Anthony m married 8033332329 bolaj_@yahoo.com 21,delivery road South Ekiti
80 Mbari Jane f married 8033767779 mbari_jane@yahoo.com 21,Denger street ikota South Ebonyi
85 Daina Lawson f single 8033334679 d_lawson@yahoo.com 21b, benin city South Delta
90 Gloria Agofure f single 80322334679 gloria@yahoo.com 24,Agofure street warri South Edo
95 Paul Smart m single 8033334679 mikel_ekum@yahoo.com 21,Dende street ikota South Ebonyi
100 Bisi Afuye f married 8030894679 bisi_afuye@yahoo.com 23,customs street yaba South Lagos
150 Funmi Jato f married 8022334679 funmi_jato@yahoo.com 2,Baale street bako South Ogun
155 Funke Olalemi f single 8089334679 fun@yahoo.com 24,darewa street sango South Ogun
160 Jibola Awobutu f single 8035688679 jola_awutu@yahoo.com 21 balarabe musa street vi South Lagos
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428440 is a reply to message #428438] Wed, 28 October 2009 06:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1) Create a folder, like c:\import on the server.

2) Create the directory

create or replace directory
 ext_tab_dir as 'c:\import';


The put the staff.csv in there and use "staff.csv" as location for the external table.

[Updated on: Wed, 28 October 2009 06:34]

Report message to a moderator

Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428445 is a reply to message #428436] Wed, 28 October 2009 06:44 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

Thomas, i changed the 'c:\staff.csv' to 'ext_tab_dir'.
still getting the error

12:41:00 ORA-29913: error in executing ODCIEXTTABLEOPEN callout
12:41:00 ORA-29400: data cartridge error
12:41:00 KUP-04063: unable to open log file STAFF_LOAD_3412_6008.log
12:41:00 OS error The system cannot find the file specified.
12:41:00 ORA-06512: at "SYS.ORACLE_LOADER", line 19

Sad
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428447 is a reply to message #428438] Wed, 28 October 2009 06:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
OT warning.
All the possible useful answers have been posted and I have nothing to add except my usual rant.
I just hope that the "raw" file posted by OP does not have "real" personal information.
icon14.gif  Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428450 is a reply to message #428440] Wed, 28 October 2009 06:59 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

hey Thomas thanks a big bunch!!! its working now but loading the data in like a cartesian join giving me about 190 rows and 3 columns instead of 10 columns and about 30 rows.

whats the cause of this?
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428488 is a reply to message #428450] Wed, 28 October 2009 08:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You are confused about what a "RECORD" and a "FIELD" is it seems. Read up on it in the External Table documentation.
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428859 is a reply to message #428488] Fri, 30 October 2009 06:19 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

Hi Thomas,
Iam very sorry for persistent, now i can load the the stuff perfectly but i still hav some little issue about this.
i wrote a block of code to load a table and create it at the same time.The statement executed successfully but it contains no data.pls help out!!!!

These are the step taken so far.
NOTE...these are strings is comment /*.../*


/* CONNECT AS SYS THEN CREATE A DIRECTORY CALLED LOAD_DATA AND GRANT READ WRITE PRIVILEGE TO THE USER U WANT TO USE */

SQLPLUS SYS / AS SYSDBA

CREATE OR REPLACE DIRECTORY load_data AS 'C:\data';

GRANT READ, WRITE ON DIRECTORY LOAD_DATA TO TARGET;

/* THEN YOU CREATE YOUR EXTERNAL TABLE AS FOLLOWS IN THE PROCEDURE */



DECLARE
begin
EXECUTE IMMEDIATE 'create table load_table(
column_1 number(10),
column_2 number(10),
column_3 number(10),
column_4 number(10),
column_5 number(10),
column_6 number(10),
column_7 number(10),
column_8 number(10),
column_9 number(10),
column_10 number(10)
)
organization external(
type oracle_loader
default directory load_data
access parameters (
records delimited by newline
fields terminated by '';''
)
location (''assessment_data.csv'')
)reject limit unlimited';
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE PHASE_1_TAB
AS SELECT * FROM LOAD_TABLE';
END;
END;

this is data

309 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
308 211000002 211000005 220100009 410000013 220600012 211000006 212000002 212000001 220600012
301 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
306 210800000 211000005 220100012 410000007 220600012 211000006 212000002 212000001 220600012
307 210700002 211000005 220100010 410000006 220600012 211000006 212000002 212000001 220600012
303 210700003 211000005 220100011 410000008 220600012 211000006 212000002 212000001 220600012
310 210700001 211000005 220100010 410000006 220600012 211000006 212000002 212000001 220600012
304 210800000 211000005 220100012 410000007 220600012 211000006 212000002 212000001 220600012
305 210800000 211000005 220100012 410000009 220600012 211000006 212000002 212000001 220600012
302 210700001 211000005 220100010 410000006 220600012 211000006 212000002 212000001 220600012
311 211000011 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
316 211000015 211000005 220100009 410000039 220600012 211000006 220100033 212000001 220600012
312 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
313 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
314 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
317 211000015 211000005 220100009 410000039 220600012 211000006 220100033 212000001 220600012
315 211000015 211000005 220100009 410000039 220600012 211000006 220100033 212000001 220600012
309 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
308 211000002 211000005 220100009 410000013 220600012 211000006 212000002 212000001 220600012
301 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
306 210800000 211000005 220100012 410000007 220600012 211000006 212000002 212000001 220600012
307 210700002 211000005 220100010 410000006 220600012 211000006 212000002 212000001 220600012
303 210700003 211000005 220100011 410000008 220600012 211000006 212000002 212000001 220600012
310 210700001 211000005 220100010 410000006 220600012 211000006 212000002 212000001 220600012
304 210800000 211000005 220100012 410000007 220600012 211000006 212000002 212000001 220600012
305 210800000 211000005 220100012 410000009 220600012 211000006 212000002 212000001 220600012
302 210700001 211000005 220100010 410000006 220600012 211000006 212000002 212000001 220600012
311 211000011 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
316 211000015 211000005 220100009 410000039 220600012 211000006 220100033 212000001 220600012
312 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
313 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
314 211000002 211000005 220100009 410000010 220600012 211000006 212000002 212000001 220600012
317 211000015 211000005 220100009 410000039 220600012 211000006 220100033 212000001 220600012
315 211000015 211000005 220100009 410000039 220600012 211000006 220100033 212000001 220600012
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428861 is a reply to message #428859] Fri, 30 October 2009 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
fields terminated by '';''


There is not true for the data you posted.

Regards
Michel

[Updated on: Fri, 30 October 2009 06:28]

Report message to a moderator

Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428865 is a reply to message #428861] Fri, 30 October 2009 06:49 Go to previous messageGo to next message
folarin taiwo
Messages: 9
Registered: October 2009
Location: Nigeria
Junior Member

Hi Michet,
i don't understand what you mean?
Are saying that my data is not the same as the one i sent previous time, if that is what you 're saying i guess you are right but all the same,the both datas are targeted to achieve the same the purpose. pls help out.
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428868 is a reply to message #428865] Fri, 30 October 2009 07:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There isn't a single ";" in your data, so how can the fields be terminated by ";" ?
Re: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL [message #428879 is a reply to message #428865] Fri, 30 October 2009 08:22 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
folarin taiwo wrote on Fri, 30 October 2009 12:49
Hi Michet,
i don't understand what you mean?
Are saying that my data is not the same as the one i sent previous time, if that is what you 're saying i guess you are right but all the same,the both datas are targeted to achieve the same the purpose. pls help out.

You tell Oracle that your fields are terminated by ";" and in your data the fields (columns, value...) are not terminated by ";".

Regards
Michel

Previous Topic: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target
Next Topic: Rounding off
Goto Forum:
  


Current Time: Fri Dec 02 18:20:46 CST 2016

Total time taken to generate the page: 0.23697 seconds