1

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?

0

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.

| improve this answer | |
  • Set feedback off doesn't seem to work here. Good points. There is already dbms_output from a procedure designed only to support testing, and I was going to ignore or remove that. But you're right about the main functional procedures. We're using Excel to track pass/fail since that's where we document the purpose of the test and use formulas to calculate expected results for each test case. (We don't have JIRA/Zephyr here.) In considering your answer, I found a good workaround: put last script (pass/fail check) for each test in a separate step with its own output file. Thx! – wiress Sep 19 '19 at 22:12
0

Here's a workaround that gave me what I wanted without addressing my question. Documenting it here in case it's useful to someone.

I was able to put the final pass/fail-checking script in a separate action in Toad's Automation Designer, with a separate output file. The first several scripts for each test write to one file, and the last step of each test writes to a second file. The second file does have one set of PL/SQL complete and commit lines per test, but not all the create/insert/drop.

| improve this answer | |
  • If you have more requests like this, I would reccomend that you look into Unit Testing tools because... that is what you are doing. – Michael Kutz Sep 19 '19 at 23:44
  • This is a great point. Our company installs Toad rather than SQL Developer as the default, but we can probably request SQL Developer for unit tests. There are only a few of us devs/testers/admins, and this is the most advanced test automation we've embarked on so far. We automate tons of the analysis processes that write results into the database, so a better unit testing tool would probably be welcomed by our team. Thanks! – wiress Sep 24 '19 at 1:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.