Forum Stats

  • 3,758,942 Users
  • 2,251,479 Discussions
  • 7,870,440 Comments

Discussions

Suppress query results unless errors exist

randoogle
randoogle Member Posts: 6 Blue Ribbon
edited Jul 11, 2018 1:43AM in SQLcl

I've looked around, and it doesn't currently seem possible to suppress query results AND still see any possible errors.

For example, say I have a query that I run from a bash script that extracts a certain population and spools this information into a CSV file. I want output any possible errors in a separate log file, but I don't want to log all the data that got extracted.

The closest I can get is to simply set termout off, but this also excludes any ORA error messages, which are very helpful in determining what might be wrong. The other option is to just log EVERYTHING, but some extracts can be pretty big, and it seems like a waste of time and space to do.

First, I'm wondering, has anyone has found a workable solution to this?

Second, if there's no way currently, is there any way we could see it added to sqlcl?

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jul 10, 2018 5:45PM

    If you invoke the WHENEVER command (say, WHENEVER SQLERROR EXIT SQL.SQLCODE) prior to running one or more queries, then you will exit SQLcl upon encountering the first error, with any error messages going both to the spool file and to the console.  Does that help?

    Since the documentation for WHENEVER in SQLcl is quite brief, you can always search for WHENEVER examples in the sqlplus doc as an alternative and hope the implementation is similar for both command line tools.

    Cheers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jul 11, 2018 1:43AM
    randoogle wrote:I've looked around, and it doesn't currently seem possible to suppress query results AND still see any possible errors.

    If your query runs and produces output, what errors are you getting that you want to log elsewhere?

    Perhaps using an AFTER SERVERERROR trigger will give you what you are looking for, to log the query and errors to a error log table:

    AFTER SERVERERRORCauses the database to fire the trigger whenever both of these conditions are true:    A server error message is logged.    Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers.    Examples of when it is unsafe to fire error triggers include:        RDBMS is starting up.        A critical error has occurred.

    ...or, if you are trying to debug your sql before "running" it, then sqlplus 12.2.0.1.0 has the "SET FEEDBACK ONLY" feature where it suppresses query results.

    ...or you could append "rownum = 1" to the query and see if the query gets parsed successfully.