0

I have created tables in SQL but the way the tables are laid out are not correct as they an in lines, as this screenshot shows:

i.e.:

name

-----------------------------------------------------------------------------------------------------------------------
city

---------------------------------------------------------------------------------------------
country

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

british airways

london


uk

As you can see the fields are displayed before the data. I have just showed you a representation of what it looks like, in SQL the lines after the field names i.e. "name " are dotted.

I am using the insert into statement to insert this data manually, i.e. insert into customer (carrier_id, name, city, country etc) into customer values ('Ba01', 'london','uk'); I have a feeling that its the field sizes as they are all pretty much varchar2 (100)?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Jesal Mavadiya
  • 49
  • 2
  • 8
  • 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) – Alex Poole Apr 02 '13 at 19:32

1 Answers1

3

I'm assuming that this is a question about how SQL*Plus, the command-line tool, is displaying your data. You can control that by changing the formatting of your columns

SQL> column name format a20
SQL> column city format a20
SQL> column country format a10

will use 20 columns to display the name, 20 columns to display the city, and 10 columns to display the country. You can, of course, adjust those depending on how you want the data to be presented.

If you have numeric data, you would use a format mask like

SQL> column column_name format 999.99

This will display up to three digits before the decimal point and two afterword. It will round the number to the appropriate number of decimal positions if the actual data contains more decimal data. If the number is too large (in this case, a value greater than or equal to 1000), SQL*Plus won't be able to display the data so you'll see a bunch of replacement characters (#).

Most of the time, however, if you're just trying to view your data, you're better off using a tool that is more suitable for presenting data in a pleasing format. Oracle's SQL Developer, for example, is a free PL/SQL IDE that will present the data in a nice tabular grid rather than trying to display the data in a text-based command-line tool.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • great thanks alot, tables look much better now.I ll look into sql developer thankyou – Jesal Mavadiya Apr 02 '13 at 19:58
  • one thing that happened when I did this is that my float prices and some order ids went missing and displayed #######? – Jesal Mavadiya Apr 02 '13 at 20:00
  • @JesalMavadiya - For numeric columns, you'd want the format mask to be something like 999.99 to display 2 decimal digitc and up to three digits before the decimal point. If you specify a format mask that is too small for the data, SQL*Plus will display all #'s. – Justin Cave Apr 02 '13 at 20:14