UTL_FILE 保存在本地机器中 [英] UTL_FILE saving in local machine

问题描述

你好，我想在我的本地机器上保存所有的功能、程序、包.我尝试使用 SPOOL 但使用 spool 我不确定获取文件名并保存它.所以我尝试使用 UTL_FILE .. 但问题是我的数据库在 UNIX 中，我想保存在我的 Windows 本地机器中.我收到下面提到的错误

费勒贝里赫特 -ORA-29283: Ungültiger DateivorgangORA-06512:在SYS.UTL_FILE"中，Zeile 536ORA-29283: Ungültiger DateivorgangORA-06512:在 Zeile 2429283. 00000 - 无效的文件操作 %s"*原因:试图从一个文件或目录中读取不存在，或者文件或目录访问被拒绝操作系统.*操作:验证文件系统上的文件和目录访问权限，如果正在读取，请验证该文件是否存在.

下面是我的代码.

CREATE DIRECTORY FUNC_DIR AS 'C:\Workspace\BE\DB\Funktionen\';
CREATE DIRECTORY PROC_DIR AS 'C:\Workspace\BE\DB\Prozeduren';
CREATE DIRECTORY PACK_DIR AS 'C:\Workspace\BE\DB\Package\';
CREATE DIRECTORY PACBO_DIR AS 'C:\Workspace\BE\DB\Package_Body';
GRANT READ,WRITE ON DIRECTORY FUNC_DIR TO PUBLIC;
GRANT READ,WRITE ON DIRECTORY PROC_DIR TO PUBLIC;
GRANT READ,WRITE ON DIRECTORY PACK_DIR TO PUBLIC;
GRANT READ,WRITE ON DIRECTORY PACBO_DIR TO PUBLIC;

DECLARE
fileHandler UTL_FILE.FILE_TYPE; 
filename VARCHAR2(60); 
filetext VARCHAR2(32766); 
filetype VARCHAR2(20);


BEGIN 
FOR a IN (SELECT distinct name,type INTO filename,filetype 
FROM all_source 
WHERE type IN 
('FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY') 
AND OWNER='HR')
LOOP
filetype := a.object_type;
filename := a.object_name;  
IF (filetype = 'FUNCTION') THEN
fileHandler := UTL_FILE.FOPEN('FUNC_DIR', filename||'.sql', 'W'); 
ELSIF filetype = 'PROCEDURE' THEN
fileHandler := UTL_FILE.FOPEN('PROC_DIR', filename||'.sql', 'W'); 
ELSIF filetype = 'PACKAGE' THEN
fileHandler := UTL_FILE.FOPEN('PACK_DIR', filename||'.sql', 'W');  
ELSIF filetype = 'PACKAGE_BODY' THEN
fileHandler := UTL_FILE.FOPEN('PACBO_DIR', filename||'.sql', 'W'); 

FOR b IN (SELECT text INTO filetext FROM all_source WHERE TYPE IN('FUNCTION')AND OWNER = 'HR' AND 
NAME = (filename))
LOOP
filetext := b.text;
UTL_FILE.PUTF(fileHandler, filetext); 
END LOOP;
UTL_FILE.FCLOSE(fileHandler); 

ELSE
DBMS_OUTPUT.PUT_LINE('OBJECT TYPE DOES NOT MATCH');
END IF;
END LOOP;
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/

推荐答案

您可以在 PLSQL 中使用 dbms_output.put_line 和 SPOOL 来获得结果.例如，如下所示.这会在 file_name.txt 文件中打印 ename、sal.

You can use dbms_output.put_line in your PLSQL together with SPOOL to get the result. For example like the below.This prints ename, sal in the file_name.txt file.

    set serveroutput on  
    Spool e:\file_name.txt  
    Begin  
      for c in (Select ename, sal from emp)  
      loop  
        dbms_output.put_line(c.ename || ',' || c.sal);  
      end loop;  
    End;  
    /  
    spool off

对于您的示例，您可以使用以下内容

for your example, you can use the below

    set serveroutput on  
    Spool C:\Workspace\BE\DB\Funktionen\Function1.sql
    BEGIN 
    FOR a IN (SELECT distinct name,type 
    FROM all_source 
    WHERE type IN 
    ('FUNCTION')
    AND OWNER='HR')
    LOOP
    FOR b IN (SELECT text FROM all_source WHERE TYPE IN('FUNCTION')AND OWNER = 'HR' AND 
    NAME = (a.name))
    LOOP
    dbms_output.enable;
    dbms_output.put_line(b.text);

    END LOOP;
    END LOOP;
    EXCEPTION
    WHEN others THEN
    dbms_output.put_line( 'ERROR: Invalid PATH FOR file.'||sqlerrm);
    END;
    /  
    spool off

您可以生成一个包含所有函数的 plsql 的主文件，您可以为所有函数运行该文件

You can generate one master file which has plsql for all the functions individually which you can run for all the functions

        set serveroutput on
    spool C:\Workspace\BE\DB\Funktionen\function12.sql
    begin
    FOR a IN (SELECT distinct name,type 
        FROM all_source 
        WHERE type IN 
        ('FUNCTION')
        AND OWNER='HR')
        loop
     dbms_output.enable  ; 
    dbms_output.put_line('set serveroutput on  
        Spool C:\Workspace\BE\DB\Funktionen\'||a.name||'.sql
        BEGIN 
        FOR a1 IN (SELECT distinct name,type 
        FROM all_source 
        WHERE type IN 
        (''FUNCTION'')
        AND OWNER=''HR'' and name='''||a.name||''')
        LOOP
        FOR b IN (SELECT text FROM all_source WHERE TYPE IN(''FUNCTION'')AND OWNER = ''HR'' AND 
        NAME = '''||a.name||'''))
        LOOP
        dbms_output.enable;
        dbms_output.put_line(b.text);

        END LOOP;
        END LOOP;
        EXCEPTION
        WHEN others THEN
        dbms_output.put_line( ''ERROR: Invalid PATH FOR file.''||sqlerrm);
        END;'
        );
        END LOOP;
        END;
        /
    spool off

这篇关于UTL_FILE 保存在本地机器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！