Shell script to create directory via Oracle using UTL_FILE

Question

#! /bin/bash

sqlplus -s /nolog  << EOF
conn sys/password as sysdba
CREATE or replace DIRECTORY LOGDIR AS '~/log';
GRANT WRITE ON DIRECTORY LOGDIR TO scott; 

connect scott/tiger
set serveroutput on feedback off
CREATE OR REPLACE PROCEDURE SP_ORACLE_2_EXCEL(filename IN VARCHAR2) AS
    filehandle UTL_FILE.FILE_TYPE;
    filepath VARCHAR2(50);
    CURSOR C1 IS SELECT * FROM emp order by deptno;
    VARC1 C1%ROWTYPE;
BEGIN
    filepath := filename || '_' || SYSDATE || '.csv';    
    filehandle := UTL_FILE.FOPEN('LOGDIR', filepath, 'W');
    utl_file.putf(filehandle,' REPORT :GENERATED ON %s\n',SYSDATE);
    utl_file.new_line(filehandle);
    UTL_FILE.PUT_LINE(filehandle, 'EMPNO' || ',' || 'ENAME' || ',' || 'DEPTNO');
    OPEN C1;
    LOOP
        FETCH C1 INTO VARC1;
        EXIT WHEN C1%NOTFOUND;
        UTL_FILE.PUT_LINE(filehandle, '"' || VARC1.empno || '"' || ' ,' || '"' ||
            VARC1.ename || '"' || ' ,' || '"' || VARC1.deptno|| '"');
    END LOOP;
    close C1;
    UTL_FILE.FFLUSH(filehandle);
    UTL_FILE.FCLOSE(filehandle);
EXCEPTION
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END SP_ORACLE_2_EXCEL;
/
execute SP_ORACLE_2_EXCEL('test')
quit
EOF

When I run this script I get the below output saying the directory is successfully created. However the directory is not created when I verified my home directory, even though the message confirms the same.

Directory created.

Grant succeeded.

-29283,ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line
536
ORA-29283: invalid file operation

I have gone through the solution given in the UTL_FILE.FOPEN() procedure not accepting path for directory?, but still I am not sure how to correct this?

Directory here is Oracle's directory. You can verify the same from dba_directories or all_directories view. Don;t confuse with Unix Directory. You need to create the unix directory using mkdir command and then you need to place the file into this unix directory manully to perform ULT_FILE operations. — San, Jan 13 '14 at 12:47
You need to place the file if you have to perform read operation, but in you example you want to write a new file, in that case no need to place any file, just create Unix directory manually. — San, Jan 13 '14 at 12:56
@San so you mean to say there is no need for `CREATE or replace DIRECTORY LOGDIR AS '~/log'; GRANT WRITE ON DIRECTORY LOGDIR TO scott;` ? — Ibrahim Quraish, Jan 15 '14 at 06:39
Not like that, Oracle directory `LOGDIR` is a name given to '~/log' folder. Now, your operating system identifies this log folder by name '~/log', but Oracle identifies the same folder by `LOGDIR` name. So, create a folder `log` in your home directory, and then tell oracle that you can identify `~log` location as `LOGDIR` by creating a Oracle directory using `CREATE or replace DIRECTORY LOGDIR AS '~/log'`. Both are required. — San, Jan 15 '14 at 07:04

score 0 · Answer 1 · answered Nov 24 '14 at 12:54

Not like that, Oracle directory LOGDIR is a name given to '~/log' folder. Now, your operating system identifies this log folder by name '~/log', but Oracle identifies the same folder by LOGDIR name. So, create a folder log in your home directory, and then tell oracle that you can identify ~/log location as LOGDIR by creating a Oracle directory using CREATE or replace DIRECTORY LOGDIR AS '~/log'. Both are required. – San

Shell script to create directory via Oracle using UTL_FILE

1 Answers1