CODEWITHSUNDEEP
sqloracle-databasesqlplus
Alisa
Alisa

Reputation: 3072

Oracle SQLPlus: print column titles in header when there is no output rows (showing "no rows selected" message)

I am running an SQL command from SQLPlus command line and getting no rows.

Example:

select * from users;

no rows selected

I want to show the column names even if there is no rows in the table.

I tried:

set heading on;

Still no headers appeared after running the query!

I want a straight command, not tricks like concatenating fake records at the start/end of the query by union.

Upvotes: 2

Views: 4069

Answers (1)

Robert Dupuy
Robert Dupuy

Reputation: 857

This can't be done in SQL*Plus.

Two lines are necessary:

desc Users

select * from Users;

If this is for the purpose of outputting a report, in 11g you could turn off SQL*Plus headers:

set heading off;

Then add the headers yourself:

  SELECT listagg (column_name, ',') 
  WITHIN GROUP (ORDER BY COLUMN_ID) COLUMN_NAMES
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME='Users';

and then issue the data query: Select * from Users;

This would at least mean you always get headers.

Upvotes: 4

Related Questions