-1

I'm new to oracle (or SQL in general) and trying to get stuff done. In MS SQL Server I can do select * from tablename; and it displays all the data in a tabulated format. If I do that in Oracle it displays the data in a weird format that's hard to read, unless I specifically select the columns I want.

Is there a way to display the data in Oracle formatted like regular MSSQL format?

I looked around and people say to use SHOW COLUMNS FROM TABLENAME; but that gives me an error saying "unknown option". I can do Desc tablename but that only gives me the metadata.

APC
  • 144,005
  • 19
  • 170
  • 281
polaris
  • 339
  • 2
  • 14
  • 33
  • 2
    Possible duplicate of [how to display table data more clearly. in oracle sqlplus](http://stackoverflow.com/questions/3006431/how-to-display-table-data-more-clearly-in-oracle-sqlplus) – Kris Johnston May 17 '17 at 16:08
  • 1
    The Oracle database is responsible only for returning the results of your `SELECT * FROM tablename`. It is the responsibility of the client program you are using to display those results on the screen, if that is what you want. Which client program are you using? Most GUI clients SQL*Developer, SQL*Navigator, TOAD, etc., will display results nicely out of the box. SQL*Plus is more difficult, requiring a bunch of setup to define column widths, line widths, page size, etc. That is, perhaps, one reason why many developers use GUI clients. – Matthew McPeak May 17 '17 at 16:22
  • Also unlike SQL Server you need to utilize refcursors when using procedures to return record sets. https://www.akadia.com/services/ora_return_result_set.html – xQbert May 17 '17 at 17:20
  • Don't use SQL\*Plus, use a graphical SQL client. –  May 17 '17 at 18:26
  • @xQbert: that's no longer true with Oracle 12: https://oracle-base.com/articles/12c/implicit-statement-results-12cr1 –  May 17 '17 at 19:07
  • Schweeeeet... too bad I'm stuck on 11.... for the time being.... (and you still have to define a refcursor to return... but it is easier/cleaner) – xQbert May 17 '17 at 19:09

4 Answers4

2

Welcome to Oracle!

As a new user, we have 2 command-line interfaces. It sounds like you're using SQL*Plus. We also have something called SQLcl.

The latter includes automatic output formatting so you don't have to do the things like 'linesize' or 'format' in your commands to get readable query results.

enter image description here

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
1

I think you are using SQLPlus window to see output. You have to format it to see good. First

set linesize 5000;

This command will make your row long enough to hold columns. You need to increase or decrease number according your need.

Set pagesize 100;

Here, 100 is number of records in one page to show.

Moreover, some column length are very big in database. You need to format display for those specific column to show it as short.

COLUMN Column_NAME FORMAT A10;

Here, a10 is ten digit for that column.

smshafiqulislam
  • 564
  • 6
  • 12
0

Use below query, use solution 2 if you don't know exact table name:

--Solution1

select column_name,table_name from all_tab_cols where table_name='table_name'

--Solution2

select column_name,table_name from all_tab_cols where table_name like '%table_name%'
Pritam Parashuram Thakur
  • 131
  • 9
0

Download SQLDeveloper from Oracle. It's free to use.

unleashed
  • 771
  • 3
  • 9