UTL_FILE saving in local machine

Question

Hello, I wanted to save all the functions,procedures,packages in my local machine. I tried with SPOOL but with spool im not sure to get the filename and save it. So i tried with UTL_FILE.. But the problem is my database is in UNIX and I wanted to save in my windows local machine. Im getting the below mentioned error

Fehlerbericht - ORA-29283: Ungültiger Dateivorgang ORA-06512: in "SYS.UTL_FILE", Zeile 536 ORA-29283: Ungültiger Dateivorgang ORA-06512: in Zeile 24 29283. 00000 - "invalid file operation%s" *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.

And the below is my code.

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;
/

score 0 · Answer 1 · answered Mar 27 '20 at 09:06

0

UTL_FILE writes to the database server. To write to your local machine, you would need to mount your directories on the db server. This is generally a bad idea!

Use spool to write to your machine just do:

spool c:\path\to\your\folder\file.name
select ....

answered Mar 27 '20 at 09:06

Chris Saxon

9,105
1
26
42

thank you..but the problem is spool can not be used inside plsql know.. so i have to write separate script for each function,procedure,package or so.. If im not wrong. – Sangathamilan Ravichandran Mar 27 '20 at 09:16
You're right, you can't use spool inside PL/SQL. But if you're trying to export the data to your machine, you can just run the query in SQL*Plus/SQL Dev/... right? – Chris Saxon Mar 27 '20 at 09:44
oh ya got it. Let me try that.. Thank you – Sangathamilan Ravichandran Mar 27 '20 at 09:47

psaraj12 · Answer 2 · 2020-03-27T12:33:42.383

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

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

thanks for your input. But i wanted the filename to be the name of the function itself..I retrieve the function name using the loop and then i provided that to the utl_file handler. Is the anyoption that it can de done using spool? — Sangathamilan Ravichandran, Mar 27 '20 at 11:53
You can create a master file which will have PLSQL for individual functions,i have updated the answer — psaraj12, Mar 27 '20 at 12:35
thanks again. I tried this but still the file is getting save in the name of function12.sql — Sangathamilan Ravichandran, Apr 01 '20 at 07:04
Check the contents of function12.sql it will have command to run individual files.so u can just run function12.sql and individual function SQLs will get created — psaraj12, Apr 01 '20 at 07:21

UTL_FILE saving in local machine

2 Answers2

Linked