Printing comments or statements when executing script to a output file using TOAD/ORACLE

Question

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!

Answer 1

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

Answer 2

The answer from Hector is showing only the PL/SQL version and it's practically not was has been asked.

If you're looking for printing in pure SQL scripts, use the sqlplus command PROMPT.

PROMPT Searching Inventory Discrepancies at Store 1
Select * from table1 where store_id = '1' ;

PROMPT Searching Inventory Discrepancies at Store 2
Select * from table1 where store_id = '2' ;

Then the executing client (e.g. sqlplus) prints the text directly and because it doesn't sent the text first to the database, it's even much faster and has no size limits like dbms_output.

Disadvantage: You cannot use any DB-functions like e.g. SYSDATE from database.

Please note: sqlplus commands don't need ; to mark the end of a SQL-command like SQL commands do.

Answer 3

Or just do this:

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