CODEWITHSUNDEEP
sqloracle-databasesqlplus
PizzaAndCode
PizzaAndCode

Reputation: 360

Sqlplus formatting width, as specified

How can you execute the following Oracle/pl-sql code to display two columns side by side? 

SELECT table_name, user_cons_columns.column_name
FROM user_cons_columns;

Currently, this is my output formatting:

enter image description here

This is the formatting I hope to see:

enter image description here

Solutions tried:

set long 1000

set linesize 200

Where long and linesize have been changed from 20 to 2000, unsuccessfully. I suspect it's just improper SQL code...but unsure. Thank you in advance!

Upvotes: 1

Views: 8090

Answers (2)

user5683823
user5683823

Reputation:

This has nothing to do with the SQL code (and you should NOT change it, for example by truncating the strings in the SQL query, just to fix a formatting problem).

The issue is that the columns, in the table, are declared of a certain width, say VARCHAR2(1000), and then that's what SQL Plus will reserve by default. You can change that in SQL Plus itself, with SQL Plus commands. In this case, the COLUMN command.

SQL> column column_name format a30
SQL> column table_name format a30

These are SQL Plus commands, so don't end them in semicolon ( ; )

Change a30 to a40 if you want 40 characters per column. Etc.

It is not clear why, if in the output you wanted the table name to appear first, in the query you have the column name first. You should be able to fix that yourself. Also, if you select from just one table, there is no need to prefix column names with the table name. However, if you do, be consistent - do it for both columns. And if you do, it is better to give an alias to the table in the FROM clause, and use the alias in SELECT. These are all unrelated to your original question.

Upvotes: 5

lit
lit

Reputation: 16226

Select only the first N (20) characters from the column_name field.

SELECT SUBSTR(column_name, 1, 20) column_name, table_name
FROM user_cons_columns;

Upvotes: -1

Related Questions