Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to set column size in sqlplus.exe ?

bootstrapJun 21 2010 — edited Apr 22 2012
SQL> select * from dba_cons_columns where user='USER1' and table_name='PARENT1';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
  POSITION
----------
USER1                          PARENT1_PK
PARENT1
COL2
         2


OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
  POSITION
----------
USER1                          PARENT1_PK
PARENT1
COL1
         1
doesn't it seems bit ugly to see these data.Why length of columns is so long ?please tell how to set the length of columns and other parameters so that data that is displayed should come out in screen in a well managed and beautiful way.
Like this:
OWNER     CONSTRAINT_NAME     TABLE_NAME       COLUMN_NAME   POSITION
-----------     ----------------------------     ------------------        ---------------------    --------------
USER1        PARENT1_PK              PARENT1             COL2                  2
USER1        PARENT1_PK              PARENT1             COL1                  1
I have written this by hand. How to get display in sqlplus.exe ?
This post has been answered by Aman.... on Jun 22 2010
Jump to Answer

Comments

Aman....
Answer
Easiest would be to use the column format command and also the linesize option. 
sql>column column_name format a30
sql>set linesize 300
The column command would make the display limited to 30 chars only. Also the linesize would widen the display. Try it out and see how it looks?

HTH
Aman....
Marked as Answer by bootstrap · Sep 27 2020
2 people found this helpful
bootstrap
ok..that's working. thanks a lot.
Aman....
Cool! Mark the answer as helpful if you did find it and close the thread if you have got what you were looking for.

HTH
Aman....
bootstrap
How can i close a thread ?
bootstrap
Aman.... wrote:
Easiest would be to use the column format command and also the linesize option. 
sql>column column_name format a30
sql>set linesize 300
The column command would make the display limited to 30 chars only. Also the linesize would widen the display. Try it out and see how it looks?

HTH
Aman....
What is the meaning of "a" in "a30" ?
CKPT
sql>column column_name format a30
Hi,
a30 - alphanumeric30

Thanks
1 person found this helpful
EdStevens
user12222356 wrote:
Aman.... wrote:
Easiest would be to use the column format command and also the linesize option. 
sql>column column_name format a30
sql>set linesize 300
The column command would make the display limited to 30 chars only. Also the linesize would widen the display. Try it out and see how it looks?

HTH
Aman....
What is the meaning of "a" in "a30" ?
That is explained in the fine "SQL*Plus User's Guide and Reference", in the description of the "column" command that you were given. I can understand (sort of ) not being familiar with that command, but when Aman gave you the lead, the first thing you should have done was look up the basic command in the relevant reference and get familiar with all of the arguments associated with it.
bootstrap
Deleted
bootstrap
Aman.... wrote:
Easiest would be to use the column format command and also the linesize option. 
sql>column column_name format a30
sql>set linesize 300
The column command would make the display limited to 30 chars only. Also the linesize would widen the display. Try it out and see how it looks?

HTH
Aman....
This command sets column size for all forthcoming commands. Suppose there is select command for different columns, and i want to set different size for different columns. e.g.,
select name,address,zone from table1;
How can i set column size for name to (say) 20 char, address to 30 char and zone to 3 char ?
Aman....
This command sets column size for all forthcoming commands. Suppose there is select command for different columns, and i want to set different size for different columns. e.g.,
select name,address,zone from table1;
How can i set column size for name to (say) 20 char, address to 30 char and zone to 3 char ?

You need to do it in some sort of sql script which would set the column formatting before the query would start and then would clear the formatting after the query gets finished. See an example below, 
SQL> column ename format a20 heading emp_name
SQL> select ename from emp
  2  ;

emp_name
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

emp_name
--------------------
JAMES
FORD
MILLER

14 rows selected.

SQL> column ename clear
SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

14 rows selected.

SQL>
So likehtis you have to format all the columns on individual lines and then clear the fomattings of each once th query isdone.

HTH
Aman....
bootstrap
Can i set column size of each column in the select query itself ?
something like this:

select name column size=20,address column size=30 from table1;

I gave here the expected prototype of query that i want..or something like that.Is that possible ?
Aman....
No. Its a column level formatting done on the sql*plus , its not a sql command line syntax.

Aman....
932375
sir,

My table is having like this.

Student
---------
sid sname
--- ---------

101 raja

after executing below command

sql>column sid format a30;

sql>column sname format a30;

sql>select * from student;

sid sname
--- ---------

### raja

how to slove this.
sb92075
929372 wrote:
sir,

My table is having like this.

Student
---------
sid sname
--- ---------

101 raja

after executing below command

sql>column sid format a30;

sql>column sname format a30;

sql>select * from student;

sid sname
--- ---------

### raja

how to slove this.
column sid format 999999
1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 20 2012
Added on Jun 21 2010
#general-database-discussions
14 comments
385,902 views