2 Replies Latest reply: Jan 13, 2012 5:16 PM by 643298 RSS

    Query output result on shell is a big mess

      I just installed Oracle Express on Ubuntu 11.10. Then I tried using SQL*PLUS by running the query "select * from hr.employees;". But the output was a mess. Can anyone help me with formatting the output?
      Besides, I want this to be a permanent change not just for the current session.

      This is how it looks after I ran the query "select * from hr.employees;"

      Edited by: 908010 on Jan 13, 2012 11:17 AM
        • 1. Re: Query output result on shell is a big mess
          Brian Bontrager
          Formatting SQL*Plus Reports

          SQL*Plus Configuration (or using a login.sql file so every time you start sqlplus your preferences are loaded)
          • 2. Re: Query output result on shell is a big mess
            Set off with a wider console using SET LINESIZE 180. Then add few useful settings like SET PAGES 50000 for having headers printed only once every 50k rows, SET TAB OFF to prevent replacing of multiple spaces with tab characters plus SET TRIMOUT ON and SET TRIMSPOOL ON to get rid of trailing spaces at line ends.

            To format individual columns use column clause:
            column first_name format a20
            column last_name format a20
            column email format a30
            Finally, to make these persistent, place them in either site global configuration script under $ORACLE_HOME/sqlplus/admin/glogin.sql or local one at ~/login.sql.