| utl_file [message #375058] |
Mon, 23 July 2001 03:21  |
Shukla
Messages: 5
Registered: July 2001
|
Junior Member |
|
|
hello friends,
i have a problem while usinf utl_file utility.i have created a procedure which is having following structure:
CREATE OR REPLACE PROCEDURE
LOADDATA(P_FILEDIR IN VARCHAR2,P_FILENAME IN VARCHAR2) AS
V_FILEHANDLE UTL_FILE.FILE_TYPE;
V_NEWLINE VARCHAR2(100);
V_FIRSTNAME STUDENTS.FIRST_NAME%TYPE;
V_LASTNAME STUDENTS.LAST_NAME%TYPE;
V_MAJOR STUDENTS.MAJOR%TYPE;
V_FIRSTCOMA NUMBER;
V_SECONDCOMA NUMBER;
BEGIN
V_FILEHANDLE :=UTL_FILE.FOPEN(P_FILEDIR,P_FILENAME,'R');
LOOP
BEGIN
UTL_FILE.GET_LINE(V_FILEHANDLE,V_NEWLINE);
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
END;
V_FIRSTCOMA :=INSTR(V_NEWLINE,',',1,1);
V_SECONDCOMA :=INSTR(V_NEWLINE,'1',1,2);
V_FIRSTNAME :=SUBSTR(V_NEWLINE,1,V_FIRSTCOMA-1);
V_LASTNAME :=SUBSTR(V_NEWLINE,V_FIRSTCOMA+1,V_SECONDCOMA-V_FIRSTCOMA-1);
V_MAJOR :=SUBSTR(V_NEWLINE,V_SECONDCOMA+1);
INSERT INTO STUDENTS(FIRST_NAME,LAST_NAME,MAJOR) VALUES(V_FIRSTNAME,V_LASTNAME,V_MAJOR);
END LOOP;
UTL_FILE.FCLOSE(V_FILEHANDLE);
COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END LOADDATA;
i want to run this procedure from a PC running on Windows NT workstation.
when i execute this procedure with following command :
Exec loaddata('STUDENT','STUDENT.TXT');
i get an output "ERROR plsql procedure completed sucessfully'.
i am not able to load data in table students from the file "student.txt" which is in the directory "C:\student". what command should i use to get my work done?
i dont want to use sql loader utility.
thanks
shukla
|
|
|
|
| Re: utl_file [message #375059 is a reply to message #375058] |
Mon, 23 July 2001 05:10  |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Shukla,
is this c:\student directory on the server where the oracle is running or is this a directory on you workstation? You need to create this directory on your c: of the server and the file too in that directory.
Also, check if the UTL_FILE_DIR=c:\student or UTL_FILE_DIR=* (to read/write to any directory on your server) to allow access to the filesystem.
hth
Prem :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
