Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    11

    Unanswered: SPOOL only DBMS_OUTPUT and display only oracle error

    Hi All,

    I have a very simple requirement.

    I want to spool only the output of DBMS_OUTPUT.PUT_LINE to a file but the same shouldn't be displayed on the screen.

    I have already achieved this by using the following configuration:
    set termout off
    set serveroutput on

    But the problem with the above configuration is that, it'll suppress the oracle error also if any.
    I mean oracle errors will not be displayed on the console and I'm not able to capture them.

    If I set termout on, then it would give me the dbms_output as well on the console, which I don't want.

    How can I capture the oracle errors on the console if any and the dbms_output in a file?
    Is there any configuration that could give me the desired result?

    Can I make use of SHOW ERRORS somewhere in this case?

    Thanks in advance for your feedback.

    Regards,
    Supreeth K

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Capture error in the 'EXCEPTION' section and display using DBMS_OUTPUT.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2013
    Posts
    11
    Yes, I can do that but in that case the error would also go into my spool file which I don't want

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about logging errors into a table (EXCEPTION section would perform INSERT INTO), and - later - reviewing what's in there? I'd probably go for an autonomous transaction procedure.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Supreeth K View Post
    Yes, I can do that but in that case the error would also go into my spool file which I don't want
    Why not? if there is an error, your spool file may be also unusable.

    Or use UTL_FILE for your output file and remove the DBMS_OUTPUT restrictions (set termout on).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jul 2013
    Posts
    11
    Quote Originally Posted by Littlefoot View Post
    How about logging errors into a table (EXCEPTION section would perform INSERT INTO), and - later - reviewing what's in there? I'd probably go for an autonomous transaction procedure.
    Yes, I can do that but my requirement is simple. I just want to display the error on console. I can't spool the same to a log file as I'm already spooling some data into another file.

    Regards,
    Supreeth K

  7. #7
    Join Date
    Jul 2013
    Posts
    11
    Quote Originally Posted by LKBrwn_DBA View Post
    Why not? if there is an error, your spool file may be also unusable.

    Or use UTL_FILE for your output file and remove the DBMS_OUTPUT restrictions (set termout on).
    Hi,

    Yes, I agree with your statement. But I'm deleting the spool file if the operation isn't successful. Besides, I'm maintaining a different log file created in UNIX but unfortunately I can't spool the error to this file as I'm already spooling another file.
    So, I just want to display the error on console if any.

    one more question that arises in my mind: "is it possible to use nested spool and if the spool can be used within PL SQL block (I know this isn't supported)?"

    I started scripting using UTL_FILE package only but the system setup is such that the UNIX and DB resides on different servers. Hence, I can't use UTL_FILE as it can write only to a file which is local to the server else the access will be denied by the OS.

    Regards,
    Supreeth K

Posting Permissions

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