?
Solved

PL/SQL file I/O

Posted on 2003-02-21
3
Medium Priority
?
329 Views
Last Modified: 2008-03-10
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;



0
Comment
Question by:bhuvanagunuru
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
TheBeaver earned 100 total points
ID: 7995946
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
 
LVL 8

Assisted Solution

by:heskyttberg
heskyttberg earned 100 total points
ID: 7998256
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
 
LVL 5

Expert Comment

by:Tom Knowlton
ID: 9453598
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

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question