FAQ
the vms user oracle needs rights to the directory.
RROGERS_at_galottery.org 09/30/02 10:53AM >>>
List,
I have a package that creates files on the server. The directory
location and file name are obtained from tables in oracle. The procedure
works as designed on Novell 7.3.4 and no changes were needed when the
database way loaded on Linux Oracle 8.1.7. I am trying to move the
database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
get the package to write the files to the OS directory.
The package is created by the Oracle user DTSUSER and executed by
DTSUSER. There is no OPENVMS user DTSUSER.
The sysadmin assures me that the permissions are correct to write to
the directory.
I have place a Dbms_output in the package to display the directory
information and it looks correct.
Is there anything different that has to be done to an OPENVMS server
that will allow a package to write to a directory using the Utl_File
package?

Listing from the Oracle tables:

DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

------------------------------ ------------------------------

LOAD_USERID LOADITUP

LOAD_PASSWORD ILOADIT

LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

Listing from the package that writes the parameter file:

Procedure Create_New_Par_File

(

P_Current_Table_Name In Varchar2,

P_Run_Date In Date,

P_Load_Userid In Varchar2,

P_Load_Password In Varchar2,

P_Load_Service_Name In Varchar2,

P_Load_Par_File_Dir In Varchar2,

P_Load_Data_File_Dir In Varchar2,

P_Load_Control_File_Dir In Varchar2,

P_Load_Log_File_Dir In Varchar2,

P_Load_Bad_File_Dir In Varchar2,

P_Load_Discard_File_Dir In Varchar2

)

as

Begin

Declare

L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to
hold the File Pointer for the parameter file.

Begin
I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

EN TO.

Open a new parameter file

L_Par_File_Hand :=
Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

-- Print the following lines into the parameter file.

Utl_File.Put
(L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load_Service_Name);

Utl_File.New_Line(L_Par_File_Hand);

If Not P_Current_Table_Name = 'GLCRET'

Then

Utl_File.Put (L_Par_File_Hand,'Errors=1');

Else

Utl_File.Put (L_Par_File_Hand,'Errors=50');

End If;

Utl_File.New_Line(L_Par_File_Hand);

Utl_File.Put
(L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_Name||'.CTL');

Utl_File.New_Line(L_Par_File_Hand);

Utl_File.Put
(L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_char(P_Run_Date,'mmdd')||'.LOG');

Utl_File.New_Line(L_Par_File_Hand);

Utl_File.Put
(L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_char(P_Run_Date,'mmdd')||'.BAD');

Utl_File.New_Line(L_Par_File_Hand);

Utl_File.Put
(L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_Name||to_char(P_Run_Date,'mmdd')||'.DSC');

as

Utl_File.New_Line(L_Par_File_Hand);

