3 Replies Latest reply on Feb 17, 2017 7:18 PM by Vadim Tropashko-Oracle

    Formatting in SQL Developer 4.2

    riffel

      Since the formatting engine was improved dramatically behind the scenes in 4.2, I'm hoping that future versions will take advantage of the new engine to make more visible formatter improvements.

       

      SQL Developer formatting creates very tall formatting that often requires scrolling to read.  The closest existing formatter I've found that works the way I would like is http://poorsql.com/.  Here are some changes that I believe would provide more compact code that can then be read and understood more easily.  All these examples are modifications of the example code shown in SQL Developers options dialog.

       

      Function headers should wrap their parameters, so this:

         Function Hire (

            Last_Name Varchar2,

            Job_Id Varchar2,

            Manager_Id Number,

            Salary Number,

            Department_Id Number

         ) Return Number Is

      Should be this:

         Function Hire (Last_Name Varchar2, Job_Id Varchar2, Manager_Id Number, Salary Number, Department_Id Number) Return Number Is

      If that becomes too long then wrap the Return like this:

         Function Hire (Last_Name Varchar2, Job_Id Varchar2, Manager_Id Number, Salary Number, Department_Id Number)

             Return Number Is

      If that is still too long wrap the parameters and line up the types like this:

         Function Hire (

            Last_Name     Varchar2,

            Job_Id        Varchar2,

            Manager_Id    Number,

            Salary        Number,

            Department_Id Number

         ) Return Number Is

       

      Case should never wrap when it can fit on one line, so this:

               Case "1"

               When 1

               Then 'XX'

               End

      Should be this:

               Case "1"  When 1 Then 'XX' End

      If this makes for lines that are too long then it should break on each When and the end, like this:

               Case "1"

               When 1 Then 'XX'

               End

       

      Only if the When and Then can't fit in the line should the Then wrap, but when it does it should be indented like this:

               Case "1"

               When 1

                   Then 'XX'

               End

       

      Don't break after INTO, so this:

            Into

               New_Empno

      Should be this:

            Into New_Empno

       

      Don't break after From, so this:

            From

               Emp,

      Should be this:      From Emp,

       

      Don't break after Select, so this:

            Select

               Case "1"  When 1 Then 'XX' End

      Should be this:

            Select Case "1"  When 1 Then 'XX' End

       

      Joins should line up with Select/From, should not break before ON and should not break after ON so this:

                  From Wsh_New_Deliveries Wnd

                     Join

                        Wsh_Delivery_Assignments Wda

                     On

                        Wnd.Delivery_Id=Wda.Delivery_IdShould be this:

                  From Wsh_New_Deliveries Wnd

                  Join Wsh_Delivery_Assignments Wda On Wnd.Delivery_Id=Wda.Delivery_Id

       

      Don't break before/after an open/close parenthesis unless it is the start of a subquery, but do break for AND and line it up with WHERE, so this:

            Where (

                  1=2 Or 3=4

            ) And 0=1+2

      should become this:

            Where (1=2 Or 3=4)

            And 0=1+2

      This also would mean that this should stay as is:

      And Exists (

          Select

       

      Inline select lists when they are short, and insert value lists always.  If wrapping is needed for length, then indent and start with comma, so this:

                  Select 1,

                     2,

                     3

                  From Wsh_New_Deliveries Wnd

      Should be this:

                  Select 1, 2, 3

                  From Wsh_New_Deliveries Wnd

      And this:

            Insert Into Employees Values (

               New_Empno,

               'First'

                ||  'Middle'

                ||  'Last',

               '(415)555-0100',

               To_Date('18-JUN-2002','DD-MON-YYYY'),

               'IT_PROG',

               90,

               100,

               110

            );

      Should be this:

            Insert Into Employees Values (

               New_Empno, 'First' ||  'Middle' ||  'Last', '(415)555-0100',  To_Date('18-JUN-2002','DD-MON-YYYY')

              ,'IT_PROG', 90, 100, 110

            );

       

      If the INTO will fit on the same line as the SELECT, then don't wrap it, so this:

            Select Case "1"  When 1 Then 'XX' End

            Into New_Empno

      Should be this:

            Select Case "1"  When 1 Then 'XX' End Into New_Empno

       

      So now what was originally 63 lines and would not fit on some screens without scrolling is now 33 lines and the entire

      package can be viewed at once.

       

      -- create_package_body.htm#LNPLS01381

      Create Or Replace Package Body Emp_Mgmt As

         Tot_Emps Number;

         Tot_Depts Number;

         Function Hire (Last_Name Varchar2, Job_Id Varchar2, Manager_Id Number, Salary Number, Department_Id Number)

            Return Number Is

            New_Empno Number;

         Begin

            Select Case "1"  When 1 Then 'XX' End Into New_Empno

            From Emp,

               Dual D1,

               Dual D2

            Where ( 1=2 Or 3=4 )

            And 0=1+2

            And Exists (

                  Select 1, 2, 3

                  From Wsh_New_Deliveries Wnd

                  Join Wsh_Delivery_Assignments Wda On  Wnd.Delivery_Id=Wda.Delivery_Id

                  Join Hz_Locations Hl On Hps.Location_Id=Hl.Location_Id

               );

            Insert Into Employees Values (

               New_Empno, 'First' ||  'Middle' ||  'Last', '(415)555-0100',  To_Date('18-JUN-2002','DD-MON-YYYY')

              ,'IT_PROG', 90, 100, 110

            );

            Tot_Emps:=Tot_Emps+1;

            Case   /* PL/SQL CASE operator formal syntax is different from SQL CASE expression */

            When 100<Salary Then Dbms_Output.Put_Line('100<salary');

            Else Funct1('1',2);

            End Case;

            Return(New_Empno);

         End;

      End Emp_Mgmt;

      /