3 Replies Latest reply: Sep 16, 2013 10:23 PM by JustinCave RSS

    UTL_FILE Usage

    Comet

      I am working in the following Oracle environment:

      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0

      Unix: Solaris Sunsparc 10

      Oracle Financials: 12.1.3

       

      I defined a stored procedure seen below.  I am trying to create a CSV file and write it to a directory on my C: drive (ultimately in a UNIX directory)  I can't issue a CREATE DIRECTORY from within the stored procedure (or can I) so how do I specify the path to the directory c;\? What avialable paths do I have from Windows 2007?

       

      create or replace
      Procedure ZZAP_REMEDY_EXPORT_PROC
      (P_Folder_Name In Varchar2
      ,P_File_name In Varchar2 
      )
      AS
      --    F := UTL_FILE.FOPEN('MYCSV','REMEDY_AP_EXPORT.CSV','w',32767);

      ---Declare
         Exp_A Exception;
        
         Errbuf Varchar2(120);
         V_Errloc VARCHAR2(120);Count number;
         Retcode Varchar2(120);
         Counter Number;
         F UTL_FILE.FILE_TYPE;
      Begin
          Counter := 0;
          F := Utl_File.Fopen('MYCSV','REMEDY_AP_EXPORT.CSV','w',32767);
      --    F := UTL_FILE.FOPEN('MYCSV','REMEDY_AP_EXPORT.CSV','w',32767);
          Loop
              Utl_File.Put(F,'123456');
              Utl_File.Put(F,',A' );
              Utl_File.Put(F,',1050 MAIN' );
              Utl_File.Put(F,',BRONX' );
              UTL_FILE.PUT(F,',NY' );
              Utl_File.New_Line(F);
              Exit When Counter = 10;
              counter := counter + 1;
          END LOOP;
          Utl_File.Fclose(F);

      Exception
      WHEN OTHERS THEN
         errbuf := 'Error:- ' || SQLCODE ||' '|| SUBSTR(SQLERRM, 1, 950) ;
         Fnd_File.Put_Line(Fnd_File.Log, 'Exception -> Err Code: ' || To_Char(Sqlcode) || '(' || Rtrim(Sqlerrm) || ') occured in procedure Inactivate item at location '|| V_Errloc);
         Retcode := 2;
      End Zzap_Remedy_Export_Proc;

        • 1. Re: UTL_FILE Usage
          JustinCave

          What do you mean by "my C: drive (ultimately in a UNIX directory)"?

           

          Are you trying to write data to a file on the client?  Or on the server?  UTL_FILE can only write to directories on the server's file system.

          I'm not sure how the C:\ drive, a Windows file system identifier, can be a Unix directory.  You're also talking about both Solaris and Windows 2008 (I'm guessing, I don't think there is a Windows 2007.  There is a Windows 7).

           

          Justin

          • 2. Re: UTL_FILE Usage
            Comet

            Hi Justin: I forgot to mention I am developing the stored procedure through SQL Developer.  I am trying to create a shell program for creating the CSV file and then integrate the cursor definition later.  I want something to write to my local windows drive if possible so I can validate the logic.  Eventually it wikll run as a concurrent process in Oracle Finacials and create the output file on the Unix server.  For now I want to create the file locally.

            • 3. Re: UTL_FILE Usage
              JustinCave

              UTL_FILE can only create files on the database server.

               

              In SQL Developer, you can run a query, fetch the results, and then tell SQL Developer to write them to a CSV file on your local drive.  It's not obvious to me why you would want to do that, though, since obviously your code may be quite different from the code SQL Developer uses to write the file.  And it would seem easier to review the results of your query in SQL Developer rather than writing it to a separate file just to use some other tool to open the file.  Perhaps you want SQL Developer to write an XLS[X] file of results that you can then use Excel to view?

               

              Justin