Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-20099: Unknown UTL_FILE Error

986379Mar 29 2013 — edited Mar 29 2013
hi 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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2013
Added on Mar 29 2013
6 comments
2,375 views