CODEWITHSUNDEEP
oracle-databasesqlplus
Younes
Younes

Reputation: 3293

sqlplus print running statement

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

Upvotes: 9

Views: 31333

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

If you need to know the exact creation time for the objects you could query DBA_OBJECTS or ALL_OBJECTS for the CREATED column.

Upvotes: -3

Allan
Allan

Reputation: 17429

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

Upvotes: 20

Related Questions