DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORACLE 9i REL 2 - UTL_FILE_DIR

  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question ORACLE 9i REL 2 - UTL_FILE_DIR

    I AM TRYING TO CREATE A TEXT FILE AS FOLLOWS
    AS SYSTEM..
    Create or Replace procedure UTLTEST as

    f1 utl_file.file_type;

    begin

    -- Open a file in write mode

    f1 := utl_file.fopen('MYFILE','NEWFILE.TXT','W');

    -- Write a line to a file

    utl_file.put_line(f1,'1. This is a test of UTL_FILE packages');

    utl_file.put_line(f1,'2. Oracle has added a new procedures in the package');

    utl_file.put_line(f1,'3. We will see all the procedure one by one');

    -- Close a file

    utl_file.fclose(f1);
    exception
    WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_path'); RAISE;

    WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_mode'); RAISE;

    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_filehandle'); RAISE;

    WHEN UTL_FILE.INVALID_OPERATION THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_operation'); RAISE;

    WHEN UTL_FILE.READ_ERROR THEN
    DBMS_OUTPUT.PUT_LINE ('read_error'); RAISE;

    WHEN UTL_FILE.WRITE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE ('write_error'); RAISE;

    WHEN UTL_FILE.INTERNAL_ERROR THEN
    DBMS_OUTPUT.PUT_LINE ('internal_error'); RAISE;

    end;

    DIR CREATED AS
    CREATE OR REPLACE DIRECTORY MYFILE AS 'C:\APPS\ORDER1';

    GRANT READ ON DIRECTORY MYFILE TO ADMIN1;

    WHEN I EXECUTE THIS FILE AS ADMIN1
    EXEC SYSTEM.utltest

    HERE IS THE ERROR MESSAGE
    SQL> EXEC SYSTEM.utltest
    invalid_operation
    BEGIN SYSTEM.utltest; END;

    *
    ERROR at line 1:
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 449
    ORA-29283: invalid file operation
    ORA-06512: at "SYSTEM.UTLTEST", line 33
    ORA-06512: at line 1

    COULD SOME ONE PLEASE HELP ?

  2. #2
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Hi,

    First check whether UTL_FILE_DIR parameter is set or not.
    sql>show parameter utl_file_dir
    or
    sql>select * from v$parameter where name='utl_file_dir';

    If you dont get a value, then it is not set.
    If a value for UTL_FILE_DIR is not set, then you cannot use the UTL_FILE package.

    This being a static parameter (u cant change using ALTER SYSTEM SET.. command), you need to bring down the instance, edit the init.ora file to include the UTL_FILE_DIR parameter and then restart the instance.

    Note that you need to sepcify a valid path for the UTL_FILE_DIR parameter, and that that directory has write permissions set.

    specify the directory in the following way in the inid.ora file:

    UTL_FILE_DIR="/uo2/oradata2/prod/utl_dir/"

    If u wish to specify multiple directories, put multiple entries in the init.ora file, line by line, and continuously:

    UTL_FILE_DIR="/uo1/oradata1/prod/utl_dir/"
    UTL_FILE_DIR="/uo2/oradata1/prod/utl_dir/"
    UTL_FILE_DIR="/uo3/oradata2/prod/utl_dir/"

    And Last, but not the least: READ THE DOCUMENTATION !!
    Regards,
    Suhas
    "Shoot for the moon, even if u miss, u'll land among the stars !!"

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by suhasd75
    Hi,

    First check whether UTL_FILE_DIR parameter is set or not.
    sql>show parameter utl_file_dir
    or
    sql>select * from v$parameter where name='utl_file_dir';

    If you dont get a value, then it is not set.
    If a value for UTL_FILE_DIR is not set, then you cannot use the UTL_FILE package.
    And Last, but not the least: READ THE DOCUMENTATION !!
    Regards,
    Suhas
    "Shoot for the moon, even if u miss, u'll land among the stars !!"
    You don't need to set this parm in 92, maybe you should read the documentation dude.

    Here's a simple example that you should be able to build on to create your own proc

    CREATE DIRECTORY test_dir AS 'c:\temp';

    CREATE OR REPLACE PROCEDURE test
    AS
    v_output_file1 utl_file.file_type;
    BEGIN
    /* Ensure TEST_DIR below is in all caps */
    /* Third argument below is W which overwrites file each time, change to A to append */
    v_output_file1 := utl_file.fopen('TEST_DIR', 'my_test.csv', 'W');
    FOR cursor_emp IN (SELECT empnom,ename, deptno FROM emp)
    LOOP
    utl_file.put_line(v_output_file1, cursor_emp.empno || ',' || cursor_emp.ename || ',' || cursor_emp.deptno);
    END LOOP;
    utl_file.fclose_all;
    END;
    /
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    oh really????

    Do u think i'll give permision to ALL users to CREATE DIRECTORY???
    Dont i know this option ...

    It's all the more easier to manage.. otherwise, u'll end up having n number of "directories",,,, dude...Default directories are always easier to manage.... believe me

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by suhasd75
    oh really????

    Do u think i'll give permision to ALL users to CREATE DIRECTORY???
    Dont i know this option ...

    It's all the more easier to manage.. otherwise, u'll end up having n number of "directories",,,, dude...Default directories are always easier to manage.... believe me
    whatever dude, give'em sysdba for all I care

    For tinakh25's testcase make sure 'C:\APPS\ORDER1' is a valid path on the server, not your client.

    I ran your testcase ok by changing the path to c:\temp below


    SQL> show parameter utl

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    utl_file_dir string
    SQL> CREATE OR REPLACE DIRECTORY MYFILE AS 'C:\TEMP';

    Directory created.

    SQL>
    SQL> Create or Replace procedure UTLTEST as
    2 f1 utl_file.file_type;
    3 begin
    4 f1:= utl_file.fopen('MYFILE','NEWFILE.TXT','W');
    5 utl_file.put_line(f1,'1. This is a test of UTL_FILE packages');
    6 utl_file.put_line(f1,'2. Oracle has added a new procedures in the package');
    7 utl_file.put_line(f1,'3. We will see all the procedure one by one');
    8 utl_file.fclose(f1);
    9 end;
    10 /

    Procedure created.

    SQL>
    SQL> exec utltest;

    PL/SQL procedure successfully completed.
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width