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

Very good example by David, but most importantly you need to make sure that your DBA has set the
init<sid>.ora parameter UTL_FILE_DIR to some directory that you can write to. Without this parameter
being set properly, you will not be able to use the UTL_FILE package.

John M. Reed Jr.
DBA/Dev 2K Programmer

David wrote in message ...
>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 Mon Jul 06 1998 - 13:14:41 CDT