Utl_File.Put
(L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'/'||

P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

-- Close the file after printing.

Utl_File.Fclose(L_Par_File_Hand);

Exception

When Others then

Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

End;

Thanks, Any assistance would be appreciated. I am just getting in to
the OPENVMS OS.
Ron
ROR mª¿ªm

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
INET: RROGERS_at_galottery.org

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gene Sais
INET: Gsais_at_co.palm-beach.fl.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Baswannappa, Shiva at Oct 1, 2002 at 4:53 pm
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------ ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=
    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    -- Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');



    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');



    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||



    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    -- Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Baswannappa, Shiva
    INET: SXBaswan_at_dcss.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gene Sais at Oct 1, 2002 at 6:25 pm
    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------ ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=
    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    -- Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');



    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');



    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||



    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    -- Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Baswannappa, Shiva
    INET: SXBaswan_at_dcss.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Ron Rogers at Oct 1, 2002 at 7:08 pm
    Thanks to all of you for the assistance.
    I have the package working by coding the directory into the
    utl_file.fopen command and the files are being created okay. I got the
    batch procedure to work with the SQLLDR command( I did not know you had
    to set noon and each line starts with a $). It works okay as a database.
    It takes 5 min 48 sec to load 20 different tables with a total of 178000
    rows.
    When I get the database up to date the developers will test their
    applications and I will start making the production server.
    Thanks,
    Ron
    Gsais_at_co.palm-beach.fl.us 10/01/02 02:25PM >>>
    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using
    UTL_FILE

    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The
    procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------ ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir I
    n Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=
    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    -- Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);


    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||



    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    -- Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Baswannappa, Shiva
    INET: SXBaswan_at_dcss.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jared.Still_at_radisys.com at Oct 2, 2002 at 11:13 pm
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com
    10/01/2002 11:25 AM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE



    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File


    (


    P_Current_Table_Name In Varchar2,


    P_Run_Date In Date,

    P_Load_Userid In Varchar2,


    P_Load_Password In Varchar2,


    P_Load_Service_Name In Varchar2,


    P_Load_Par_File_Dir In Varchar2,


    P_Load_Data_File_Dir In Varchar2,


    P_Load_Control_File_Dir In Varchar2,


    P_Load_Log_File_Dir In Varchar2,


    P_Load_Bad_File_Dir In Varchar2,


    P_Load_Discard_File_Dir In Varchar2


    )


    as


    Begin


    Declare


    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.




    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');


    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);





    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');


    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');


    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');


    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||



    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');


    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception


    When Others then


    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;



    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Vergara, Michael (TEM) at Oct 2, 2002 at 11:28 pm
    I did it like the control_files entry:

    utl_file_dir = (C:\TEMP,

    D:\OraNT\Archive)

    ...and it works just fine.

    Cheers,
    Mike

    -----Original Message-----
    Sent: Wednesday, October 02, 2002 4:14 PM
    To: Multiple recipients of list ORACLE-L

    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com
    10/01/2002 11:25 AM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE



    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File


    (


    P_Current_Table_Name In Varchar2,


    P_Run_Date In Date,

    P_Load_Userid In Varchar2,


    P_Load_Password In Varchar2,


    P_Load_Service_Name In Varchar2,


    P_Load_Par_File_Dir In Varchar2,


    P_Load_Data_File_Dir In Varchar2,


    P_Load_Control_File_Dir In Varchar2,


    P_Load_Log_File_Dir In Varchar2,


    P_Load_Bad_File_Dir In Varchar2,


    P_Load_Discard_File_Dir In Varchar2


    )


    as


    Begin


    Declare


    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.




    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');


    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);





    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');


    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');


    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');


    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||



    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');


    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception


    When Others then


    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;



    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Vergara, Michael (TEM)
    INET: mvergara_at_guidant.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Babu Nagarajan at Oct 3, 2002 at 12:03 am
    I have seen comma delimited entries working...

    Babu
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, October 02, 2002 6:13 PM

    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com
    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Babu Nagarajan
    INET: orclbabu_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gene Sais at Oct 3, 2002 at 1:28 pm
    On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com
    10/01/2002 11:25 AM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE



    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File


    (


    P_Current_Table_Name In Varchar2,


    P_Run_Date In Date,

    P_Load_Userid In Varchar2,


    P_Load_Password In Varchar2,


    P_Load_Service_Name In Varchar2,


    P_Load_Par_File_Dir In Varchar2,


    P_Load_Data_File_Dir In Varchar2,


    P_Load_Control_File_Dir In Varchar2,


    P_Load_Log_File_Dir In Varchar2,


    P_Load_Bad_File_Dir In Varchar2,


    P_Load_Discard_File_Dir In Varchar2


    )


    as


    Begin


    Declare


    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.




    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');


    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);





    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');


    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');


    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');





    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');


    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||



    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');


    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception


    When Others then


    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;



    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mohammad Rafiq at Oct 3, 2002 at 3:43 pm
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com
    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gene Sais at Oct 3, 2002 at 3:58 pm
    What does your utl_file_dir parameter look like in your init.ora? I am using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com
    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,
    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.
    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.
    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE

    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT

    EN TO.

    Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mohammad Rafiq at Oct 3, 2002 at 5:13 pm
    Like these

    utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
    utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata

    and these are the last entries in initSID.ora file.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 07:58:38 -0800

    What does your utl_file_dir parameter look like in your init.ora? I am
    using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L

    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,

    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.

    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.

    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.

    Begin

    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
    EN TO.
    -- Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gene Sais at Oct 3, 2002 at 7:13 pm
    hmm, when you go into svrmgrl connect internal and show parameters, do both show up or just the last one? on my system, i only see the last.
    rafiq9857_at_hotmail.com 10/03/02 01:13PM >>>
    Like these

    utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
    utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata

    and these are the last entries in initSID.ora file.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 07:58:38 -0800

    What does your utl_file_dir parameter look like in your init.ora? I am
    using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L

    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,

    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.

    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.

    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.

    Begin

    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
    EN TO.
    -- Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mohammad Rafiq at Oct 3, 2002 at 8:03 pm
    Here you are right. You will see here only one because of width. Please
    don't rely on svrmgrl for such info. Instead use sqlplus and check it from
    v$parameter

    result is
    PARAMETER

    VALUE

    utl_file_dir
    /u327/applmgr/10_7/finprod_output/hfs_data,
    /u327/applmgr/10_7/har/1.0.0/mm_cbda
    ta

    Both entries are appearing here,

    HTH

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 11:13:42 -0800

    hmm, when you go into svrmgrl connect internal and show parameters, do both
    show up or just the last one? on my system, i only see the last.
    rafiq9857_at_hotmail.com 10/03/02 01:13PM >>>
    Like these

    utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
    utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata

    and these are the last entries in initSID.ora file.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 07:58:38 -0800

    What does your utl_file_dir parameter look like in your init.ora? I am
    using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L

    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,

    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.

    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.

    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
    EN TO.
    -- Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');



    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Join the world’s largest e-mail service with MSN Hotmail.
    http://www.hotmail.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gene Sais at Oct 3, 2002 at 8:45 pm
    so right you are! i just tested in sqlplus and it shows both. thanks. one thing noticed, is that if you include another file w/ utl_file_dir setting to something, it ignores the original setting, i.e. part of my problem.

    all my initsid.ora files include a common 8i init.ora. in the common init.ora file i have this set, but it gets reset not appended to when i set utl_file_dir it in the initsid.ora file. thanks again.
    rafiq9857_at_hotmail.com 10/03/02 04:03PM >>>
    Here you are right. You will see here only one because of width. Please
    don't rely on svrmgrl for such info. Instead use sqlplus and check it from
    v$parameter

    result is
    PARAMETER

    VALUE

    utl_file_dir
    /u327/applmgr/10_7/finprod_output/hfs_data,
    /u327/applmgr/10_7/har/1.0.0/mm_cbda
    ta

    Both entries are appearing here,

    HTH

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 11:13:42 -0800

    hmm, when you go into svrmgrl connect internal and show parameters, do both
    show up or just the last one? on my system, i only see the last.
    rafiq9857_at_hotmail.com 10/03/02 01:13PM >>>
    Like these

    utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
    utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata

    and these are the last entries in initSID.ora file.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 07:58:38 -0800

    What does your utl_file_dir parameter look like in your init.ora? I am
    using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L

    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,

    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.

    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.

    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In Varchar2,

    P_Load_Password In Varchar2,

    P_Load_Service_Name In Varchar2,

    P_Load_Par_File_Dir In Varchar2,

    P_Load_Data_File_Dir In Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In Varchar2,

    P_Load_Bad_File_Dir In Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; -- Local
    variable to cc:

    Subject:
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
    EN TO.
    -- Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');



    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------/www.o
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Join the world's largest e-mail service with MSN Hotmail.
    http://www.hotmail.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mohammad Rafiq at Oct 3, 2002 at 9:11 pm
    I am reproducing below an email from John Kanagaraj for your info..
    It might help you further...

    Regards
    Rafiq

    Date: Thu, 15 Aug 2002 00:08:35 -0800

    Kathy,

    This is probably not documented, but you can have multiple lines of
    UTL_FILE_DIR _anywhere_ as _long_as_ you bunch them all up without any
    gaps
    / lines inbetween them (in which case the last set overrides the
    previous
    ones). I have the following in a 7.3.4 Db, all at the end (and I bet
    that
    you are recognizing your favorite ERP system therein - Oops my version
    is
    showing :)

    #
    # ******** VERY VERY IMPORTANT ***********
    # *** Keep ALL lines for UTL_FILE_DIR ***
    # *** together at the end of the file ***
    # *** Otherwise only the last set is ***
    # *** effective ***
    # ******** VERY VERY IMPORTANT ***********
    #
    utl_file_dir = /u001/app/smartdb/files/data/AR
    utl_file_dir = /u01/home/arftp/edi
    utl_file_dir = /u01/home/xxusc4xx
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/error
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/log

    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/processed
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware

    I believe you will have to either name specific directories or use a
    single
    '*' (the latter is _not_ a good idea!)

    John Kanagaraj
    Oracle Applications DBA
    DB Soft Inc
    Work : (408) 970 7002

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 12:45:17 -0800

    so right you are! i just tested in sqlplus and it shows both. thanks. one
    thing noticed, is that if you include another file w/ utl_file_dir setting
    to something, it ignores the original setting, i.e. part of my problem.

    all my initsid.ora files include a common 8i init.ora. in the common
    init.ora file i have this set, but it gets reset not appended to when i set
    utl_file_dir it in the initsid.ora file. thanks again.
    rafiq9857_at_hotmail.com 10/03/02 04:03PM >>>
    Here you are right. You will see here only one because of width. Please
    don't rely on svrmgrl for such info. Instead use sqlplus and check it from
    v$parameter

    result is
    PARAMETER

    VALUE

    utl_file_dir
    /u327/applmgr/10_7/finprod_output/hfs_data,
    /u327/applmgr/10_7/har/1.0.0/mm_cbda
    ta

    Both entries are appearing here,

    HTH

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 11:13:42 -0800

    hmm, when you go into svrmgrl connect internal and show parameters, do both
    show up or just the last one? on my system, i only see the last.
    rafiq9857_at_hotmail.com 10/03/02 01:13PM >>>
    Like these

    utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
    utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata

    and these are the last entries in initSID.ora file.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 07:58:38 -0800

    What does your utl_file_dir parameter look like in your init.ora? I am
    using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L

    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,

    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.

    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.

    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In

    Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In

    Varchar2,

    P_Load_Password In

    Varchar2,

    P_Load_Service_Name In

    Varchar2,

    P_Load_Par_File_Dir In

    Varchar2,

    P_Load_Data_File_Dir In

    Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In

    Varchar2,

    P_Load_Bad_File_Dir In

    Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; --
    Local
    variable to cc:

    Subject:
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
    EN TO.
    -- Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------/www.o
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Join the world's largest e-mail service with MSN Hotmail.
    http://www.hotmail.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gene Sais
    INET: Gsais_at_co.palm-beach.fl.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Send and receive Hotmail on your mobile device: http://mobile.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jared.Still_at_radisys.com at Oct 3, 2002 at 10:13 pm
    It is documented:

    http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90190/ch1219.htm#945570

    "Mohammad Rafiq"
    Sent by: root_at_fatcity.com
    10/03/2002 02:11 PM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    I am reproducing below an email from John Kanagaraj for your info..
    It might help you further...

    Regards
    Rafiq

    Date: Thu, 15 Aug 2002 00:08:35 -0800

    Kathy,

    This is probably not documented, but you can have multiple lines of
    UTL_FILE_DIR _anywhere_ as _long_as_ you bunch them all up without any
    gaps
    / lines inbetween them (in which case the last set overrides the
    previous
    ones). I have the following in a 7.3.4 Db, all at the end (and I bet
    that
    you are recognizing your favorite ERP system therein - Oops my version
    is
    showing :)

    #
    # ******** VERY VERY IMPORTANT ***********
    # *** Keep ALL lines for UTL_FILE_DIR ***
    # *** together at the end of the file ***
    # *** Otherwise only the last set is ***
    # *** effective ***
    # ******** VERY VERY IMPORTANT ***********
    #
    utl_file_dir = /u001/app/smartdb/files/data/AR
    utl_file_dir = /u01/home/arftp/edi
    utl_file_dir = /u01/home/xxusc4xx
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/error
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/log

    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/processed
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware

    I believe you will have to either name specific directories or use a
    single
    '*' (the latter is _not_ a good idea!)

    John Kanagaraj
    Oracle Applications DBA
    DB Soft Inc
    Work : (408) 970 7002

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 12:45:17 -0800

    so right you are! i just tested in sqlplus and it shows both. thanks. one

    thing noticed, is that if you include another file w/ utl_file_dir setting

    to something, it ignores the original setting, i.e. part of my problem.

    all my initsid.ora files include a common 8i init.ora. in the common
    init.ora file i have this set, but it gets reset not appended to when i
    set
    utl_file_dir it in the initsid.ora file. thanks again.
    rafiq9857_at_hotmail.com 10/03/02 04:03PM >>>
    Here you are right. You will see here only one because of width. Please
    don't rely on svrmgrl for such info. Instead use sqlplus and check it from
    v$parameter

    result is
    PARAMETER

    VALUE

    utl_file_dir
    /u327/applmgr/10_7/finprod_output/hfs_data,
    /u327/applmgr/10_7/har/1.0.0/mm_cbda
    ta

    Both entries are appearing here,

    HTH

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 11:13:42 -0800

    hmm, when you go into svrmgrl connect internal and show parameters, do
    both
    show up or just the last one? on my system, i only see the last.
    rafiq9857_at_hotmail.com 10/03/02 01:13PM >>>
    Like these

    utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
    utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata

    and these are the last entries in initSID.ora file.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 07:58:38 -0800

    What does your utl_file_dir parameter look like in your init.ora? I am
    using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep
    gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested
    below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed.
    Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L

    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,

    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.

    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.

    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In

    Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In

    Varchar2,

    P_Load_Password In

    Varchar2,

    P_Load_Service_Name In

    Varchar2,

    P_Load_Par_File_Dir In

    Varchar2,

    P_Load_Data_File_Dir In

    Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In

    Varchar2,

    P_Load_Bad_File_Dir In

    Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; --
    Local
    variable to cc:

    Subject:
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
    EN TO.
    -- Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mohammad Rafiq at Oct 3, 2002 at 10:34 pm
    Jared,
    Thanks..very nice.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 14:13:46 -0800

    It is documented:

    http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90190/ch1219.htm#945570

    "Mohammad Rafiq"
    Sent by: root_at_fatcity.com
    10/03/2002 02:11 PM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Utl_file and OPENVMS

    I am reproducing below an email from John Kanagaraj for your info..
    It might help you further...

    Regards
    Rafiq

    Date: Thu, 15 Aug 2002 00:08:35 -0800

    Kathy,

    This is probably not documented, but you can have multiple lines of
    UTL_FILE_DIR _anywhere_ as _long_as_ you bunch them all up without any
    gaps
    / lines inbetween them (in which case the last set overrides the
    previous
    ones). I have the following in a 7.3.4 Db, all at the end (and I bet
    that
    you are recognizing your favorite ERP system therein - Oops my version
    is
    showing :)

    #
    # ******** VERY VERY IMPORTANT ***********
    # *** Keep ALL lines for UTL_FILE_DIR ***
    # *** together at the end of the file ***
    # *** Otherwise only the last set is ***
    # *** effective ***
    # ******** VERY VERY IMPORTANT ***********
    #
    utl_file_dir = /u001/app/smartdb/files/data/AR
    utl_file_dir = /u01/home/arftp/edi
    utl_file_dir = /u01/home/xxusc4xx
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/error
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/log

    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/processed
    utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware

    I believe you will have to either name specific directories or use a
    single
    '*' (the latter is _not_ a good idea!)

    John Kanagaraj
    Oracle Applications DBA
    DB Soft Inc
    Work : (408) 970 7002

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 12:45:17 -0800

    so right you are! i just tested in sqlplus and it shows both. thanks. one

    thing noticed, is that if you include another file w/ utl_file_dir setting

    to something, it ignores the original setting, i.e. part of my problem.

    all my initsid.ora files include a common 8i init.ora. in the common
    init.ora file i have this set, but it gets reset not appended to when i
    set
    utl_file_dir it in the initsid.ora file. thanks again.
    rafiq9857_at_hotmail.com 10/03/02 04:03PM >>>
    Here you are right. You will see here only one because of width. Please
    don't rely on svrmgrl for such info. Instead use sqlplus and check it from
    v$parameter

    result is
    PARAMETER

    VALUE

    utl_file_dir
    /u327/applmgr/10_7/finprod_output/hfs_data,
    /u327/applmgr/10_7/har/1.0.0/mm_cbda
    ta

    Both entries are appearing here,

    HTH

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 11:13:42 -0800

    hmm, when you go into svrmgrl connect internal and show parameters, do
    both
    show up or just the last one? on my system, i only see the last.
    rafiq9857_at_hotmail.com 10/03/02 01:13PM >>>
    Like these

    utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
    utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata

    and these are the last entries in initSID.ora file.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 07:58:38 -0800

    What does your utl_file_dir parameter look like in your init.ora? I am
    using AIX.
    rafiq9857_at_hotmail.com 10/03/02 11:43AM >>>
    What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
    problem with separate entries as mentioned by Jared. However, don't keep
    gap
    between these entries...and define it the way it was explained by Jared.

    Regards
    Rafiq

    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Date: Thu, 03 Oct 2002 05:28:22 -0800

    On unix, multiple entries in the init.ora file results in the last entry
    being the only valid value (i.e. last time variable is set). I tested
    below
    and only dir3 is listed as a utl_file_dir parameter. But if you comma
    delimit them w/ 1 instance of the variable then all dir's are listed.
    Maybe
    NT is different, fortunately never had to support Oracle on NT :).

    Gene
    Jared.Still_at_radisys.com 10/02/02 07:13PM >>>
    Gene,
    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3
    The multiple lines shown are actually the documented method for doing
    this.

    The single line with comma delimited entries may also work, though I'm
    not sure about it.

    Jared

    "Gene Sais"
    Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

    To: Multiple recipients of list ORACLE-L

    cc:
    Subject: RE: Utl_file and OPENVMS

    i believe its

    utl_file_dir=dir1,dir2,dir3,...

    in your example, dir 3 would be the only valid dir.

    hth,
    gene
    SXBaswan_at_dcss.com 10/01/02 12:53PM >>>
    Make sure you have the directory name entry covered in INIT.ORA

    e.g.

    utl_file_dir = D:\directory name1
    utl_file_dir = D:\directory name2
    utl_file_dir = D:\directory name3

    You need one entry per directory that you want to write to using UTL_FILE
    Package

    Hope this helps

    Regards

    Shiva

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 9:48 AM
    To: Multiple recipients of list ORACLE-L

    the vms user oracle needs rights to the directory.
    RROGERS_at_galottery.org 09/30/02 10:53AM >>>
    List,

    I have a package that creates files on the server. The directory
    location and file name are obtained from tables in oracle. The procedure
    works as designed on Novell 7.3.4 and no changes were needed when the
    database way loaded on Linux Oracle 8.1.7. I am trying to move the
    database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
    get the package to write the files to the OS directory.

    The package is created by the Oracle user DTSUSER and executed by
    DTSUSER. There is no OPENVMS user DTSUSER.
    The sysadmin assures me that the permissions are correct to write to
    the directory.
    I have place a Dbms_output in the package to display the directory
    information and it looks correct.

    Is there anything different that has to be done to an OPENVMS server
    that will allow a package to write to a directory using the Utl_File
    package?

    Listing from the Oracle tables:

    DTS_PARAMETER_NAME DTS_PARAMETER_VALUE

    ------------------------------

    LOAD_USERID LOADITUP

    LOAD_PASSWORD ILOADIT

    LOAD_SERVICE_NAME GLC_ALPHADEV-TCP

    LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]

    LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]

    LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]

    LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]

    LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]

    LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]

    Listing from the package that writes the parameter file:

    Procedure Create_New_Par_File

    (

    P_Current_Table_Name In

    Varchar2,

    P_Run_Date In Date,

    P_Load_Userid In

    Varchar2,

    P_Load_Password In

    Varchar2,

    P_Load_Service_Name In

    Varchar2,

    P_Load_Par_File_Dir In

    Varchar2,

    P_Load_Data_File_Dir In

    Varchar2,

    P_Load_Control_File_Dir In Varchar2,

    P_Load_Log_File_Dir In

    Varchar2,

    P_Load_Bad_File_Dir In

    Varchar2,

    P_Load_Discard_File_Dir In Varchar2

    )

    as

    Begin

    Declare

    L_Par_File_Hand Utl_FIle.File_Type; --
    Local
    variable to cc:

    Subject:
    hold the File Pointer for the parameter file.

    Begin
    I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
    PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
    EN TO.
    -- Open a new parameter file

    L_Par_File_Hand :=

    Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');

    Print the following lines into the parameter file.

    Utl_File.Put

    (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
    _Service_Name);

    Utl_File.New_Line(L_Par_File_Hand);

    If Not P_Current_Table_Name = 'GLCRET'

    Then

    Utl_File.Put

    (L_Par_File_Hand,'Errors=1');

    Else

    Utl_File.Put

    (L_Par_File_Hand,'Errors=50');

    End If;

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
    ame||'.CTL');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.LOG');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
    char(P_Run_Date,'mmdd')||'.BAD');

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
    ame||to_char(P_Run_Date,'mmdd')||'.DSC');

    as

    Utl_File.New_Line(L_Par_File_Hand);

    Utl_File.Put

    (L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
    /'||

    P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');

    Close the file after printing.

    Utl_File.Fclose(L_Par_File_Hand);

    Exception

    When Others then

    Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);

    End;

    Thanks, Any assistance would be appreciated. I am just getting in to
    the OPENVMS OS.
    Ron
    ROR mª¿ªm

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ron Rogers
    INET: RROGERS_at_galottery.org

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    _________________________________________________________________
    Chat with friends online, try MSN Messenger: http://messenger.msn.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mohammad Rafiq
    INET: rafiq9857_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 1, '02 at 2:48p
activeOct 3, '02 at 10:34p
posts17
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase