load csv file into oracle table

CREATE OR REPLACE PROCEDURE p_import_csv (
  p_FileDir  IN VARCHAR2,
  p_FileName IN VARCHAR2,
  p_TotalInserted IN OUT NUMBER
  ) AS

  v_FileHandle                               UTL_FILE.FILE_TYPE;
  v_NewLine                                  VARCHAR2(100);  -- Input line
  v_empid                                    i_importtimeattendance.employeeid%TYPE;
  v_timeentry                                i_importtimeattendance.timeentry%TYPE;
  v_date                                     i_importtimeattendance.datetimesheet%TYPE;
  v_remarks                                  i_importtimeattendance.remarks%TYPE;
  v_dateofwork                               i_importtimeattendance.date_of_work%TYPE;
  v_FirstComma                               NUMBER;
  v_SecondComma                              NUMBER;
  v_ThirdComma                               NUMBER;
  v_FourthComma                              NUMBER;
  veer                                       VARCHAR2(500);
  vdept                                      VARCHAR2(50);  

BEGIN
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, '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);
    v_ThirdComma    := INSTR(v_NewLine, ',', 1, 3);
    v_FourthComma   := INSTR(v_NewLine, ',', 1, 4);
   
    v_empid         := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
    v_date          := SUBSTR(v_NewLine, v_FirstComma + 1,v_SecondComma-1-v_FirstComma);
    v_timeentry     := SUBSTR(v_NewLine, v_SecondComma + 1,v_ThirdComma - v_SecondComma - 1);
    v_remarks       := SUBSTR(v_NewLine,v_ThirdComma+1,v_FourthComma - v_ThirdComma - 1);
    v_dateofwork    := SUBSTR(v_NewLine,v_FourthComma+1);
   
    SELECT dept_code
      INTO vdept
      FROM M_Department
     WHERE M_Department_ID = (SELECT M_Department_ID
                                FROM M_Employee_Master
                               WHERE value = v_empid);
    INSERT INTO i_importtimeattendance (i_importtimeattendance_id,ad_client_id,ad_org_id,employeeid, datetimesheet, timeentry,remarks,
                                        i_isimported,processed,ischk,isactive,dept_name,date_of_work
                                        )
         VALUES (ad_sequence_nextno('I_ImportTimeAttendance'),1000133,1000305,v_empid, v_date, v_timeentry,v_remarks,'N','N','N','Y',vdept,
                v_dateofwork
                );

    p_TotalInserted := p_TotalInserted + 1;
  END LOOP;
 
  UTL_FILE.FCLOSE(v_FileHandle);
 -- UTL_FILE.FRENAME ('UTL_DIR',p_FileName,'UTL_DIR',REPLACE (p_FileName,substr(p_FileName,-4,4),TO_CHAR (SYSDATE, 'DD-MON-RR')) || '.csv'); 
 
  COMMIT;
EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20051, 'Loading Failed: Invalid Operation');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20052, 'Loading Failed: Invalid File Handle');
  WHEN UTL_FILE.READ_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20053, 'Loading Failed: Read Error');
  WHEN UTL_FILE.INVALID_PATH THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20054, 'Loading Failed: Invalid Path');
  WHEN UTL_FILE.INVALID_MODE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20055, 'Loading Failed: Invalid Mode');
  WHEN UTL_FILE.INTERNAL_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20056, 'Loading Failed: Internal Error');
  WHEN VALUE_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20057, 'Loading Failed: Value Error');
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    --RAISE;
    veer:=sqlerrm;
END p_import_csv;
/




DECLARE
      v NUMBER;
    BEGIN
          p_import_csv('UTL_DIR','1749.CSV',v);   
   END;
   /
  

DataBase Daily Backup

SET NAME =RAQFIN_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%
MD RAQFINBKP\RAQFIN_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%
SET AA=RAQFINBKP
EXP raqfin_080512/raqfin_080512@orcl FILE = D:\%AA%\RAQFIN_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%\RAQFIN_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%

ORA-01036: illegal variable name/number

ORA-01036: illegal variable name/number

Cause: Unable to find bind context on user side


Action: Make sure that the variable being bound is in the sql statement.

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

ORA-01035: ORACLE only available to users with RESTRICTED SESSION  privilege

Cause: Logins are disallowed because an instance started in restricted mode. Only  users with RESTRICTED SESSION system privilege can log on.
Action: Request that Oracle be restarted without the restricted option or obtain  the RESTRICTED SESSION system privilege.

ORA-01034: ORACLE not available

ORA-01034: ORACLE not available

Cause: Oracle was not started up. Possible causes include the following:


Action: Refer to accompanying messages for possible causes and correct the  problem mentioned in the other messages. If Oracle has been initialized, then on  some operating systems, verify that Oracle was linked correctly. See the platform  specific Oracle documentation.

ORA-01033: ORACLE initialization or shutdown in progress

ORA-01033: ORACLE initialization or shutdown in progress

Cause: An attempt was made to log on while Oracle is being started up or  shutdown.


Action: Wait a few minutes. Then retry the operation.

ORA-01032: no such userid

ORA-01032: no such userid

Cause: This is an internal error message related to Export/Import.


Action: Contact customer support.