PL/SQL file I/O

I've written a procedure which extract the data from the flat file which is a comma delimiter and import it to the table. I've used UTL_FILE package. The procedure is accepting 2 inputs i.e., FILEDIR AND FILE NAME.

After executing the preocedure, the values from the file are not imported into the table.

Please go through the source code which is below and correct the code if it is wrong or correct me if i am giving the input values wrong.

For input FILEDIR i gave c:\
For input FILENAME I gave student_info.csv

following is my PL/SQL code
 
CREATE OR REPLACE PROCEDURE LOAD_STUDENT(vdFILEDIR IN VARCHAR2,vnFILENAME IN VARCHAR2) AS

vnFILEHANDLE UTL_FILE.FILE_TYPE;

vnNEWLINE VARCHAR2(100);

vtFIRST_NAME STUDENT.FIRST_NAME%TYPE;
vtLAST_NAME STUDENT.LAST_NAME%TYPE;
vtSUBJECT STUDENT.SUBJECT%TYPE;

/* POSITIONS OF COMMAS WITHIN INPUT LINE*/

vnFIRSTCOMMA NUMBER;
vnSECONDCOMMA NUMBER;  

BEGIN
DBMS_OUTPUT.PUT_LINE(vdFILEDIR);
DBMS_OUTPUT.PUT_LINE(vnFILENAME);

vnFILEHANDLE:= UTL_FILE.FOPEN(vdFILEDIR,vnFILENAME,'R');


LOOP

DBMS_OUTPUT.PUT_LINE('ENTER LOOP');

BEGIN
UTL_FILE.GET_LINE(vnFILEHANDLE,vnNEWLINE);
EXCEPTION
           WHEN NO_DATA_FOUND THEN
           EXIT;
END;

vnFIRSTCOMMA :=INSTR(vnNEWLINE,',',1,1);
vnSECONDCOMMA:=INSTR(vnNEWLINE,',',1,2);

vtFIRST_NAME := SUBSTR(vnNEWLINE,1,vnFIRSTCOMMA-1);
vtLAST_NAME := SUBSTR(vnNEWLINE,vnFIRSTCOMMA+1,vnSECONDCOMMA-vnFIRSTCOMMA-1);
vtSUBJECT := SUBSTR(vnNEWLINE,vnFIRSTCOMMA+1);      

INSERT INTO STUDENT (ID,
   FIRST_NAME, LAST_NAME, SUBJECT)
VALUES (STUDENT_SEQUENCE.NEXTVAL,vtFIRST_NAME,vtLAST_NAME,vtSUBJECT);

END LOOP;
UTL_FILE.FCLOSE(vnFILEHANDLE);


COMMIT;

   EXCEPTION
     WHEN UTL_FILE.INVALID_OPERATION THEN
             UTL_FILE.FCLOSE(vnFILEHANDLE);
            RAISE_APPLICATION_ERROR(-20051,'LOAD_STUDENT: INVALID OPERATION');
     WHEN UTL_FILE.INVALID_FILEHANDLE THEN
             UTL_FILE.FCLOSE(vnFILEHANDLE);
            RAISE_APPLICATION_ERROR(-20052,'LOAD_STUDENT: INVALID FILE HANDLE');
      WHEN UTL_FILE.READ_ERROR THEN
             UTL_FILE.FCLOSE(vnFILEHANDLE);
            RAISE_APPLICATION_ERROR(-20053,'LOAD_STUDENT: READ ERROR');
     WHEN OTHERS THEN
          UTL_FILE.FCLOSE(vnFILEHANDLE);


END LOAD_STUDENT;



bhuvanagunuruAsked:
Who is Participating?
 
TheBeaverConnect With a Mentor Commented:
vtSUBJECT := SUBSTR(vnNEWLINE,vnFIRSTCOMMA+1);      
...should probably be...
vtSUBJECT := SUBSTR(vnNEWLINE,vnSECONDCOMMA+1);      

You should peobably set a record size as well, ie...
vnFILEHANDLE:= UTL_FILE.FOPEN(vdFILEDIR,vnFILENAME,'R', 100);

Otherwise your records may be overflowing vnNEWLINE
0
 
heskyttbergConnect With a Mentor Commented:
Hi!

Read up abit on sqlldr and try to use that instead, much quicker and easier once you understand how to use it.

Regards
/Hans - Erik Skyttberg
0
 
Tom KnowltonWeb developerCommented:
bhuvanagunuru,
No comment has been added lately (219 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: split points between TheBeaver http:#7995946 and heskyttberg http:#7998256

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

knowlton
EE Cleanup Volunteer
0
All Courses

From novice to tech pro — start learning today.