Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: Writing to a file using PL/SQL

    Hi,

    I am trying write information to a text file using PL/SQL. I have the following code run from a post-text-item trigger:

    DECLARE

    firstline DATE := :TREATMENT.treatment_date;
    output_file utl_file.file_type;

    BEGIN

    output_file := utl_file.fopen ('C:/', 'drugDetails.txt', 'W');

    utl_file.put_line(output_file, firstline);
    utl_file.fclose(output_file);

    END;

    I recieve this error:
    FRM-40735: POST-TEXT-ITEM trigger raised unhandled exception ORA-06510

    Please can somebody tell me where im going wrong, or is there an easier way to go about it?

    Thank you for your time

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Add the following exception handler to the bottom of your code.
    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;
    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.

  3. #3
    Join Date
    Apr 2004
    Posts
    11
    I added the exception stuff to the bottom of my code and it still comes up with the same error code. I read about the init.ora file do i need to do anything with this?

  4. #4
    Join Date
    Nov 2003
    Posts
    65

    This might work

    I think i know where you were going wrong, I think it had to do with the way you were sending in your drive letter parameter. Also added another exception to throw out any others that might not be declared.
    Try the following:
    Code:
    DECLARE
    
    firstline DATE := :TREATMENT.treatment_date;
    output_file utl_file.file_type;
    
    BEGIN
    
    --
    -- Notice the drive letter is now C:\ and NOT C:/
    -- 
    output_file := utl_file.fopen ('C:\', 'drugDetails.txt', 'W');
    
    utl_file.put_line(output_file, firstline);
    utl_file.fclose(output_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;
    WHEN OTHERS THEN
    	DBMS_OUTPUT.PUT_LINE (sqlcode || ' --- ' || sqlerm); 
    	RAISE;
    
    END;
    Try it and let us know how it turns out.
    Hope that works for you.

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    ORA-6510 means "PL/SQL: unhandled user-defined exception" . You can set SET SERVEROUT ON and review all the error messages.

    Most probably the UTL_DIR variable is not setup properly. Try this:

    1. Shutdown the database.
    2. Add the following line to the init<sid>.ora

    utl_file_dir = <dictionary_location>

    example: utl_file_dir = 'c:\'

    3. Start database

    Then check the utl_dir value:

    SQL> set serverout on
    SQL> select value from v$parameter where name = 'utl_file_dir';


    Note: Each directory must be specified with a separate UTL_FILE_DIR paramater in the init<sid>.ora file.

    HTH,

    clio_usa - OCP 8/8i/9i DBA

  6. #6
    Join Date
    Apr 2004
    Posts
    11
    I added this line:
    utl_file_dir = 'c:\'
    to the init.ora file right at the end, does it matter where it goes?

    I then ran this code in SQL++ and recieved the following errors:
    SQL> set serverout on;
    SQL> select value from v$parameter where name = 'utl_file_dir';
    select value from v$parameter where name = 'utl_file_dir'
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    When i try to compile the PL/SQL code above i get this error:
    Error 201 at line 39, column 46
    identifier 'SQLERM' must be declared
    Error 0 line 39, column 2
    Statement ignored

    Where am i going wrong?

    Thank you for your time

  7. #7
    Join Date
    May 2004
    Location
    alabama
    Posts
    5
    aren't there two 'R's in 'SQLRRM'?

  8. #8
    Join Date
    Apr 2004
    Posts
    11
    Yeah your right. Changed sqlerm to sqlerrm and that now compiles. I now get this error:
    FRM-40734: internal Error: PL/SQL error occured

Posting Permissions

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