12 Replies Latest reply: Sep 3, 2014 11:08 AM by Cuauhtemoc Amox RSS

    sqlplus auto-format columns ?

    2654204

      Hello fellows,

       

      I have a situation here, and maybe some of you could help.

       

      I am using sqlplus, and a user asked to execute a "select *" query and send the output.

       

      The problem is that the table has many columns, and the resultset was unformatted, i tried to format all the columns, but i couldn't solve it. The better thing that i've got was doing 'set markup html on', and give the output in xls.

       

      Do you guys know another way to solve this issue ?

       

      Thanks.

        • 1. Re: sqlplus auto-format columns ?
          SomeoneElse

          We can't look over your shoulder so we have no idea what problem you're having.

           

          You need to post something.

          • 2. Re: sqlplus auto-format columns ?
            2654204

            Hi,

             

            I'll try to do better:

             

            Let's suppose that someone asked you for the output of this query:

            select * from v$session where sid= 1851;

             

            You may use only SQLPLUS to take this report.

             

            My output looks like :

            SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME                          COMMAND    OWNERID TADDR         LOCKWAIT         STATUS   SERVER       SCHEMA# SCHEMANAME                     OSUSER                     PROCESS
            ---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ---------------- -------- --------- ---------- ------------------------------ ------------------------------ ------------
            MACHINE                                                          TERMINAL                       PROGRAM                      TYPE        SQL_ADDRESS      SQL_HASH_VALUE SQL_ID        SQL_CHILD_NUMBER PREV_SQL_ADDR    PREV_HASH_VALUE PREV_SQL_ID   PREV_CHILD_NUMBER
            ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ---------- ---------------- -------------- ------------- ---------------- ---------------- --------------- ------------- -----------------
            MODULE                                           MODULE_HASH ACTION                           ACTION_HASH CLIENT_INFO          FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET PDM
            ------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- -------------------- ------------- -------------- --------------- ------------- --------- ------------ ---
            FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER                                             BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION       SEQ#     EVENT#
            ------------- ---------- --- -------------------------------- -------- -------- -------- ---------------------- ---------------------------------------------------------------- ----------- ----------------- ---------------- ---------- ----------
            EVENT                                                            P1TEXT                                                       P1 P1RAW             P2TEXT                                                                   P2 P2RAW
            ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ----------------
            P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                     WAIT_TIME SECONDS_IN_WAIT STATE
            ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- -------------------
            SERVICE_NAME                                                     SQL_TRAC SQL_T SQL_T
            ---------------------------------------------------------------- -------- ----- -----
            0000000CD24F40A8       1851          1          0 0000000CD2351B20          0                                         0 2147483644                                ACTIVE   DEDICATED          0 SYS                            oracle                     22789

             

             

            As you can see, the output has too many columns to format.

            How can i format this output that makes it easier to read ? (the best way i found doing this is using 'set markup html on').

             

            Did i made myself clear this time?

             

            Thanks.

            • 3. Re: Re: sqlplus auto-format columns ?
              SomeoneElse

              > Did i made myself clear this time?

               

              Much better.

               

              Try this in SQL Plus:

               

              SQL> SET linesize 5000

              SQL> SET tab off

              SQL> select * from v$session where sid = 221;

               

               

              SADDR                             SID              SERIAL#               AUDSID PADDR                           USER# USERNAME                      

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

              0000000316468800                  221                    7               241245 00000003162B7AA0                   91 SELSE                         

               


               

              (I'm not going to post the entire row.)

               

              And if your output is still wrapping it might be because of your OS.  But I don't know what you're using.

              • 4. Re: sqlplus auto-format columns ?
                Kent D Sorber

                Do you need every column ?

                You could do a DESC and just select columns needed and format those columns.

                EM also has a SQL Worksheet you might be able to use to format them for you or use the HTML ON.

                 

                Regards,

                Kent D Sorber, OCP

                • 5. Re: sqlplus auto-format columns ?
                  Sven W.

                  Use Oracle SQL Developer. It is free (in the sense of "no cost").

                  And it is a better tool for getting small result sets than sql*plus.

                  • 6. Re: Re: sqlplus auto-format columns ?
                    2654204


                    Hi,

                     

                    It didn't made much difference.

                     

                    Thanks anyway!

                    • 7. Re: Re: sqlplus auto-format columns ?
                      2654204

                      Anyway, i'm using Red Hat Enterprise Linux Server release 5.10.

                      • 8. Re: sqlplus auto-format columns ?
                        2654204

                        Hi,

                         

                        It is a user request (not from v$session, but from another table with many columns).

                         

                        It is not a option to cut off some columns.

                         

                        Thanks.

                        • 9. Re: sqlplus auto-format columns ?
                          2654204

                          Hi Sven,

                           

                          I am sure SQL DEVELOPER, or another software but sqlplus would do better.

                           

                          But the thing is: i can't use them, my access to the server isn't directly.

                          My access to the server is like this:

                           

                          MYMACHINE -> GATEWAY -> SERVER.

                           

                          Isn't possible to do MYMACHINE -> SERVER.

                           

                          Thanks.

                          • 10. Re: sqlplus auto-format columns ?
                            Cuauhtemoc Amox

                            You could try port forwarding if using a jump host.

                             

                            About formatting your sqlplus output, Formatting SQL*Plus Reports

                             

                            Just spool everything, then the user should be able to import to excel and convert to columns using a fixed space. A basic script would be something like the following

                             

                            -- set whatever options you want your sqlpus env

                            set lines 99999

                            set pages 0

                            set feedback off

                            set trimspool on

                             

                            -- Enter query

                            select *

                            from table_with_1000_columns

                            .

                             

                            -- Open Spool

                            spool table_with_1000_columns_data.txt

                             

                            -- Execute query

                            /

                             

                            -- Close spool

                            spool off

                            • 11. Re: sqlplus auto-format columns ?
                              Sven W.

                              These are the settings that I commonly use:

                               

                              set pagesize 0

                              set linesize 1000

                              set long  20000000

                              set longc 2000

                              set head off

                              set feedback off

                              set trimspool on

                              set termout off

                              set scan off

                               

                              Termout off works only if sqlplus with a script is called from the command line. But it improves spool-speed a lot.

                              Trimspool on is important to reduce the size of the file.

                              Long and longchunc seem to important for line breaks and maybe clobs.

                              Linesize can be increased if you have more columns.

                              • 12. Re: sqlplus auto-format columns ?
                                Cuauhtemoc Amox

                                Beware that longc and long should usually be the same, otherwise you might get hard word wraps when reaching the longc size. As you mention this is usually for long and clob and a particular pesky case is when trying to retrieve XML data as it might get wrongly formatted.