如何解决 Oracle DBMS_LOB 问题 [英] How to solved problem with Oracle DBMS_LOB

问题描述

我正在尝试将 XML 文件的信息保存在数据库表中，并且我正在使用以下过程:

I am trying to save the information of an XML file in a database table and I am using this procedure:

create or replace PROCEDURE P_FILEUPLOAD_XML (P_CMTT_CODE IN NUMBER DEFAULT 15, P_TEXT IN VARCHAR2, P_TEXT_NAR IN VARCHAR2, P_PATH IN VARCHAR2, P_FILENAME IN VARCHAR2, P_RET_VAL OUT NUMBER) IS

GRUPO       VARCHAR2(20);
l_dir       CONSTANT VARCHAR2(35) := P_PATH;
l_fil       CONSTANT VARCHAR2(30) := P_FILENAME; 
l_loc       BFILE; -- Pointer to the BFILE
l_ret       BOOLEAN := FALSE; -- Return value
l_pos       NUMBER := 1; -- Current position in the file (file begins at position 1)
l_sum       number default 8000; -- Amount of characters have been read
l_buf       VARCHAR2(32767); -- Read Buffer
l_sen       CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(10)); -- Character at the end of the file is NEWLINE (ascii = 10)
l_end       NUMBER; -- End of the current word which will be read
l_counter NUMBER := 0; -- Counter for line sequence
l_line      VARCHAR2(32767); -- Contains the info line by line for insert

BEGIN

SELECT TEXTO INTO GRUPO FROM gzvcatg
         UNPIVOT ((codigo, texto) FOR gzvcatg_external_code IN (
            (gzvcatg_external_code1, gzvcatg_desc1) AS '1')
         WHERE GZVCATG_GROUP = 'TIT_ELEC'
            AND CODIGO = 'PATH';

     -- Mapping the physical file with the pointer to the BFILE
        l_loc := BFILENAME(GRUPO,'ES0000251446027471.xml');

    -- Open the file in READ_ONLY mode
       DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY);
       LOOP
          l_counter := l_counter + 1; -- Counter for sequence
          -- Calculate the end of the current word
          l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);

          -- Process end-of-file
          IF (l_end = 0) THEN
            l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);
            l_sum := l_end - l_pos - 1;
            EXIT;
          END IF;

          -- Read until end-of-file
          l_sum := l_end - l_pos;
          DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
          l_line := UTL_RAW.CAST_TO_VARCHAR2(l_buf);

        BEGIN 
            INSERT INTO SPRCMNT (
                         SPRCMNT_CMTT_CODE,
                         SPRCMNT_TEXT,
                         SPRCMNT_TEXT_NAR)
                                VALUES(P_CMTT_CODE,
                                       P_TEXT,
                                       P_TEXT_NAR);    
             EXCEPTION WHEN OTHERS THEN 
             ROLLBACK;           
        END;
    END LOOP;    
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error:' || SQLERRM);
        DBMS_LOB.CLOSE(l_loc);
        P_RET_VAL := 3;
        dbms_output.put_line('P_RET_VAL:' || P_RET_VAL);    
END;

但是当我执行该过程时，我收到以下错误:

But when I execute the procedure I get the following error:

Error:ORA-22285: non-existent directory or file FILEOPEN operation

如果我的 XML 文件存在，我不明白为什么路径 C:\XMLS\ 确实存在.

I do not understand why the path C:\XMLS\ does exist if my XML file exists .

我得到了查询结果的路线.

I get the route with the result of the Query.

这个Query的结果被赋值给了一个叫做GRUPO的变量，这是我在程序开始时声明的，然后我把这个变量作为参数放在函数中BFILENAME，它最初包含 directory y filename.您可以在BFILENAME此处查看文档

This result of the Query was assigned to a variable called GRUPO, which is what I declare at the beginning of the procedure, and then I place this variable as a parameter in the function BFILENAME, which originally contains the directory y filename. You can see documentation BFILENAME here

l_loc := BFILENAME(GRUPO,'ES0000251446027471.xml');

除此之外，我还在 Oracle 中创建了一个目录，如下所示:

In addition to this I have created a directory in Oracle as follows:

CREATE OR REPLACE DIRECTORY DIR_XML as 'C:\XMLS\';

我也给了目录权限

GRANT ALL ON DIRECTORY DIR_XML TO PUBLIC;

我在这个问题上浪费了很多时间，但找不到任何解决方案.任何帮助将不胜感激.

I've been wasting a lot of time with this problem and I can't find any solution. Any help will be appreciated.

推荐答案

来自 您链接到的文档:

• 'directory' 是一个数据库对象，用作文件实际所在的服务器文件系统上完整路径名的别名.

• 'directory' is a database object that serves as an alias for a full path name on the server file system where the files are actually located.

所以 GRUPO 应该计算为 'DIR_XML' 而不是 'C:\XMLS'.该文档的示例中显示了这一点.(有一个旧的基于作为数据库参数存储的路径的预目录对象机制，但安全性较低......)

so GRUPO should evaluate to 'DIR_XML' rather than 'C:\XMLS'. That is shown in the example in that documentation. (There was an old pre-directory-object mechanism based on a path stored as a database parameter, but that was less secure...)

如果您只有路径，您可以查找目录名称:

If all you have is the path you can look up the directory name:

select directory_name from all_directories where directory_path = 'C:\XMLS'

请记住，目录路径不必是唯一的，因此您可能需要处理重复项.

bearing in mind that directory paths don't have to be unique, so you may have to deal with duplicates.

但正如@Matthew 已经解释过的，以及如文档所说(强调):

But as @Matthew already explained, and as the documentation says (emphasis added):

目录对象为服务器文件系统上的目录指定别名，其中 ...

A directory object specifies an alias for a directory on the server file system where ...

数据库只能看到它自己文件系统上的文件——本地的或共享的——而不是客户端文件系统上的文件.如果您也在本地运行数据库，则没有区别(尽管目录和文件权限仍然很重要).如果您正在访问远程数据库，那么它看不到您的客户端 C: 驱动器，并且如果您提供目录对象名称，您仍然会得到如下内容:

The database can only see files on its own filesystem - local or shared - and not those on client filesystems. If you are running the DB locally as well then there is no difference (though directory and file permissions still matter). If you are accessing a remote DB then it cannot see your client C: drive, and if you give the directory object name you'll still get something like:

ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory

您必须将 XML 文件放在操作系统帐户可以访问的数据库服务器上的目录中，并创建一个指向服务器上该位置的目录对象；然后引用目录对象名称，而不是底层文件系统路径.

You have to put your XML files in a directory on the DB server that the operating system account has access to, and create a directory object that points to that location on the server; and then refer to the directory object name, not the underlying filesystem path.

这篇关于如何解决 Oracle DBMS_LOB 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！