Oracle SQLPlus: Echo without line numbers?

Question

I'm working on a solution where several SQL and PL/SQL scripts are being run together, in a batch of sorts, via SQL*Plus.

I'm declaring SET ECHO OFF; and SET ECHO ON; at relevant points in the scripts so as to output relevant code.

Currently the output looks something like this:

SQL> DECLARE
  2      ct number := 0;
  3      ctChanges number := 0;
  4  
  5  BEGIN
  6      select count(*) into ct from ...
  7  (...rest of code block...)
"some specific status message"
Commit executed.

We keep this output as a run-log in our build-environment, but can also access it as a plain text file.

One downside of this format however, is that if I'd like to copy a certain section of the code and run it again in an IDE (like Toad or SQL Developer), it's hard to exclude the line numbers.

Is it possible to tell SQL*Plus to output the code as above, but without including the line numbers?

GMB · Accepted Answer · 2019-12-06 10:10:48Z

9

You can use options sqlnumber and sqlprompt:

set sqlprompt ''
set sqlnumber off

SET SQLN[UMBER] {ON|OFF}

SET SQLNUMBER is not supported in iSQL*Plus

Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.

edited Dec 6, 2019 at 10:10

answered Dec 6, 2019 at 10:04

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kjartan Over a year ago

Brilliant. So simple, once you know what to look for! Thanks!

Collectives™ on Stack Overflow

Oracle SQLPlus: Echo without line numbers?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related