在没有命名目录的情况下使用dbms_xmldom.writetofile [英] Use dbms_xmldom.writetofile without a Named Directory

问题描述

我正在尝试使用 dbms_xmldom.writetofile 过程使用PL/SQL编写XML文件.但是，上面的代码仅在我提供从 DBA_DIRECTORIES 提取的命名目录时有效，如下所示:

I am trying to write an XML file using PL/SQL using the procedure dbms_xmldom.writetofile. However, the code above only works when I supply a named directory taken from DBA_DIRECTORIES like below:

procedure PRINT_XML (p_xml          xmltype
                    ,p_requestid    number)
is

    l_filename      varchar2(100);
    doc             dbms_xmldom.domdocument;
    l_directory     request_history.OUTPUTWORKDIRECTORY%type;
    l_dml_stmnt     request_history.OUTPUTWORKDIRECTORY%type;

begin

    l_filename          := 'JPK_Accounting_Books_'||g_jpk_ess_request_id||'.xml';
    
    doc := dbms_xmldom.newdomdocument(p_xml);
    dbms_xmldom.writetofile(doc, 'TMP/'||l_filename);
    dbms_xmldom.freeDocument(doc);

EXCEPTION
    WHEN OTHERS THEN
        write_to_aflog(p_module     => 'JG.JPK.JE_PL_JPK_ACCT_BOOKS.PRINT_XML'
                  ,    p_message    => 'Error in PRINT_XML '||sqlerrm);
        raise;
end PRINT_XML;

我希望通过编程方式提供目录，因为每次执行时目录都会更改，如下所示:

I was hoping to supply the directory programmatically, as the directory changes per execution, like below:

procedure PRINT_XML (p_xml          xmltype
                    ,p_requestid    number)
is

    l_filename      varchar2(100);
    doc             dbms_xmldom.domdocument;
    l_directory     request_history.OUTPUTWORKDIRECTORY%type;
    l_dml_stmnt     request_history.OUTPUTWORKDIRECTORY%type;

begin

    l_filename          := 'JPK_Accounting_Books_'||g_jpk_ess_request_id||'.xml';
    
    select  OUTPUTWORKDIRECTORY
    into    l_directory
    from    request_history -- ESS
    where   requestid = p_requestid;
        
    doc := dbms_xmldom.newdomdocument(p_xml);
    dbms_xmldom.writetofile(doc, l_directory||'/'||l_filename);
    dbms_xmldom.freeDocument(doc);

EXCEPTION
    WHEN OTHERS THEN
        write_to_aflog(p_module     => 'JG.JPK.JE_PL_JPK_ACCT_BOOKS.PRINT_XML'
                  ,    p_message    => 'Error in PRINT_XML '||sqlerrm);
        raise;
end PRINT_XML;

但是，我收到错误 ORA-29280:无效的目录路径.我试图做一个立即执行创建或替换目录... ，但是我得到: ORA-01031:权限不足.

However, i am getting an error ORA-29280: invalid directory path. I have tried to do an EXECUTE IMMEDIATE CREATE OR REPLACE DIRECTORY... but i am getting: ORA-01031: insufficient privileges.

由于该组织的政策，我无法执行以下操作:

I cannot do the following due to the organization's policies:

1. 在 DBA_DIRECTORIES
中添加另一个永久目录
2. 为当前用户提供其他特权

还有另一种方法可以使用dbms_xmldom.writetofile(或类似方法)而不创建命名目录吗?

Is there another way to use dbms_xmldom.writetofile (or something similar) without creating a named directory?

推荐答案

第一个问题是该路径可能不存在，或者您对该目录没有写权限.

The first issue is that probably the path does not exist or you have no write privileges over the directory.

SQL> select directory_path from all_directories where directory_name = 'YOUR DIRECTORY' ;

如果没有行，则需要使用DBA来解决此问题，因为该目录不存在或对该目录没有特权.数据库目录是两个元素的组合:

If you got no rows, you need to address this with your DBA, as either the directory does not exist or you have no privileges over it. A database directory is a combination of two elements:

• 数据库目录是操作系统中某个位置的指针或引用.
• 该目录的路径必须存在，并且运行该进程的用户必须对其具有读取和写入特权.

对于第二个问题，问题在于拥有该过程的用户未被授予系统特权创建任何目录".您的DBA必须向您的用户授予CREATE ANY DIRECTORY特权，尽管我不建议这样做.授予任何特权是一种不良的安全习惯.

For the second issue, the problem is that the user who owns the procedure has not been granted with the system privilege CREATE ANY DIRECTORY. Your DBA must grant the CREATE ANY DIRECTORY privilege over your user, although I don't recommend it. Granting ANY privileges is a bad security practice.

创建目录通常是DBA的任务.我没有预见到需要动态创建它们的场景，只要您还必须在Filesystem(Linux)或Windows驱动器中创建基础目录，具体取决于您的操作系统.

Creating directories is normally a task for your DBA. I don't foresee a scenario when you need to create them dynamically, as long as you have also to create the underlying directory in the Filesystem ( Linux ) or Windows drive, depending of your operating system.

鉴于您的特殊情况，您别无选择.您需要重新评估解决方案.也许您可以使用WRITETOCLOB而不是WRITETOFILE，然后通过sqlplus在客户端将结果假脱机

这篇关于在没有命名目录的情况下使用dbms_xmldom.writetofile的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！