Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlplus print running statement

Tags:

oracle

sqlplus

Is there a way where sqlplus can print what statement it executed. I mean I have some .sql files that I run in a bash script. I need to know when I read the log file which statement sqlplus ran.

Example: Say I have this test.sql file:

set timing on
create table foo (a varchar2(10));
create table bar (b varchar2(10));
exit

When I check the log I get this:

Table created.

Elapsed: 00:00:00.02

Table created.

Elapsed: 00:00:00.01

Which is not informative. Is there a way where I can get output like this:

Table foo created.

Elapsed: 00:00:00.02

Table bar created.

Elapsed: 00:00:00.01

Or even like this:

create table foo (a varchar2(10));

Table created.

Elapsed: 00:00:00.02

create table bar (b varchar2(10));

Table created.

Elapsed: 00:00:00.01

I know I can use PROMPT before each statement but I have big sql scripts and it would be tedious to write PROMPT before each statement.

EDIT:

For Allan's solution to always work (i.e. using "set echo on"), you should avoid the following:

1) Don't use the -S option with sqlplus because this will suppress the display of echoing of commands.

2) Don't "cat" your script to sqlplus like this:

cat test.sql | sqlplus scott/tiger@orcl
like image 542
Younes Avatar asked May 09 '13 16:05

Younes


1 Answers

The command you're looking for is SET ECHO ON, which will repeat the each statement that is issued.

like image 185
Allan Avatar answered Oct 19 '22 09:10

Allan