Oracle TOAD – Printing Comments or Statements to Output File

oraclessmstoad

Coming from a SQL SSMS environment, I would like to duplicate their Print Command which allows you to print commands or variables while executing a query. I am looking for this feature to help identify particular sections of the script during the execution.

For instance we have a .sql script that runs in the evening that checks for discrepancies on particular inventory numbers. These inventory numbers have lets say a store_id of 1, 2, 3, 4, 5.

The script has a select statement attached to each of these store_id. I would like to be able to place a PRINT statement for the select statement of Store_ID 1 so I can see the output.txt file and see the heading 'Store_id 1" then the inventory numbers, then see heading "Store_id 2" etc…

in MSSQL SSMS it would like something like this 

PRINT 'Searching Inventory Discrepancies at Store 1'
Select * from table1 where store_id = '1' etc...

PRINT 'Searching Inventory Discrepancies at Store 2'
Select * from table1 where store_id = '2' etc...

If I need to clarify please advise, thank you!

Best Answer

The syntax below will allow you to place comments either before or after your query, so that the comments display on the output.txt file:

BEGIN
    DBMS_OUTPUT.PUT_LINE('TEXT GOES HERE ');
END;
/

You should also issue SET SERVEROUTPUT ON to ensure the PUT_LINE command will work.

Here are some commands you can use while using DBMS_OUTPUT.PUT_LINE to control the formatting of the output:

SET UNDERLINE OFF    
SET LINESIZE 32767    
SET PAGES 10000    
SET TRIMSPOOL ON    
SET TRIMOUT ON    
SET FEEDBACK OFF    
SET HEADING ON    
SET NEWPAGE 0    
SET HEADSEP OFF    
SET WRAP OFF    
SET TERMOUT OFF

In my case, I had to use particular settings to allow my output to stop word wrapping and causing the formatting to get thrown off.

SET LINESIZE 32767
SET HEADING ON
SET HEADSEP OFF
SET WRAP OFF
Related Question