2 Replies Latest reply on Jul 11, 2018 5:43 AM by Gaz in Oz

    Suppress query results unless errors exist

    randoogle

      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?

        • 1. Re: Suppress query results unless errors exist
          Glen Conway

          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

          • 2. Re: Suppress query results unless errors exist
            Gaz in Oz

            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 SERVERERROR

            Causes 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.