Home » Developer & Programmer » Forms » how to insert records from a text file into a table
how to insert records from a text file into a table [message #177951] Sun, 18 June 2006 07:49 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii all

I have a text file that contains a records of a table and are semiconom delemited

that text file is in that form:

10;emad
20;pero
30;manoon
40;fady


i want to insert these records to a table of that description

Quote:

create table try (
code number,
name varchar2(10));



Thanks for everyone Helped me and helping me

Re: how to insert records from a text file into a table [message #177965 is a reply to message #177951] Sun, 18 June 2006 12:53 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Two simple ways would be SQL*Loader and External table.
Re: how to insert records from a text file into a table [message #177983 is a reply to message #177965] Sun, 18 June 2006 21:39 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Or use utl_file to read the file.

David
Re: how to insert records from a text file into a table [message #178011 is a reply to message #177951] Mon, 19 June 2006 01:21 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

i used the "text_io" function in this issue

Quote:

DECLARE
IN_FILE TEXT_IO.FILE_TYPE;
INS_PATH VARCHAR2(50):= 'D:\REPORT.txt' ;
vCUSTOMER CHAR(200);

BEGIN

IN_FILE :=TEXT_IO.FOPEN(INS_PATH,'R');
TEXT_IO.GET_LINE(OUT_FILE,vCUSTOMER);

MESSAGE (vTRY);
MESSAGE (vTRY);

TEXT_IO.FCLOSE (OUT_FILE);


EXCEPTION
WHEN OTHERS THEN
TEXT_IO.FCLOSE (OUT_FILE);
MESSAGE(SQLERRM);
END;




But this code takes "lines" doesnot see the delimter "semiconom"
and also it reads the first line only
and i don't know how to loop on that text file

[Updated on: Mon, 19 June 2006 01:23]

Report message to a moderator

Re: how to insert records from a text file into a table [message #178188 is a reply to message #177951] Tue, 20 June 2006 01:21 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Can anyone help me

please if it is possible tell me
Re: how to insert records from a text file into a table [message #178344 is a reply to message #178188] Tue, 20 June 2006 13:02 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Loader solution:

TEST.CTL file:

load data
infile 'test.txt'
badfile 'test.bad'
discardfile 'test.dsc'
replace
into table try
fields terminated by ";"
(code, name)

> sqlldr username/password@database control=test.ctl


External table solution:
CONNECT SYSTEM/password@database

CREATE OR REPLACE DIRECTORY ext_dir AS 'c:\temp';

GRANT READ, WRITE ON DIRECTORY ext_dir TO scott;

CONNECT scott/tiger@database

CREATE TABLE EXT_TABLE_TEST 
(  code         NUMBER,
   name         VARCHAR2(10)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS 
  ( RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ';'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('test.txt')
)
REJECT LIMIT UNLIMITED
/

INSERT INTO TRY (code, name) (SELECT code, name FROM ext_table_test);


Solve the "text_io" function by yourself.
Re: how to insert records from a text file into a table [message #178348 is a reply to message #178011] Tue, 20 June 2006 13:59 Go to previous messageGo to next message
sachinjainonweb
Messages: 24
Registered: October 2005
Junior Member
to loop in text file
we can use infinite loop and once it reaches EOF no_data_found exception is raised

loop
<code to be repeated>;
end loop ;

exception
when no_data_found then
text_io.fclose(fname);
Re: how to insert records from a text file into a table [message #178365 is a reply to message #177951] Tue, 20 June 2006 16:29 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Thanks for your reply

i used this code


Quote:


TEST.CTL file:

load data
infile 'test.txt'
badfile 'test.bad'
discardfile 'test.dsc'
replace
into table try
fields terminated by ";"
(code, name)

> sqlldr username/password@database control=test.ctl




and when i do this
it gives me that error
Quote:


ERROR at line 1:
ORA-00900: invalid SQL statement



does sqlldr is a reserved word in sql
it is my first time to use this
also it is my first time to try to load data from a file

Re: how to insert records from a text file into a table [message #178366 is a reply to message #178365] Tue, 20 June 2006 16:59 Go to previous message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Loader is NOT an SQL command; it is Oracle utility and is invoked from the operating system prompt. Read more about it here.
Previous Topic: forms-mask based on stored procedure ?
Next Topic: weird Issue with Forms/Reports 10g XE
Goto Forum:
  


Current Time: Fri Sep 20 09:29:13 CDT 2024