Header Ads Widget

Responsive Advertisement

How to set line size and column size in sql query

Kartik Chandra Mandal December 20, 2018


In SQL, especially in Oracle SQL Plus, you can adjust the formatting of column output by setting a specific size for the column. Here is an example based on your provided instructions, using the COL command to format the columns and SET commands to control line and page size.

SELECT column1, column2, ... FROM table_name;

 

if you see the column2 value length is bigger, then put the size like "col column2 format a30;" where 30 is the size

Example of SQL Query with Formatting:

sql

-- Set the line size and page size to accommodate larger outputs

SET LINESIZE 1000;

SET PAGESIZE 1000;

 

col <columname> format a<Number>;

 

-- Format specific columns

COL NAME FORMAT A30;           -- Set the NAME column to have a width of 30 characters

COL PARAM_VALUE FORMAT A10;    -- Set the PARAM_VALUE column to have a width of 10 characters

 

-- Perform the select query

SELECT NAME, PARAM_VALUE

FROM your_table_name;

 

Explanation:

  1. SET LINESIZE 1000;: This command sets the line size to 1000 characters, meaning the maximum length of a line displayed on the screen.
  2. SET PAGESIZE 1000;: This command sets the number of rows displayed per page to 1000 rows before a page break occurs.
  3. COL NAME FORMAT A30;: This sets the NAME column's width to 30 characters. If the actual value in this column exceeds 30 characters, it will be truncated.
  4. COL PARAM_VALUE FORMAT A10;: This sets the PARAM_VALUE column's width to 10 characters.

You can adjust the FORMAT A<number> size based on the longest expected value in each column to ensure readability.

Example Query Output:

Assume you have a table with columns NAME and PARAM_VALUE, the output would be something like this:

plaintext

NAME                          PARAM_VALUE

------------------------------ ----------

John Doe                          Active

Alexander The Great      Pending

...

 

In this example:

Ø  The NAME column is formatted with a width of 30 characters.

Ø  The PARAM_VALUE column is formatted with a width of 10 characters.

Feel free to adjust the formatting based on your specific column names and values.


How to set line size and column size in sql query
How to set line size and column size in sql query

 

Post a Comment

0 Comments

Post a Comment