DB version/SQL*Plus version: 11.2.0.4
OS : Linux/Unices
I couldn't find a dedicated forum ('space') for SQL*Plus , hence posting it here.
I have few SQL scripts where the output should be pipe separated. So, I use SET COLSEP '|'
The number of row returned can be in millions. So, i must set PAGESIZE to 0
But, setting PAGESIZE to 0 will prevent the headings from being printed. So, when I have a SELECT query like below
SELECT ser.serial_num,
a.row_id AS irrowid,
a.bill_profile_id AS irbp,
a.bill_accnt_id AS irba,
a.status_cd irstatus,
a.root_asset_id irrootasset,
b.name AS irname,
b1.name raname,
a1.row_id AS rarowid,
a1.bill_profile_id AS rabp,
a1.bill_accnt_id AS raba,
a1.status_cd rastatus,
a1.root_asset_id AS rarootasset
FROM
<table list>
<where list>
To get the column names printed at the top, I tried placing the below DUAL query with column names before SELECT query
set lines 200
SELECT 'serial_num| irrowid| irbp| irba| irstatus| irrootasset| irname|raname|rarowid|rabp|raba|rastatus|rarootasset 'FROM dual;
SELECT ser.serial_num,
a.row_id AS irrowid,
a.bill_profile_id AS irbp,
.
.
.
But, the output doesn't seem to be aligned. ie. It prints like below where the pipe character is not aligned to the beginning of the each column name.
Any workaround for this in 11.2 or 12C ?
serial_num| irrowid| irbp| irba| irstatus| irrootasset| irname|raname|rarowid|rabp|raba|rastatus|rarootasset
0143170188 |1-17G4HEMD |1-10NWXPIL |1-10NWXPIH |Active |1-10NXGA4J |International Roaming Data |ROAM ALERT - RTP |1-16G4AHHQ |1-10NWXPIL |1-10NWXPIH |Active |1-10NXGA4J
0173797419 |1-14G4ZEJU |1-IC2C-3978 |1-I9IT-2751 |Active |1-13GO2EBH |International Roaming Data |ROAM ALERT - RTP |1-13GO2EC6 |1-IC2C-3978 |1-I9IT-2751 |Active |1-13GO2EBH
0124404519 |1-16H5B258 |1-K5XP1JU |1-K5XP1JQ |Active |1-16DQMBIO |International Roaming Data
SQL*Plus variables I use the script
set show off
set feedback off
set term off
set echo off
set underline off
set colsep '|'
set linesize 3000
set pages 0
set trimspool on
set trimout on
set feedback off
set heading on
set newpage 0
set headsep off
set termout off
set long 50000