Wednesday, March 14, 2012

Set Long

As their name suggests, long columns can be long. Oracle lets you choose how much of a long column you see when you select it. You can do this with the set long SQL*Plus command. To illustrate this, first create a table with a 26 character long column: 

SQL> create table andrews_table
  2  (long1 long)
  3  /
 
Table created.
 
SQL> insert into andrews_table values
  2  ('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
  3  /
 
1 row created.

SQL>

To see the current value of long, use the show long command:
 
SQL> show long
long 80
SQL>

The value is 80. This means that you will see a maximum of 80 characters if you select a long column. Therefore, if you select the long column in the table above, you will see all of it: 
 
SQL> select * from andrews_table
  2  /
 
LONG1
-------------------------------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZ

SQL>

If you set long to 10, you will see the first ten characters of a long column:
 
SQL> set long 10
SQL> select * from andrews_table
  2  /
 
LONG1
----------
ABCDEFGHIJ

SQL>

If you set long to 20, you will see the first 20 characters of a long column:
 
SQL> set long 20
SQL> select * from andrews_table
  2  /
 
LONG1
--------------------
ABCDEFGHIJKLMNOPQRST

SQL>

... and so on:
 
SQL> set long 30
SQL> select * from andrews_table
  2  /
 
LONG1
------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZ
 
SQL>

No comments: