I tried UTL_FILE.FOPEN to save file into windows network drive but getting error ORA-00972: identifier is too long

Question

In my pl/sql sprocedure, trying store cursor data into into a test file on our windows network drive. It compiles but when I run it, getting error: ORA-00972: identifier is too long

create or replace
procedure openDataTest
is
  fHandle  UTL_FILE.FILE_TYPE;
  CURSOR cur IS
     select a.key_location from XXX.YYYY a;
rec cur%ROWTYPE;
begin
  fHandle := UTL_FILE.FOPEN('windows network drive', 'test_file', 'w');

OPEN cur;
        LOOP
        FETCH cur INTO rec;
        EXIT WHEN cur%NOTFOUND;
         UTL_FILE.PUT(fHandle, rec.key_location);
        END LOOP;
        CLOSE cur;
         UTL_FILE.FCLOSE(fHandle);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
    RAISE;
end;

It compiles but when I run it, getting error:

Connecting to the database XXX dev.
ORA-00972: identifier is too long
ORA-06512: at "XXX.YYYY", line 21
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "XXX.YYYY", line 8
ORA-06512: at line 2
Exception: SQLCODE=-972  SQLERRM=ORA-00972: identifier is too long
Process exited.

I tried to write file into other network drive with shorter path but still has same error.

score 0 · Answer 1 · answered Oct 25 '22 at 21:29

UTL_FILE works on directory objects, not directly on the operating system directory path. You must create the directory object first, assign permissions to the object in the database to allow access, and then reference the directory object in your code.

Paraphrased from the documentation, here: https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS72681

SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log'; 
SQL> GRANT READ ON DIRECTORY log_dir TO YOURUSER; 
SQL> GRANT WRITE ON DIRECTORY log_dir TO YOURUSER;

Then:

DECLARE 
  V1 VARCHAR2(32767); 
  F1 UTL_FILE.FILE_TYPE; 
BEGIN 
  -- In this example MAX_LINESIZE is less than GET_LINE's length request 
  -- so the number of bytes returned will be 256 or less if a line terminator is seen. 
  F1 := UTL_FILE.FOPEN('LOG_DIR','u12345.tmp','R',256); 
  UTL_FILE.GET_LINE(F1,V1,32767); 
  UTL_FILE.FCLOSE(F1); 

  -- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024, 
  -- so the number of bytes returned will be 1024 or less if a line terminator is seen. 
  F1 := UTL_FILE.FOPEN('LOG_DIR','u12345.tmp','R'); 
  UTL_FILE.GET_LINE(F1,V1,32767); 
  UTL_FILE.FCLOSE(F1); 

  -- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to 
  -- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. 
  -- So the number of bytes returned will be 1024 or less if a line terminator is seen. 
  F1 := UTL_FILE.FOPEN('LOG_DIR','u12345.tmp','R'); 
  UTL_FILE.GET_LINE(F1,V1); 
  UTL_FILE.FCLOSE(F1); 
END;

Also note that UTL_FILE often has issues accessing network-mounted drives unless they are configured very specifically. Windows may be more forgiving than Linux, though.

I tried UTL_FILE.FOPEN to save file into windows network drive but getting error ORA-00972: identifier is too long

1 Answers1