read text file and insert into db

What does the file look like (can you post a sample)?
Are all the lines the same or do you need to skip a few before you start loading?

The approach you've chosen gets a line at a time.  You will then have to parse the line into individual fields.

There are much better ways to load this data.  Probably the best in newer versions of Oracle is External Tables.  The old way is SQL*Loader.

456    1computer science
457    1english
458    1information management
459    1mathematics
460    1biology



first column is student_id (until position 1-7)
second column is enroll_status(position 8)
third column is major ( position 9-60)

what if i want to use the above your code inside pl/sql, how can i apply?

In fact, baed on your code, everything is working fine.

>>what if i want to use the above your code inside pl/sql,

I'm not sure what you mean.  I'm going to assume this is going to be part of some nightly batch pocess.

If you want a stored procedure to load the data then just put that insert statement in the code.  The external table will always exist.  As long as you keep the same file name in the same format, you won't have a problem.

The only possible problem is that you will need something to verify the record counts for the load.  If something messes up, you'll need to check the log and/or discard files to see what happened.

Here's an example procedure (untested, I just typed it in).
note:  I assume you can retrieve the number that's supposed to be in the file which is why I have a parameter on the sample proc.  Pass that in and check with what's in the external table.
--------------------------
create or replace procedure load_data(numberExpected in number)
is
     numberReceived number;
begin
     -- do some checking
     select count(*) into numberReceived from test_external;
     if numberReceived <> numberExpected then
         dbms_output.put_line('Numbers don''t match');
    else
         INSERT INTO TEST (student_id,enroll_status,major) ( select student_id,enroll_status,major from test_external );
        commit;
    end if;
end;
/

my logic is like
1. create external table
2. insert data into db table
3. drop external table
4. delete db table

using schedule jobs, can it run xxxxx.sql?
OR
is it possible to create table using pl/sql?
DECLARE
BEGIN
END;

There's really no need to create and drop the external table.

I'm afraid I'll need a little more information.  Are you talking internal database job or something like cron on UNIX?

I would really look to something at the O/S level for this as a scheduled job.  Otherwise you have no good way to verify all the records in the file were loaded.

From the statement you wrote in your PL/SQL, I assume that :
student_id is varchar2(7),
enroll_status varchar2(8),
major varchar2(9);

Just try to modify the pl/sql script as follow;


DECLARE
   f utl_file.file_type;
   st_id    varchar2(7);
   en_st  varchar2(8);
   maj        varchar2(9);
   vchTemp  varchar2(24);

BEGIN

f := UTL_FILE.fopen ('TESTDIR', 'test.txt', 'r');
LOOP
utl_file.get_line(f,vchTemp, 24);
dbms_output.put_line(vchTemp);
st_id := substr(vchTemp, 1, 7);
dbms_output.put_line(st_id);
en_st := substr(vchTemp, 8, 8);
dbms_output.put_line(en_st);
maj := substr(vchTemp, 16, 9);
dbms_output.put_line(maj);
INSERT INTO TEST (student_id,enroll_status,major) VALUES (st_id, en_st, maj);
END LOOP;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    utl_file.fclose(f);
    commit;
  END;