0

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?

  • Try with the following GRANT: Grant Read, Write on Directory log_dir to PUBLIC; – Bibhuti Bhusan Padhi Nov 22 '16 at 13:22
  • Is your DB on the same host where D:\1Deepak\Data is? or does the host has access to D:\1Deepak\Data ? – Raj Nov 22 '16 at 20:36
1

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:

v_FileHandle := UTL_FILE.FOPEN ('LOG_DIR','Lect.csv', 'r');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.