ORA-20099: Unknown UTL_FILE Error
986379Mar 29 2013 — edited Mar 29 2013hi experts,
my req is read and insert into table but that time i got error ORA-20099: Unknown UTL_FILE Error how to resolve.
DECLARE
V_LINE VARCHAR2(2000); -- Data line read from input file
V_FILE UTL_FILE.FILE_TYPE;
V_DIR VARCHAR2(250);
V_FILENAME VARCHAR2(50);
V_1ST_COMMA NUMBER;
V_2ND_COMMA NUMBER;
V_3RD_COMMA NUMBER;
V_4TH_COMMA NUMBER;
V_5TH_COMMA NUMBER;
V_6TH_COMMA NUMBER;
V_7TH_COMMA NUMBER;
V_8TH_COMMA NUMBER;
V_9TH_COMMA NUMBER;
V_10TH_COMMA NUMBER;
V_11TH_COMMA NUMBER;
V_CODE VARCHAR(30);
V_STATUS CHAR;
V_SHORT_DESCRIPTION VARCHAR2(100);
V_LONG_DESCRIPTION VARCHAR2(100);
V_FULL_DESCRIPTION VARCHAR2(100);
V_CODE_EFFECTIVE_DATE DATE;
V_CHANGE_EFFECTIVE_DATE DATE;
V_TERMINATION_DATE DATE;
V_RELEASE_DATE DATE;
V_CREATION_DATE DATE;
V_VALIDITY CHAR;
BEGIN
V_DIR :='MID5010_DOC1TP';
V_FILENAME := 'OPT_ICD10CM_BASE.TXT';
V_FILE := UTL_FILE.FOPEN(V_DIR, V_FILENAME, 'r');
LOOP
BEGIN
UTL_FILE.GET_LINE(V_FILE, V_LINE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
V_1ST_COMMA := INSTR(V_LINE, ',' ,1 , 1);
V_2ND_COMMA := INSTR(V_LINE, ',' ,1 , 2);
V_3RD_COMMA := INSTR(V_LINE, ',' ,1 , 3);
V_4TH_COMMA := INSTR(V_LINE, ',' ,1 , 4);
V_5TH_COMMA := INSTR(V_LINE, ',' ,1 , 5);
V_6TH_COMMA := INSTR(V_LINE, ',' ,1 , 6);
V_7TH_COMMA := INSTR(V_LINE, ',' ,1 , 7);
V_8TH_COMMA := INSTR(V_LINE, ',' ,1 , 8);
V_9TH_COMMA := INSTR(V_LINE, ',' ,1 , 9);
V_10TH_COMMA := INSTR(V_LINE, ',' ,1 , 10);
V_5TH_COMMA := INSTR(V_LINE, ',' ,1 , 11);
V_CODE := TO_NUMBER(SUBSTR(V_LINE, 1, V_1ST_COMMA-1));
V_STATUS := SUBSTR(V_LINE, V_1ST_COMMA+1, V_2ND_COMMA-V_1ST_COMMA-1);
V_SHORT_DESCRIPTION := SUBSTR(V_LINE, V_2ND_COMMA+1, V_3RD_COMMA-V_2ND_COMMA-1);
V_LONG_DESCRIPTION := SUBSTR(V_LINE, V_3RD_COMMA+1, V_4TH_COMMA-V_3RD_COMMA-1);
V_FULL_DESCRIPTION := SUBSTR(V_LINE, V_4TH_COMMA+1, V_5TH_COMMA-V_4TH_COMMA-1);
V_CODE_EFFECTIVE_DATE := TO_DATE(SUBSTR(V_LINE, V_5TH_COMMA+1, V_6TH_COMMA-V_5TH_COMMA-1),'DD-MON-YYYY');
V_CHANGE_EFFECTIVE_DATE := TO_DATE(SUBSTR(V_LINE, V_6TH_COMMA+1, V_7TH_COMMA-V_6TH_COMMA-1),'DD-MON-YYYY');
V_TERMINATION_DATE := TO_DATE(SUBSTR(V_LINE, V_7TH_COMMA+1, V_8TH_COMMA-V_7TH_COMMA-1),'DD-MON-YYYY');
V_RELEASE_DATE := TO_DATE(SUBSTR(V_LINE, V_8TH_COMMA+1, V_9TH_COMMA-V_8TH_COMMA-1),'DD-MON-YYYY');
V_CREATION_DATE := TO_DATE(SUBSTR(V_LINE, V_9TH_COMMA+1, V_10TH_COMMA-V_9TH_COMMA-1),'DD-MON-YYYY');
V_VALIDITY := SUBSTR(V_LINE, V_10TH_COMMA+1, V_11TH_COMMA-V_10TH_COMMA-1);
----DBMS_OUTPUT.PUT_LINE(V_EMPNO ||' '|| V_ENAME || ' ' || V_JOB || ' ' || V_MGR ||' ' || V_HIREDATE);
INSERT INTO OPTUM_ICD10CM_BASE
VALUES (V_CODE,
V_STATUS,
V_SHORT_DESCRIPTION,
V_LONG_DESCRIPTION,
V_FULL_DESCRIPTION,
V_CODE_EFFECTIVE_DATE,
V_CHANGE_EFFECTIVE_DATE,
V_TERMINATION_DATE,
V_RELEASE_DATE,
V_CREATION_DATE,
V_VALIDITY);
END LOOP;
UTL_FILE.FCLOSE(V_FILE);
EXCEPTION
when UTL_FILE.INVALID_PATH then
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
when UTL_FILE.INVALID_FILEHANDLE then
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
when UTL_FILE.READ_ERROR then
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
when UTL_FILE.INTERNAL_ERROR then
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
when UTL_FILE.FILE_OPEN then
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
when UTL_FILE.INVALID_MAXLINESIZE then
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
when UTL_FILE.INVALID_FILENAME then
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END;