Jesal Mavadiya Jesal Mavadiya - 1 month ago 7
SQL Question

Oracle sql tables not displaying correctly but information within tables is correct?? using oracle 11g

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)?

Answer

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.