Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unhappy Unanswered: How to create an error.log or any other file?

    Hello All!

    I have a very trivial question:
    how can i create a file, directory with PL/SQL?

    It would be great if somebody could give me a simple example!

    Thanks in advance!
    Last edited by julla27; 05-14-04 at 09:55.
    Regards,
    Julia

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Code:
    DECLARE
    	lfFile  UTL_FILE.FILE_TYPE;
    
    	lspath varchar2(50) := 'c:\';
    	lsfile varchar2(50) := 'craw';
    
    BEGIN
    	lfFile  := UTL_FILE.FOPEN(lspath,lsfile||'.csv','w',32767);
    
    	UTL_FILE.PUT_LINE(lfFile,'Hello!');
    
    	UTL_FILE.FCLOSE_ALL;
    EXCEPTION
    	WHEN UTL_FILE.INVALID_OPERATION THEN
    		UTL_FILE.FCLOSE_ALL;
    		RAISE_APPLICATION_ERROR(-20051,'Invalid File open Operation');
    
    	WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    		UTL_FILE.FCLOSE_ALL;
    		RAISE_APPLICATION_ERROR(-20052,'Invalid File Name');
    
    	WHEN UTL_FILE.READ_ERROR THEN
    		UTL_FILE.FCLOSE_ALL;
    		RAISE_APPLICATION_ERROR(-20053,'Read Error');
    END;
    you will need utl_file_dir = * in the init.ora, or you can specify directories rather than just *
    There are 10 types of people in the world, those that know Binary and those that don't.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thanks you, Robert for your code!

    Thank you, anacedent, for the links!

    --------------------------------------------------------------------
    Quote Originally Posted by robert xr4x4
    Code:
    DECLARE
    	lfFile  UTL_FILE.FILE_TYPE;
    
    	lspath varchar2(50) := 'c:\';
    	lsfile varchar2(50) := 'craw';
    
    BEGIN
    	lfFile  := UTL_FILE.FOPEN(lspath,lsfile||'.csv','w',32767);
    
    	UTL_FILE.PUT_LINE(lfFile,'Hello!');
    
    	UTL_FILE.FCLOSE_ALL;
    EXCEPTION
    	WHEN UTL_FILE.INVALID_OPERATION THEN
    		UTL_FILE.FCLOSE_ALL;
    		RAISE_APPLICATION_ERROR(-20051,'Invalid File open Operation');
    
    	WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    		UTL_FILE.FCLOSE_ALL;
    		RAISE_APPLICATION_ERROR(-20052,'Invalid File Name');
    
    	WHEN UTL_FILE.READ_ERROR THEN
    		UTL_FILE.FCLOSE_ALL;
    		RAISE_APPLICATION_ERROR(-20053,'Read Error');
    END;
    you will need utl_file_dir = * in the init.ora, or you can specify directories rather than just *

    But executing your code i've got the following error_message:

    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "SYS.UTL_FILE", line 98
    ORA-06512: at "SYS.UTL_FILE", line 157
    ORA-06512: at line 10


    UTL_FILE
    PHP Code:
    --> /96/:  PROCEDURE get_line(file   IN file_type,
          /
    97/:               buffer OUT VARCHAR2);
          /
    98/:  PRAGMA RESTRICT_REFERENCES(get_lineWNDSRNDSWNPSRNPS);


    --> FUNCTION 
    fopen(location     IN VARCHAR2,
                     
    filename     IN VARCHAR2,
                     
    open_mode    IN VARCHAR2,
                     
    max_linesize IN BINARY_INTEGER) RETURN file_type;
      
    PRAGMA RESTRICT_REFERENCES(fopenWNDSRNDS); 

    (I'm working in a tablespace of Oracle 8.1.7.-database)

    Why?

    Thanks in advance!
    Last edited by julla27; 05-13-04 at 13:23.
    Regards,
    Julia

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    the code runs on mine so no probs there

    is this in your init.ora file : utl_file_dir = *
    and have you restarted the database since adding it

    try adding
    WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    UTL_FILE.FCLOSE_ALL;
    and see if it gives you a better error
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DECLARE
    report_file UTL_FILE.FILE_TYPE;
    filename VARCHAR2(127);
    BEGIN
    dbms_output.enable(1000000);
    filename := sysdba.unique_filename('tab_col_diffs');
    report_file := UTL_FILE.FOPEN('/tmp',filename , 'W');
    UTL_FILE.PUT_LINE(report_file,'Subject: Table column differences ');
    UTL_FILE.NEW_LINE(report_file,2);
    UTL_FILE.PUT_LINE(report_file,'TABLE_NAME COLUMN_NAME TYPE');
    UTL_FILE.FCLOSE(report_file);
    EXCEPTION
    WHEN UTL_FILE.INVALID_PATH
    THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_path'); RAISE;
    WHEN UTL_FILE.INVALID_MODE
    THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_mode'); RAISE;
    WHEN UTL_FILE.INVALID_FILEHANDLE
    THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_filehandle'); RAISE;
    WHEN UTL_FILE.INVALID_OPERATION
    THEN
    DBMS_OUTPUT.PUT_LINE ('invalid_operation'); RAISE;
    WHEN UTL_FILE.READ_ERROR
    THEN
    DBMS_OUTPUT.PUT_LINE ('read_error'); RAISE;
    WHEN UTL_FILE.WRITE_ERROR
    THEN
    DBMS_OUTPUT.PUT_LINE ('write_error'); RAISE;
    WHEN UTL_FILE.INTERNAL_ERROR
    THEN
    DBMS_OUTPUT.PUT_LINE ('internal_error'); RAISE;
    end;
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thanks to you both!


    1) To Robert:
    i cannot find init.ora on my PC!Where should i search it?


    2) To Anacedent :

    Compiling your code i've got such error:

    ORA-06550: line 6, column 13:
    PLS-00201: identifier 'SYSDBA.UNIQUE_FILENAME' must be declared
    ORA-06550: line 6, column 1:
    PL/SQL: Statement ignored

    I have just Public privileeges.
    Regards,
    Julia

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >PLS-00201: identifier 'SYSDBA.UNIQUE_FILENAME' must be declared
    This is a local procedure which just generates a unique filename.
    You can use any filename you want.
    Most importantly, use the EXCEPTION section to trap & report errors.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    it will be on the oracle server (unless you have oracle running on your machine) and typically c:\oracle\admin\<SID>\pfile\init<SID>.ora
    There are 10 types of people in the world, those that know Binary and those that don't.

  10. #10
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Quote Originally Posted by julla27
    Thanks to you both!


    1) To Robert:
    i cannot find init.ora on my PC!Where should i search it?


    2) To Anacedent :

    Compiling your code i've got such error:

    ORA-06550: line 6, column 13:
    PLS-00201: identifier 'SYSDBA.UNIQUE_FILENAME' must be declared
    ORA-06550: line 6, column 1:
    PL/SQL: Statement ignored

    I have just Public privileeges.
    Julia,

    from the instance you want to know which pfile uses:

    SQL>show parameter pfile

    NAME TYPE VALUE
    -------- ----------- ----------------------------------------------
    spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA



    HTH,

    clio_usa - OCP 8/8i/9i DBA

  11. #11
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thanks to All for your help!

    1) to Robert:
    i added
    PHP Code:
    WHEN OTHERS THEN
     dbms_output
    .put_line(sqlerrm);
     
    UTL_FILE.FCLOSE_ALL
    Now no error occurs, but also no file.

    2) to clio:
    Calling:
    show parameter pfile
    i get
    ORA-00900: invalid SQL statement


    What can i do?
    Thanks in advance for your help!
    Regards,
    Julia

  12. #12
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Quote Originally Posted by julla27
    1) to Robert:
    i added
    PHP Code:
    WHEN OTHERS THEN
     dbms_output
    .put_line(sqlerrm);
     
    UTL_FILE.FCLOSE_ALL
    Now no error occurs, but also no file.
    you do realise that the file will be created on the c drive of the oracle server and not your client machine (assuming they are different)
    There are 10 types of people in the world, those that know Binary and those that don't.

  13. #13
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    you may be able to find where the file is by running SELECT VALUE FROM V$PARAMETER WHERE NAME = 'ifile' .... I am on 9i but I assume it works on 8i
    There are 10 types of people in the world, those that know Binary and those that don't.

  14. #14
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    How can i correct it?
    Regards,
    Julia

  15. #15
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unhappy

    Hello All!

    If i understood correct, i have to alter the file init.ora.
    But i cannot find it.
    I use throught the Oracle-DB-Server a tablespace of a great Oracle 8.1.7.-database. So i think the init.ora-file is situated on this server. Also i don't think that i may to change the configurations of this file.

    What can i do? Do i have some alternatives?


    Thanks!
    Last edited by julla27; 05-14-04 at 09:54.
    Regards,
    Julia

Posting Permissions

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