Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: How can I read param utl_file_dir in Forms 9i ?

    Hi !
    1. I want to add a new dir to utl_file_dir but I don't know how to read
    this param in Forms 9i ?
    2. Why it doesn't work ? :
    :PARAMETER.S3 := '/new';
    forms_ddl('ALTER SYSTEM SET utl_file_dir=' || :PARAMETER.S3
    || 'scope=spfile');
    forms_ddl('commit');
    ------------------------------------------------------
    But something like this works OK !!! :
    forms_ddl('ALTER SYSTEM SET utl_file_dir=' ' /aaa ' ' scope=spfile');
    forms_ddl('commit');

    But I need give to forms_ddl param or variable, not a static dir !!!
    Where's a mistake ?
    Every time before I check the value of utl_file_dir I make shutdown and startup.
    Thx

  2. #2
    Join Date
    Aug 2003
    Posts
    41
    probably it is expecting something like this. (Extra quotes)


    forms_ddl('ALTER SYSTEM SET utl_file_dir=' || ''' || :PARAMETER.S3 ||| '''|| 'scope=spfile');

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311

    changing utl_file_dir in forms 9i

    Hi,

    Although it is possible to issue the named statements using FORMS_DDL, the database parameter UTL_FILE_DIR cannot be dynamicly set. It has to be changed in the init.ora and requires a database restart.

    In this file with all database initialization parameters, an extra line needs to be added like this:
    UTL_FILE_DIR = <your directory on the database server>

    greetz,

    Edwin van Hattem
    OCP DBA

  4. #4
    Join Date
    Sep 2003
    Posts
    5
    Originally posted by sunilthomas98
    probably it is expecting something like this. (Extra quotes)


    forms_ddl('ALTER SYSTEM SET utl_file_dir=' || ''' || :PARAMETER.S3 ||| '''|| 'scope=spfile');
    It doesnt'work
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    utl_file_dir string || :PARAMETER.S3 |||

    And one more question :
    If I have
    utl_file_dir = '/oracle' can user save files in all subdirectories e.g.
    /oracle/a, /oracle/tetete, /oracle/bin , .... ??? Or it means that user can save only in this one folder : /oracle ???
    Thx

  5. #5
    Join Date
    Aug 2003
    Posts
    41
    That seems worse!!


    UTL_FILE_DIR seems to be a static parameter, means, it cannot be changed without bouncing your database. Also, it looks a bit hairy to dynamically define this parameter unless you are creating a tool for configuring your database. Are you really run out of options?

    I think you have to define each directory seperately in your UTL_FILE_DIR parameter you are going to spool to. I dont think subdirectories are included by default.


    -Sunil.

  6. #6
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    UTL_FILE_DIR is a static parameter and cannot be set using alter system.. only by changing the init.or and bouncing the database.

    If you want to give the user the possibilitie to write to subdirectories as well, then the init.ora should be changed like this :

    UTL_FILE_DIR=u:/oracle/*

    Hope this helps.

    Edwin

Posting Permissions

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