How to echo text during SQL script execution in SQLPLUS

Question

I have a batch file which runs a SQL script in sqlplus and sends the output to a log file      sqlplus user pw  lt  RowCount sql   RowCount log   My log file contains this   Connected to  Oracle Database 11g Express Edition Release 11 2 0 2 0 - Production  SQL gt  SQL gt     COUNT    ----------      0  SQL gt     COUNT    ----------      0   etc  but it s several thousand lines of output and therefore hard to determine which results belong to which statement   I would like to add some formatting to the output  so that I may discern what happened  Either an echo of the executed statement or manually inserting some  echo  statements into the script would be fine  Ideally it would look something like this   SQL gt  select    from TableA    COUNT    ----------      0  SQL gt  select    from TableB    COUNT    ----------      0

User · Answer

You can change the name of the column  therefore instead of  COUNT     you would have something meaningful   You will have to update your  RowCount sql  script for that   For example   SQL gt  select count    as RecordCountFromTableOne from TableOne    Will be displayed as   RecordCountFromTableOne -----------------------            0   If you want to have space in the title  you need to enclose it in double quotes  SQL gt  select count    as  Record Count From Table One  from TableOne    Will be displayed as   Record Count From Table One ---------------------------             0

User · Answer

The prompt command will echo text to the output   prompt A useful comment  select    from TableA    Will be displayed as   SQL gt  A useful comment  SQL gt     COUNT    ----------      0

User · Answer

You can use SET ECHO ON in the beginning of your script to achieve that  however  you have to specify your script using   instead of  lt   also had to add EXIT at the end    test sql  SET ECHO ON  SELECT COUNT 1  FROM dual   SELECT COUNT 1  FROM  SELECT 1 FROM dual UNION SELECT 2 FROM dual    EXIT   terminal  sqlplus hr oracle orcl   tmp test sql  gt   tmp test log   test log  SQL   SQL  SELECT COUNT 1  FROM dual     COUNT 1  ----------      1  SQL   SQL  SELECT COUNT 1  FROM  SELECT 1 FROM dual UNION SELECT 2 FROM dual      COUNT 1  ----------      2  SQL   SQL  EXIT

[sql] How to echo text during SQL script execution in SQLPLUS

Examples related to sql

Examples related to oracle11g

Examples related to sqlplus