8 Replies Latest reply: Dec 19, 2013 9:23 PM by BeverlyABeeman-Oracle RSS

    SQL Developer 4.0 column headings

    BeverlyABeeman-Oracle

      I give!  Uncle, uncle, uncle!

       

      I migrated from SQL Developer 3.xxx to 4.0.  My queries no longer show column headings.  I have looked through "preferences" and have not found the answer.  I have tried SQL commands that work in SQL*Plus.

       

      What am I missing?  How do I get my column headings back in the output?

        • 1. Re: SQL Developer 4.0 column headings
          Gary Graham-Oracle

          In the worksheet, run the following and see if you have either heading off or pagesize 0

          show all

          Either of those settings will suppress column headings, just as in SQL*Plus.  Perhaps you have a connection startup script defined in Tools > Preferences > Database initializing the session in one of those ways.

           

          Regards,

          Gary

          SQL Developer Team

          • 2. Re: SQL Developer 4.0 column headings
            BeverlyABeeman-Oracle

            Thank you for your help.

             

            Result of "show all" include pagesize -1 and linesize -1.  Also serveroutput is OFF.

             

            There is no startup script in Tools/ Preferences/ Database.  The list must be the "factory settings."  Is there a way to change the default setting for pagesize and linesize?   I have tried "set pagesize 999" in my session, but even for the session there is no response from the database.  Also, I would think that serveroutput should be ON.  I am pretty much a newbie at SQL and SQL Developer and I am clueless about this problem. (But I definitely need my column headings!!)

             

            Beverly

             

            appinfo is OFF and set to "SQL Developer"

            arraysize default

            autocommit OFF

            autoprint OFF

            autotrace OFF

            colsep " "

            copycommit 0

            define "&"

            echo OFF

            escape OFF

            FEEDBACK ON for 6 or more rows

            heading ON

            headsep "|" (hex 7c)

            linesize -1

            long 80

            null ""

            numformat ""

            numwidth 10

            pagesize -1

            serveroutput OFF

            spool OFF

            sqlcode 0

            termout ON

            timing OFF

            USER is APPS

            verify ON

            wrap : lines will be wrapped

            • 3. Re: SQL Developer 4.0 column headings
              Gary Graham-Oracle

              Result of "show all" include pagesize -1 and linesize -1.

              Ah, sorry!   I forgot the rule is a bit more complicated than stated in my first post.  Pagesize 0 and heading off suppress the column headings.  In addition (from some discussion notes)...

              In SQLDeveloper we decided to have the LINESIZE and PAGESIZE set to -1 by default so the table gets printed without any restrictions on dimensions (LINESIZE & PAGESIZE).  Yes the labels columns won't print for PAGESIZE = -1 and LINESIZE > 0 and no indentation for data.

               

              In SQLPlus we can't set pagesize -1, but when we set the PAGESIZE = 0 and LINESIZE > 0 the labels won't print but the line indentation results will be same in

              SQLDeveloper.

              So, for your case, make sure to leave heading on and both pagesize and linesize either at their default setting of -1, or both set greater than 0.  If you wish to set them automatically to non-default values, then in ...

              Tools > Preferences > Database (remember to expand Database, then click on it so it has the focus) > Filename for connection startup script

              provide a file specification to a script containing any such SET statements.  Similar in concept to SQL*Plus checking for the existence of a login.sql script.

               

              Best Wishes,

              Gary

              • 4. Re: SQL Developer 4.0 column headings
                BeverlyABeeman-Oracle

                Can you give me an example of contents of a login.sql file such as you are describing?  I'm not getting it right.

                • 5. Re: SQL Developer 4.0 column headings
                  BeverlyABeeman-Oracle

                  Got it!

                   

                  Created a notepad file called "login.sql"

                   

                  set heading ON;

                  set pagesize 999;

                  set linesize 180;

                   

                  Do you have a suggestion for linesize?  I work in Oracle Applications, not development.  I am usually querying for a very small set of data.  I just made a guess, but I wonder what the setting might have been from SQL Developer 3.x.x.x, for example.

                  • 6. Re: SQL Developer 4.0 column headings
                    rp0428
                    Can you give me an example of contents of a login.sql file such as you are describing?  I'm not getting it right.

                    See the SQL Plus User's guide for that products support and use of login.sql files.

                    http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch2.htm#i1133106

                     

                    Gary or another sql dev rep will need to speak to which of those sql plus settings are supported by sql developer. Gary already mentioned a couple that are different and he told you where you specify the file name you are using in the preferences panel.

                    • 7. Re: SQL Developer 4.0 column headings
                      Gary Graham-Oracle

                      As rp0428 notes, SQL Developer does not support all SQL*Plus settings, although support tends to improve from version to version.  You can check the product help for more information for any given version:

                      Tools > Help > Contents > SQL Developer Concepts and Usage > Using the SQL Worksheet > SQL*Plus Statements Supported and Not Supported in SQL Worksheet

                       

                      I believe "show all" support began in 3.0, with "show" / "set" heading, pagesize, and linesize (more or less) working since 3.1 with defaults of ON, -1, -1.

                       

                      The value you choose for linesize is entirely up to you.  Historically, due to mainframe punch cards and printer fonts and page widths, I believe 80, 132, 180, and 255 have been popular sizes.

                       

                      -Gary-

                      • 8. Re: SQL Developer 4.0 column headings
                        BeverlyABeeman-Oracle

                        Thank you very much, Gary.  I appreciate your help.