Suppress some output from PL/SQL script in Toad for Oracle

oracleplsqltoad

Question:
In PL/SQL for Oracle running in Toad, can I suppress script output except for certain lines I want to see? e.g. I don't want create table, insert record, drop table. I do want my own dbms_output.put_line messages.

Example:
Here's my current output file. (This is simplified but it shows the kinds of messages I'm getting.)

  PL/SQL procedure successfully completed.
 Commit complete.
 Table created.
 1 row created.
 1 row created.
 1 row created.
 Dropping table: ER_TEST001S_RES
 Checking results for test001s
         PASS: All records on _res expected results table have matches on actual results table for test001s
         PASS: All records on _res actual results table have matches on expected results table for test001s
  PL/SQL procedure successfully completed.
 Commit complete.

My preferred output would look like this.

 Checking results for test001s
         PASS: All records on _res expected results table have matches on actual results table for test001s
         PASS: All records on _res actual results table have matches on expected results table for test001s
 Checking results for test002s
         PASS: All records on _res expected results table have matches on actual results table for test002s
         PASS: All records on _res actual results table have matches on expected results table for test002s

Context:
I'm using PL/SQL to test a calculation program that runs as a database procedure. My script inserts test records, runs the calculation procedure on them, creates tables of expected results, and compares expected to actual results. (I drop/create the test objects and expected results tables on each test run to make it quick and easy to run all tests at once when moving from dev to test environments.) Currently I have an action in Toad's Automation Designer to run the scripts for all tests, with 'set serveroutput on' and the output going to a file where I check whether any tests failed.

Goal:
Groom the script output to show only lines sent via dbms_output relating to pass/fail status of the test steps. I can't find the right search terms to get a lead on this. Is it possible?

Best Answer

If you were running via SQL*Plus, you could use set feedback off to suppress those messages. TOAD supports a subset of SQL*Plus commands so I'm not certain that it would support turning feedback off but I would expect that it would.

Personally, though, I'd strongly prefer moving away from a reliance on dbms_output here. Whatever you build is going to get screwed up as soon as someone adds a couple of dbms_output debugging commands to one of the procedures that you're testing. Plus, you're already in a database-- it seems to make a lot more sense to have a test_results table somewhere that you insert data into and then run queries against (i.e. get the count by status) once the test suite is done. If you store those historically and throw in some timestamps, that gives you the ability to do things like look at performance changes over time and things of that nature.

Related Question