Home » SQL & PL/SQL » SQL & PL/SQL » read a text file and insert into db
read a text file and insert into db [message #259387] Wed, 15 August 2007 08:08 Go to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
I have an error says "ORA-20099: Unknown UTL_TILE Error ORA-06512"

I have a file is called "test.txt"

The columns in test.txt consis of student_id ( 1-7), enroll_status ( 8 ) , major (9 - 38)

So, it has to read data based on the column and insert into data..

How can i insert those data into TEST table?

Test table has

id , student_id, enroll_status, major

------ code ------------

CREATE OR REPLACE PROCEDURE TEST_READ.TESTREAD
IS
vread UTL_FILE.FILE_TYPE;
vnewline VARCHAR (250);
file_seq INTEGER;
BEGIN

-- 1. student_id ( 1-7), enroll_status ( 8 ) , major (9 - 80)
vread :=
UTL_FILE.fopen ('TESTDIR', 'test.txt', 'r');

IF UTL_FILE.is_open (vread)
THEN
LOOP
BEGIN

utl_file.get_line(vread, vNewLine);

IF vNewLine IS NULL THEN
EXIT;
END IF;

INSERT INTO TEST
(order_id)
VALUES
(vNewLine);

END;
END LOOP;

COMMIT;
END IF;

UTL_FILE.fclose (vread);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
raise_application_error (-20051, 'Invalid Mode Parameter');
WHEN UTL_FILE.invalid_path
THEN
raise_application_error (-20052, 'Invalid File Location');
WHEN UTL_FILE.invalid_filehandle
THEN
raise_application_error (-20053, 'Invalid Filehandle');
WHEN UTL_FILE.invalid_operation
THEN
raise_application_error (-20054, 'Invalid Operation');
WHEN UTL_FILE.write_error
THEN
raise_application_error (-20057, 'Internal Error');
WHEN UTL_FILE.charsetmismatch
THEN
raise_application_error
(-20058,
'Opened With FOPEN_NCHAR But Later I/O Inconsistent'
);
WHEN UTL_FILE.file_open
THEN
raise_application_error (-20059, 'File Already Opened');
WHEN UTL_FILE.invalid_filename
THEN
raise_application_error (-20061, 'Invalid File Name');
WHEN UTL_FILE.access_denied
THEN
raise_application_error (-20062, 'File Access Denied By');
WHEN OTHERS
THEN
raise_application_error (-20099, 'Unknown UTL_FILE Error');
END TESTREAD;
Re: read a text file and insert into db [message #259389 is a reply to message #259387] Wed, 15 August 2007 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

2/
Remove/comment the exception handler, reexecute and copy and paste the execution.

3/
You don't have any condition to get out of your loop.

Regards
Michel
Re: read a text file and insert into db [message #259391 is a reply to message #259387] Wed, 15 August 2007 08:24 Go to previous messageGo to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
oracle version 10g

-------------- new code ------------------

CREATE OR REPLACE PROCEDURE TEST_READ.TESTREAD
IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
vSFile := utl_file.fopen('TESTDIR', 'test.txt','r');

IF utl_file.is_open(vSFile) THEN
LOOP
BEGIN
utl_file.get_line(vSFile,vNewLine );

--1. student_id ( 1-7), enroll_status ( 8 ) , major (9 - 80)

IF vNewLine IS NULL THEN
EXIT;
END IF;

INSERT INTO test
(student_id, enroll_status, major)
VALUES
(vNewLine);

EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
utl_file.fclose(vSFile);

EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END TESTREAD;
/

[Updated on: Wed, 15 August 2007 08:31]

Report message to a moderator

Re: read a text file and insert into db [message #259395 is a reply to message #259391] Wed, 15 August 2007 08:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please, please, please, please follow what Michel posted. It's not complex, difficult or unreasonable. Just Do It.
Re: read a text file and insert into db [message #259402 is a reply to message #259387] Wed, 15 August 2007 09:29 Go to previous messageGo to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
So far, it can open and read a file.
But if there are many data like more than 1 row, how can it display?
I think I need to use loop/..but I am not sure ...how to do..

can you do me a favor?

I have this so far
-------------------

DECLARE
f utl_file.file_type;
s varchar2(200);

BEGIN

f := UTL_FILE.fopen ('TESTDIR', 'test.txt', 'r');
utl_file.get_line(f,s,7);
dbms_output.put_line(s);
utl_file.get_line(f,s,8);
dbms_output.put_line(s);
utl_file.get_line(f,s,9);
dbms_output.put_line(s);
utl_file.fclose(f);

END;

---------------
Re: read a text file and insert into db [message #259404 is a reply to message #259387] Wed, 15 August 2007 09:33 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Have you thought about using SQL*Loader or a Global Temporary Table ?

Re: read a text file and insert into db [message #259405 is a reply to message #259402] Wed, 15 August 2007 09:34 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please, please, please, please follow what Michel posted. It's not complex, difficult or unreasonable. Just Do It.
Re: read a text file and insert into db [message #259406 is a reply to message #259405] Wed, 15 August 2007 09:35 Go to previous messageGo to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
I added LOOP then it works..
but if i want to use insert data how can i do that?

LOOP

UTL_FILE.get_line (f, s, 7);
DBMS_OUTPUT.put_line (s);
UTL_FILE.get_line (f, s, 8);
DBMS_OUTPUT.put_line (s);
UTL_FILE.get_line (f, s, 9);
DBMS_OUTPUT.put_line (s);
--INSERT INTO TEST (student_id,enroll_status,major) VALUES ( );

END LOOP;

[removed unnecessary rude piece of text, probably added by a moderator]

[Mod-edit by Frank: Please let's keep it nice, people..]

[Updated on: Thu, 16 August 2007 10:29] by Moderator

Report message to a moderator

Re: read a text file and insert into db [message #259430 is a reply to message #259406] Wed, 15 August 2007 10:46 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
It i
s was
te of ti
me writi
ng code for somet
hing, whi
ch is al
ready availa
ble.

Edit: Even unformatted code also looks same as above.

Why don't you use SQL* Loader (Or) an External table?

By
Vamsi

[Updated on: Wed, 15 August 2007 10:47]

Report message to a moderator

Re: read a text file and insert into db [message #259524 is a reply to message #259406] Wed, 15 August 2007 16:38 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please, please, please, please follow what Michel posted. It's not complex, difficult or unreasonable. Just Do It.
Smile
Previous Topic: SELECT dates into Different Columns - A better way?
Next Topic: inserting multiple email id into one column via stored procedure
Goto Forum:
  


Current Time: Mon Dec 05 23:49:54 CST 2016

Total time taken to generate the page: 0.16763 seconds