Re: Q:PL/SQL How to create a File ..

From: David <darussell_at_msn.com>
Date: Sun, 5 Jul 1998 19:31:58 +0100
Message-ID: <e9WkEMEq9GA.208@upnetnews05>

Check out the Oracle8 Server Application Developers Guide : 12-25 through 12-37, you should have a copy of this in the online documentation. Or see example below :

CREATE OR REPLACE PACKAGE Debug AS
  /* Global variables to hold the name of the debugging file and

     directory. */
  v_DebugDir VARCHAR2(50);
  v_DebugFile VARCHAR2(20);

  PROCEDURE Debug(p_Description IN VARCHAR2,
                  p_Value IN VARCHAR2);
  PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,
                  p_NewDir IN VARCHAR2 := v_DebugDir) ;

  /* Closes the debugging file. */
  PROCEDURE Close;
END Debug;
/
CREATE OR REPLACE PACKAGE BODY Debug AS

  v_DebugHandle UTL_FILE.FILE_TYPE;
  PROCEDURE Debug(p_Description IN VARCHAR2,
                  p_Value IN VARCHAR2) IS

  BEGIN
    /* Output the info, and flush the file. */     UTL_FILE.PUTF(v_DebugHandle, '%s: %s\n', p_Description, p_Value);     UTL_FILE.FFLUSH(v_DebugHandle);
  EXCEPTION
    WHEN UTL_FILE.INVALID_OPERATION THEN

      RAISE_APPLICATION_ERROR(-20102,
                              'Debug: Invalid Operation');
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR(-20103,
                              'Debug: Invalid File Handle');
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR(-20104,
                              'Debug: Write Error');

  END Debug;
  PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,

                  p_NewDir IN VARCHAR2 := v_DebugDir) IS

  BEGIN
    /* Make sure the file is closed first. */     IF UTL_FILE.IS_OPEN(v_DebugHandle) THEN       UTL_FILE.FCLOSE(v_DebugHandle);
    END IF;
    /* Open the file for writing. */
    v_DebugHandle := UTL_FILE.FOPEN(p_NewDir, p_NewFile, 'w');     /* Set the packaged variables to the values just passed in. */     v_DebugFile := p_NewFile;
    v_DebugDir := p_NewDir;
  EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR(-20100, 'Reset: Invalid Path');     WHEN UTL_FILE.INVALID_MODE THEN
      RAISE_APPLICATION_ERROR(-20101, 'Reset: Invalid Mode');     WHEN UTL_FILE.INVALID_OPERATION THEN       RAISE_APPLICATION_ERROR(-20101, 'Reset: Invalid Operation');   END Reset;
  PROCEDURE Close IS
  BEGIN
    UTL_FILE.FCLOSE(v_DebugHandle);
  END Close;
BEGIN
  v_DebugDir := '/tmp';
  v_DebugFile := 'debug.out';
  Reset;
END Debug;
/

Regards
Dvid Russell
Ralf Bender wrote in message <359f8553.0_at_dns.wolnet.de>...
>Hallo *.*
>Please help an Oracle Beginner.
>Can someone tell me how to create a file with a PL/SQL script.
>Spool c:\test.txt will not work ??
>Somebody told about utlfile.sql, but do not know how to use it.
>
>It was nice if you can declare what you doing.
>
>Here is what I want to do :
>create or replace procedure do_update as
>iScan number;
>iTempax number;
>begin
> select count(*)
> into iScan
> from scan
> where to_date(timestamp,'dd.mm.yyyy') = to_date(sysdate,'dd.mm.yyyy');
>
> select count(*)
> into iTempax
> from Tempax;
>
> if iScan = iTempax then
> mailax2fibu;
> else
> -- Create a File with something in
> end if;
>end;
>
>I work with Oracle8 on NT4.
>
>bye
>Ralf
>
>mailto:Ralf.Bender_at_wolnet.de
>
>
Received on Sun Jul 05 1998 - 13:31:58 CDT