如何在SQLPLUS中执行SQL脚本期间回显文本 [英] How to echo text during SQL script execution in SQLPLUS

问题描述

我有一个批处理文件，该文件在sqlplus中运行一个SQL脚本，并将输出发送到日志文件:

I have a batch file which runs a SQL script in sqlplus and sends the output to a log file:

sqlplus用户/密码< RowCount.sql> RowCount.log

sqlplus user/pw < RowCount.sql > RowCount.log

我的日志文件包含以下内容:

My log file contains this:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> SQL> 
  COUNT(*)
----------
     0

SQL> 
  COUNT(*)
----------
     0

等但是它有数千行输出，因此很难确定哪个结果属于哪个语句.

etc. but it's several thousand lines of output and therefore hard to determine which results belong to which statement.

我想在输出中添加一些格式，以便我可以识别出发生了什么.可以对执行的语句进行回显，也可以在脚本中手动插入一些"echo"语句.理想情况下，它看起来应该像这样:

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> select(*) from TableA;
  COUNT(*)
----------
     0

SQL> select(*) from TableB;
  COUNT(*)
----------
     0

推荐答案

您可以在脚本的开头使用SET ECHO ON来实现，但是，您必须使用@而不是<来指定脚本(还必须在末尾添加EXIT):

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 < (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

终端

sqlplus hr/oracle@orcl @/tmp/test.sql > /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

这篇关于如何在SQLPLUS中执行SQL脚本期间回显文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！