| Inserting into table using text_io [message #81958] |
Fri, 04 April 2003 00:00  |
manish mendiratta
Messages: 110
Registered: May 2002
|
Senior Member |
|
|
Hi
I wish to use text_io to insert data into table from a tab seperated text file. Uptill now i have been doing this by sqlldr now i wish to use text_io for this purpose.
Pls help in how to go about on this.
|
|
|
|
| Re: Inserting into table using text_io [message #81960 is a reply to message #81958] |
Fri, 04 April 2003 04:01  |
JAFOD
Messages: 15
Registered: February 2003
|
Junior Member |
|
|
It's pretty straightforward, you
open the text file for reading
loop -- through the text file
read a line of the text file
loop -- through the line using INSTR() to locate the TABS and SUBSTR() to load your column variables
parse the text into your column variables
INSERT a record into your table
end (line) loop
use new line to advance the file cursor
delcare an exception to close the text file when no more lines
end (text file) loop
done
|
|
|
|
| Re: Inserting into table using text_io [message #81996 is a reply to message #81958] |
Tue, 08 April 2003 09:34 |
shadow
Messages: 15
Registered: April 2003
|
Junior Member |
|
|
PROCEDURE FILTER IS
in_file TEXT_IO.FILE_TYPE;
your_file VARCHAR2(50) := 'C:RAPORLAMATEMPBEKLEYEN.TXT';
text_line VARCHAR2(400);
i number :=0;
a number;
b varchar2(10);
c number ;
BEGIN
LOOP
TEXT_IO.GET_LINE(in_file, text_line);
i:=i+1;
a:=SUBSTR(TEXT_LINE,1,18);
b:=SUBSTR(TEXT_LINE,20,20);
c:=SUBSTR(TEXT_LINE,41,5);
insert into table_name values (a,b,c)
END LOOP;
TEXT_IO.FCLOSE(in_file);
EXCEPTION
WHEN NO_DATA_FOUND THEN
TEXT_IO.FCLOSE(in_file);
commit;
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
