ORA-29283，macOS Sierra，Oracle SQL Developer，无效的文件操作

问题描述

我有Oracle SQL Developer Version 18.1.0.095我的操作系统macOS Sierra 10.12.6

运行SELECT * FROM v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0  Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

我跑步时

SET SERVEROUTPUT ON;

CREATE OR REPLACE DIRECTORY user_dir AS '/Users/joseluisbz/Documentos/TestSQL';

GRANT READ, WRITE ON DIRECTORY user_dir TO PUBLIC;

我得到了:

Directory USER_DIR created.


Grant succeeded.

在macOS上检查特权

Checking privileges on macOS

MacBook-Air:~ joseluisbz$ ls -al /Users/joseluisbz/Documentos/TestSQL
total 8
drwxrwxrwx    3 joseluisbz  staff   102 Jan 15 12:09 .
drwxr-xr-x@ 114 joseluisbz  staff  3876 Jan 15 12:08 ..
-rw-rw-rw-@   1 joseluisbz  staff   161 Jan 15 11:47 untitled.html
MacBook-Air:~ joseluisbz$

我跑步时

DECLARE
    content_file   CLOB;
    file_handler   UTL_FILE.FILE_TYPE;
BEGIN
    dbms_output.put_line('before open');
    file_handler := UTL_FILE.FOPEN('USER_DIR','untitled.html','R');
    dbms_output.put_line('after open');
    /*
    BEGIN
        UTL_FILE.GET_RAW(file_handler, content_file);
        dbms_output.put_line(content_file);
    EXCEPTION
        WHEN no_data_found THEN
            EXIT;
    END;
    */
/*
    IF UTL_FILE.IS_OPEN(file_handler) THEN
        dbms_output.put_line('File is Open');
    END IF;
    */
    --UTL_FILE.FCLOSE(file_handler);

END;

我得到了:

before open


Error starting at line : 9 in command -
DECLARE
    content_file   CLOB;
    file_handler   UTL_FILE.FILE_TYPE;
BEGIN
    dbms_output.put_line('before open');
    file_handler := UTL_FILE.FOPEN('USER_DIR','untitled.html','R');
    dbms_output.put_line('after open');
    /*
    BEGIN
        UTL_FILE.GET_RAW(file_handler, content_file);
        dbms_output.put_line(content_file);
    EXCEPTION
        WHEN no_data_found THEN
            EXIT;
    END;
    */
/*
    IF UTL_FILE.IS_OPEN(file_handler) THEN
        dbms_output.put_line('File is Open');
    END IF;
    */
    --UTL_FILE.FCLOSE(file_handler);

END;
Error report -
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 6
29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.

如何解决此问题?

推荐答案

Oracle中的目录应指向数据库服务器上的实际目录，而不是您的PC. UTL_FILE无法使用您PC上的文件

Directories in Oracle should point to actual directories on the Database server, not your PC. UTL_FILE cannot work with files on your PC

这篇关于ORA-29283，macOS Sierra，Oracle SQL Developer，无效的文件操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！