sqlplus set command when using oracle sequence

Question

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>

Alex Poole · Accepted Answer · 2012-07-17 08:57:35Z

3

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.

edited Jul 17, 2012 at 8:57

answered Jul 13, 2012 at 16:32

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

1 Comment

Rajesh Chamarthi Over a year ago

Perfect. works for my case. Thanks for the insight on how sql*loader left pads numbers. :)

Collectives™ on Stack Overflow

sqlplus set command when using oracle sequence

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