I am using oracle 11g and using sqldeveloper to run/edit queries.
My table is:
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
10008 A Dd Computer Science 2
10009 B DD History 3
10010 D dd Computer Science 3
10011 C ad Economics 3
10012 D Da History 3
10013 E df History 3
Above table is in CSV format (lect.csv) stored in D:\1Deepak\Data
.
I have created below procedure to import the csv file in oracle, but it’s not working getting below error :
Connecting to the database r.
ORA-29280: invalid directory path
ORA-06512: at "SYSTEM.LOADLECTURER", line 39
ORA-06512: at line 2
Process exited.
Disconnecting from the database r.
Code :
create user D identified by d
CREATE DIRECTORY log_dir AS 'D:\1Deepak\Data';
Grant all privileges to D;
Grant Read, Write on Directory log_dir to D;
CREATE TABLE lecturer (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3)
);
CREATE OR REPLACE PROCEDURE Loadlecturer
AS
p_TotalInserted number;
v_FileHandle UTL_FILE.FILE_TYPE;
v_NewLine VARCHAR2(100); -- Input line
myFirstName lecturer.first_name%TYPE;
v_LastName lecturer.last_name%TYPE;
v_Major lecturer.major%TYPE;
v_FirstComma NUMBER;
v_SecondComma NUMBER;
BEGIN
v_FileHandle := UTL_FILE.FOPEN ('Log_dir','Lect.csv', 'r');
p_TotalInserted := 0;
LOOP
BEGIN
UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
v_FirstComma := INSTR(v_NewLine, ',', 1, 1);
v_SecondComma := INSTR(v_NewLine, ',', 1, 2);
myFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1,
v_SecondComma - v_FirstComma - 1);
v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);
INSERT INTO lecturer (ID, first_name, last_name, major) VALUES (1, myFirstName, v_LastName, v_Major);
p_TotalInserted := p_TotalInserted + 1;
dbms_output.put_line(p_totalinserted);
END LOOP;
UTL_FILE.FCLOSE(v_FileHandle);
Exception
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE;
END Loadlecturer;
From where is the above mentioned error is coming?
Best Answer
I already had that problem. The thing is that Oracle saves your objects in Uppercase. When you use the FOPEN, you MUST use directory name in uppercase as well. Try this: