3 Replies Latest reply: Jun 27, 2011 2:38 PM by Peter Gjelstrup RSS

    formatting sqlplus

    609621
      Dear all,

      Below query in sqlplus returns the output in 2 rows . I want the output to be in the same row

      select DISTINCT ORIGINATING_TIMESTAMP,MESSAGE_TEXT from sys.X$DBGALERTEXT WHERE MESSAGE_TEXT LIKE 'ORA-%' or MESSAGE_TEXT LIKE '%Fatal%' AND ORIGINATING_TIMESTAMP > SYSDATE-4;


      sample output :

      28-APR-10 07.03.29.925 PM +03:00
      ORA-28 : opiodr aborting process unknown ospid (18707_1)

      I tried using set linesize and column format also. but, the output is not displaying in single row

      Any idea what I need to set

      Kai
        • 1. Re: formatting sqlplus
          riedelme
          KaiS wrote:
          Dear all,

          Below query in sqlplus returns the output in 2 rows . I want the output to be in the same row

          select DISTINCT ORIGINATING_TIMESTAMP,MESSAGE_TEXT from sys.X$DBGALERTEXT WHERE MESSAGE_TEXT LIKE 'ORA-%' or MESSAGE_TEXT LIKE '%Fatal%' AND ORIGINATING_TIMESTAMP > SYSDATE-4;


          sample output :

          28-APR-10 07.03.29.925 PM +03:00
          ORA-28 : opiodr aborting process unknown ospid (18707_1)

          I tried using set linesize and column format also. but, the output is not displaying in single row

          Any idea what I need to set

          Kai
          There is actually a forum for SQL*PLUS, but it doesn't get much traffic (frowny emoticon following will probably not appear) :(

          Assuming that the output in your example is the message and not an error that you are asking for help with you can use the FOLD_AFTER command to force columns to appear on different lines, something like
          column one fold_after
          
          select 1 one, 2 two
            from dual;
          
          one
          ----------
          two
          ----------
                   1
                   2
          you can get better appearance by turning headings off

          Edited by: riedelme on Jun 27, 2011 12:50 PM

          oops, op wanted to eliminate the folding, not force it
          • 2. Re: formatting sqlplus
            Solomon Yakobson
            Check sys.X$DBGALERTEXT definition. MESSAGE_TEXT is VARCHAR2(2048). Therefore, to see it on one line using LINESIZE you need:
            SQL> select DISTINCT ORIGINATING_TIMESTAMP,MESSAGE_TEXT from sys.X$DBGALERTEXT WHERE MESSAGE_TEXT LIKE 'ORA-%' or MESSAGE_TEXT LIKE '%Fatal%' AND ORIGINATING_TIMESTAMP > SYSDATE-4;
            
            ORIGINATING_TIMESTAMP
            ---------------------------------------------------------------------------
            MESSAGE_TEXT
            --------------------------------------------------------------------------------
            26-JUL-10 11.52.01.178 AM -04:00
            ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
            
            
            SQL> set linesize 2200
            SQL> /
            
            ORIGINATING_TIMESTAMP                                                       MESSAGE_TEXT
            --------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------
            26-JUL-10 11.52.01.178 AM -04:00                                            ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
            
            SQL> 
            SY.
            • 3. Re: formatting sqlplus
              Peter Gjelstrup
              Hello Kai,

              It works for me. Although, it displays poorly in the command line window.

              Try spooling, and I think you will see
              SQL> set lines 4000
              SQL> spool tmp.txt 
              SQL> 
              SQL> select originating_timestamp, message_text  from sys.X$DBGALERTEXT where rownum < 11;
              
              -- Cannot post output
              
              10 rows selected.
              
              SQL> 
              SQL> spool off
              Regards
              Peter