Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Santa Monica, CA
    Posts
    33

    Question Unanswered: Problem with UTL_FILE package...

    Hi,
    I’m studying for the 1Z0-147 exam and I’m trying to work with the UTL_FILE package. I wrote one procedure (sal_status from the class book) but I can’t open the file. I always have the message:

    ERROR at line 1:
    ORA-29280: invalid directory path
    ORA-06512: at "SYS.UTL_FILE", line 18
    ORA-06512: at "SYS.UTL_FILE", line 424
    ORA-06512: at "HR.SAL_STATUS", line 16
    ORA-06512: at line 1

    What can be the problem?
    I added in my init.ora file the parameter: UTL_FILE_DIR = *
    Normally with that I can write everywhere in my PC, isn’t it?

    To be sure about this rule, I tried to write my file in the same directory than the log files (/bdump or /udump), but I always have the same error:

    SQL> exec sal_status('C:\oracle\admin\HR\bdump','erw.txt');
    BEGIN sal_status('C:\oracle\admin\HR\bdump','erw.txt'); END;

    *
    ERROR at line 1:
    ORA-29280: invalid directory path
    ORA-06512: at "SYS.UTL_FILE", line 18
    ORA-06512: at "SYS.UTL_FILE", line 424
    ORA-06512: at "HR.SAL_STATUS", line 16
    ORA-06512: at line 1

    If I try directly the command FOPEN, I have the same problem:

    SQL> edit
    Wrote file afiedt.buf

    1 DECLARE
    2 v_filehandle UTL_FILE.FILE_TYPE;
    3 BEGIN
    4 v_filehandle := UTL_FILE.FOPEN('C:\oracle\admin\HR\bdump\','erw.tx t','w');
    5* END;
    SQL> /
    DECLARE
    *
    ERROR at line 1:
    ORA-29280: invalid directory path
    ORA-06512: at "SYS.UTL_FILE", line 18
    ORA-06512: at "SYS.UTL_FILE", line 424
    ORA-06512: at line 4

    I also tried with the parameter: utl_file_dir=*

    Can you help me? Thank you…

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with UTL_FILE package...

    The file has to be on the server, not the client. Unless you are running a personal copy of Oracle installed on your PC, the server will be another machine, perhaps a Unix or NT box, so local files on your C drive will not be accessible.

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Problem with UTL_FILE package...

    Do you the Oracle Server in the same machine where you are working ?

    Apart from that apply this command in SQL*Plus and reply the result:

    show parameters utl_file

    What Oracle version are you using , 8i or 9i ?
    Joel Pérez

  4. #4
    Join Date
    Jan 2004
    Location
    Santa Monica, CA
    Posts
    33

    Arrow

    Thank you andrewst and joelperez for your responses... In fact I have a copy of Oracle9i in my PC and I can have another machine. Can I try to a CD-ROM ? It's just that I like to practice what I'm learning and not only for the certification.....
    Will I have the same problem with the 'CREATE DIRECTORY' command? Need I to just read and understand about the BFILES and LOG? Tnak you in advance for your response bacause I don't want to lose the same time for this subjects....
    Sincerely
    Eric

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    Originally posted by eric_willer
    Thank you andrewst and joelperez for your responses... In fact I have a copy of Oracle9i in my PC and I can have another machine. Can I try to a CD-ROM ? It's just that I like to practice what I'm learning and not only for the certification.....
    Will I have the same problem with the 'CREATE DIRECTORY' command? Need I to just read and understand about the BFILES and LOG? Tnak you in advance for your response bacause I don't want to lose the same time for this subjects....
    Sincerely
    Eric
    You must not have the same problem if you create a directory but I do not know really why is the cause because of there is that error.

    Examples (UNIX-Specific)
    Given the following:

    SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log';
    SQL> GRANT READ ON DIRECTORY log_dir TO DBA;

    SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';
    SQL> GRANT READ ON DIRECTORY user_dir TO PUBLIC;
    Joel Pérez

  6. #6
    Join Date
    Jan 2004
    Location
    Santa Monica, CA
    Posts
    33

    Smile

    Hi,

    In first, I want to say that all is ok now. I can write a file on my disk 'C:' in Window XP with my Oracle9i. How? (Just in case for another person with the same problem....).

    1) Like you said, I looked the parameter UTL_FILE_DIR with the command 'show', I didn't have anything inside. But in my init.ora file, I had '*'. So I just took the book about the certification 1Z1-031 (My next....) and found a page about the PFILE and the SPFILE. So I understood that I had a SPFILE somewhere and the modification into the PFILE (init.ora) was obsolete. I don't know yet how to pass a parameter inside the SPFILE but I tried some commands from the book. BADABOUM ! My database became corrupted (I don't know why, memory probleme...) and I needed to drop and recreate it. During the process of creation, I saw the parameter UTL_FILE_DIR and I initialize it with the value '*'. After that I tried my procedure and all became right !
    2) What is the command that I can use to enter the parameter UTL_FILE_DIR into the SPFILE (via SQLPLUS) ? (For my knowledge).

    Thank you so much to guide me.....
    Eric Willer
    Last edited by eric_willer; 01-16-04 at 03:44.

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    alter system set <parameter>=<value>

    You'll need to know this for your exam

  8. #8
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    ALTER SYSTEM SET UTL_FILE_DIR=* SCOPE=<<option>>;

    option:
    SCOPE
    The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you are started up the database using a parameter file (pfile) or server parameter file (spfile).

    MEMORY
    MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.

    SPFILE
    SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter.

    BOTH
    BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

    If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

    I recommend you to use both if you are in 9i. If you are in 8i you have to change the parameter in the init and shutdown/startup the database.
    Joel Pérez

  9. #9
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    ALTER SYSTEM
    Purpose
    Use the ALTER SYSTEM statement to dynamically alter your Oracle instance. The settings stay in effect as long as the database is mounted.

    http://download-west.oracle.com/docs...htm#SQLRF00902
    Joel Pérez

  10. #10
    Join Date
    Jan 2004
    Location
    Santa Monica, CA
    Posts
    33
    Thank you so much for all of these information.
    Have a good weekend.
    Sincerely,
    Eric

  11. #11
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    The same for you eric_willer
    Joel Pérez

Posting Permissions

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