CODEWITHSUNDEEP
oracle-databasesqlplus
Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

sqlplus set command when using oracle sequence

When returning data from a sqlplus session, I generally use the following set commands to suppress everything except the data.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select sysdate from dual;

SYSDATE
---------
13-JUL-12

SQL> set pagesize 0 feedback off ver off heading off echo off;
SQL> select sysdate from dual;
13-JUL-12

But when trying to get data from a sequence, looks like a tab is being added automatically. Is there another sqlplus parameter that needs to be set?

SQL> select test_seq.nextval from dual;
         2


SQL>

Upvotes: 1

Views: 2232

Answers (1)

Alex Poole
Alex Poole

Reputation: 191325

You can set tab off, but that isn't quite the issue - you'll just get spaces instead of tabs. The sequence value is a number, and SQL*Plus left-pads numbers to the width of the column. If you were showing headers you'd see it aligned with the right-most character of the heading for the (pseudo-)column.

The easiest way to stop it, since you don't know the size of the value and so can't really set a format mask with the desired number of characters, is just to do:

select to_char(test_seq.nextval) as my_val from dual;

If you're selecting more than one column you'll still get multiple spaces between each value though.

Edited to add: Actually that's not quite complete. You can also set numformat with a format mask, which I obliquely referred to; and can also set numwidth. But numwidth has to be at least 2, so you'll still get one space before a single-digit number (to allow for a sign, I think); and if the number has too many digits it will display # instead. And if the column title is longer than numwidth it'll still left-pad to that anyway. More info here.

Upvotes: 3

Related Questions