Tuesday 16 July 2013

Text version of PLSQL Formatter options in SQLDeveloper 4.0

Introduction

This blog post is to display the different formatting options for Oracle SQLDeveloper.
(***LATER VERSION IS AVAILABLE ON THIS BLOG WITH SCREENSHOTS ***).
I am using Oracle 'SQL' preferences as the base set of options.
I am also using the PL/SQL example code as shipped in the preferences UI.
I will do the text first and put in images/screen shots of the differences if time allows.

Section InputOutput:
Option 1: Source SQL Copied out of Program code
Option 2: Output can be pure SQL or embedded in different languages:

Section Alignment
Option 1: Right Align Master Keywords:
Option 2: Align Equal sign (= < > ...)
Option 3: Align Operator signs (* - + ...)
Option 4: Align on "AS" keyword
Option 5: Align on commas.
Option 6: Align line comments "--"
Option 7: Align "||" at end of line (needed to put newlines before and not after || to see it)
Option 8: Align variable declaration for stored procedures.

Section Indentation
Option 1: Spaces: number of spaces to indent:
Option 2: Use tabulator
Option 3: Indent Main Keyword 2x
Option 4: Indent AND/OR

Section Line breaks:
Option 1: Schema Type
Option 2: After SELECT, FROM, WHERE...
Option 3: Before Comma
Option 4: Before AND/OR
Option 5: before "||"
Option 6: After Comma
Option 7: After AND/OR
Option 8: After "||"
Option 9: Number of commas per line
Option 10: For brackets in AND, OR, WHEN...
Option 11: For subselect brackets
Option 12: For JOIN Statements
Option 13: Max Line Width
Option 14: Threshold for small SQL
Option 15: Force Linebreak before line comments
Option 16: Blank lines

Section CASE linebreaks

Section White Space.
Option 1: Spaces around Operators:
Option 2: Spaces around commas:
Option 3: Spaces around brackets

Section Other
Option 1: Delete Comment
Option 2: Put -- comment between /* ... */
Option 3: Force formatter output on difference
Option 4: case change

Section InputOutput:
Option 1: Source SQL Copied out of Program code.
Assumption, Source SQL: Enclosed in "

Used when SQL is contained in another language for example contained in Java, C or
even PL/SQL itself (as in the contrived example).

With option on:

SELECT
  embedded_double_query
FROM
  mytable

With option off:

/* Comment... embedded in double quotes "select embedded_double_query from
mytable" */
/* Embedded in single quotes 'select embedded_single_query from mytable' */
CREATE OR REPLACE PACKAGE BODY test1
IS
  g_column1               VARCHAR2(17) := NULL;
  g_column2               VARCHAR2(52) := NULL;
  g_column3_from_column22 VARCHAR2(25) := NULL;
  g_column_4711           VARCHAR2(11) := NULL;
FUNCTION testfunction(

Option 2: Output can be pure SQL or embedded in different languages:

Output SQL: SQL
/* Comment... embedded in double quotes "select embedded_double_query from
mytable" */
/* Embedded in single quotes 'select embedded_single_query from mytable' */
CREATE OR REPLACE PACKAGE BODY test1


Output SQL: Java StringBuffer


SQL.append("/* Comment... embedded in double quotes \"select embedded_double_query \n");
SQL.append("from mytable\" */ \n");
SQL.append("/* Embedded in single quotes 'select embedded_single_query from mytable' \n");
SQL.append("*/ \n");
SQL.append("CREATE OR REPLACE PACKAGE BODY test1 \n");

Section Alignment
Option 1: Right Align Master Keywords:
Option on
BEGIN
  IF NVL(g_emplid1,'X') <> p_emplid THEN
    BEGIN
      FOR emp_rec IN c_empl
      LOOP
        --Align on comments example
         SELECT
          1
           INTO
          var
          WHERE
          EXISTS

Option off
BEGIN
  IF NVL(g_emplid1,'X') <> p_emplid THEN
    BEGIN
      FOR emp_rec IN c_empl
      LOOP
        --Align on comments example
        SELECT
          1
        INTO
          var
        WHERE
          EXISTS

Option 2: Align Equal sign (= < > ...)
Option on
                                  AND tv.column2 >= DATE(tv.column4)
                                  AND tv.column3  < DATE(tv.column15)
                                  GROUP BY
                                    tv.column1,
                                    tv.column2
                                ) AS libraryprod,
                                db1.table2 th
                              WHERE
                                th.column1   =libraryprod.column1
                              AND th.column2 =libraryprod.column2

Option off
                                  AND tv.column2 >= DATE(tv.column4)
                                  AND tv.column3 <  DATE(tv.column15)
                                  GROUP BY
                                    tv.column1,
                                    tv.column2
                                ) AS libraryprod,
                                db1.table2 th
                              WHERE
                                th.column1 =libraryprod.column1
                              AND th.column2 =libraryprod.column2

Option 3: Align Operator signs (* - + ...)

Not visible in testcase would need to manafacture example.

Option 4: Align on "AS" keyword

Option on:
          SELECT
            price.col1      AS col1,
            price.col2      AS col2,
            price.col3      AS col3,
            MAX(price.col4) AS col4,
            MAX(price.col5) AS col5,
            MAX(price.col6) AS col6, -- comment1
            MAX(price.col7) AS col7
Option off:
          SELECT
            price.col1 AS col1,
            price.col2 AS col2,
            price.col3 AS col3,
            MAX(price.col4) AS col4,
            MAX(price.col5) AS col5,
            MAX(price.col6) AS col6, -- comment1
            MAX(price.col7) AS col7

Option 5: Align on commas.
Option on
            SELECT
              col1    , -- first field
              longcol2, --second field
              midcol3 , -- 3rd field
            FROM
              tble1

Option Off
            SELECT
              col1,     -- first field
              longcol2, --second field
              midcol3,  -- 3rd field
            FROM
              tble1

Option 6: Align line comments "--"
Option on
            SELECT
              col1,     -- first field
              longcol2, --second field
              midcol3,  -- 3rd field
            FROM
              tble1

Option off
            SELECT
              col1, -- first field
              longcol2, --second field
              midcol3, -- 3rd field
            FROM
              tble1

Option 7: Align "||" at end of line (needed to put newlines before and not after || to see it)
option on
            SELECT
              col1     ||
              longcol2 ||
              midcol3  ||
              col4 ,
              col1
            FROM
              tbl

option off
            SELECT
              col1 ||
              longcol2 ||
              midcol3 ||
              col4 ,
              col1
            FROM
              tbl

Option 8: Align variable declaration for stored procedures.

Option on

CREATE OR REPLACE PACKAGE BODY test1
IS
  g_column1               VARCHAR2(17) := NULL;
  g_column2               VARCHAR2(52) := NULL;
  g_column3_from_column22 VARCHAR2(25) := NULL;
  g_column_4711           VARCHAR2(11) := NULL;

Option off

CREATE OR REPLACE PACKAGE BODY test1
IS
  g_column1 VARCHAR2(17)               := NULL;
  g_column2 VARCHAR2(52)               := NULL;
  g_column3_from_column22 VARCHAR2(25) := NULL;
  g_column_4711 VARCHAR2(11)           := NULL;


Section Indentation

Option 1: Spaces: number of spaces to indent:

Option set to 2 (need to leave the UI element for your option to be applied)

BEGIN
  IF NVL(g_emplid1,'X') <> p_emplid THEN
    BEGIN
      FOR emp_rec IN c_empl
      LOOP
        --Align on comments example
        SELECT

option set to 10

BEGIN
          IF NVL(g_emplid1,'X') <> p_emplid THEN
                    BEGIN
                              FOR emp_rec IN c_empl
                              LOOP
                                        --Align on comments example
                                        SELECT

Option 2: Use tabulator

Uses tab character instead of spaces. (Not visible in example)

Option 3: Indent Main Keyword 2x

Option on

BEGIN
  IF NVL(g_emplid1,'X') <> p_emplid THEN
    BEGIN
      FOR emp_rec IN c_empl
      LOOP
        --Align on comments example
        SELECT
            1
          INTO
            var
          WHERE
            EXISTS

Option off

BEGIN
  IF NVL(g_emplid1,'X') <> p_emplid THEN
    BEGIN
      FOR emp_rec IN c_empl
      LOOP
        --Align on comments example
        SELECT
          1
        INTO
          var
        WHERE
          EXISTS

Option 4: Indent AND/OR
Option on

            WHERE
              (
                (
                  1+1
                )
                =2
              )
              AND
              (
                22222*3 = 44
              )

Option off

            WHERE
              (
                (
                  1+1
                )
                =2
              )
            AND
              (
                22222*3 = 44
              )

Section Line breaks:
Option 1: Schema Type
Option 1-line SQL:
  IF NVL(g_emplid1,'X') <> p_emplid THEN
    BEGIN
      FOR emp_rec IN c_empl
      LOOP /*--Align on comments example*/ SELECT 1 INTO var WHERE EXISTS (SELECT col1,                                                                                                                                                                              /*-- first field*/ longcol2, /*--second field*/ midcol3, /*-- 3rd field*/ FROM  tble1 WHERE ((1+1)=2) AND (22222*3 = 44)); /*-- align || at end of line example*/ SELECT 1 INTO var WHERE EXISTS (SELECT col1 || longcol2 || midcol3 || col4 , col1 FROM  tbl);

Other:
    BEGIN
      FOR emp_rec IN c_empl
      LOOP
        --Align on comments example
        SELECT
          1
        INTO
          var
        WHERE
          EXISTS
          (
            SELECT
              col1,     -- first field
              longcol2, --second field
              midcol3,  -- 3rd field
            FROM
              tble1
...

Option 2: After SELECT, FROM, WHERE...
option on

      LOOP
        --Align on comments example
        SELECT
          1
        INTO
          var
        WHERE
          EXISTS
          (

option off

      LOOP
        --Align on comments example
        SELECT 1
        INTO var
        WHERE EXISTS
          (

Option 3: Before Comma
option on

            SELECT
              col1
            , -- first field
              longcol2
            , --second field
              midcol3
            , -- 3rd field
            FROM

option off

            SELECT
              col1,     -- first field
              longcol2, --second field
              midcol3,  -- 3rd field
            FROM

Option 4: Before AND/OR
option on

                                  WHERE
                                    tv.column1     <> 'Y'
                                    AND tv.column1 IN ( 'a' , '1' , '12' ,
                                    '123' , ' 1234' , '12345' , '123456' ,
                                    '1234567' , '12345678' , '123456789' ,
                                    '1234567890' ,
                                    '1 12 123 1234 12345 123456 1234567 12345678'
                                    , 'b' , 'c' )
                                    AND tv.column2 >= DATE(tv.column4)
                                    AND tv.column3  < DATE(tv.column15)

option off

                                  WHERE
                                    tv.column1 <> 'Y' AND tv.column1 IN ( 'a' ,
                                    '1' , '12' , '123' , ' 1234' , '12345' ,
                                    '123456' , '1234567' , '12345678' ,
                                    '123456789' , '1234567890' ,
                                    '1 12 123 1234 12345 123456 1234567 12345678'
                                    , 'b' , 'c' ) AND tv.column2 >= DATE(
                                    tv.column4) AND tv.column3    < DATE(

Option 5: before "||"
option on

            SELECT
              col1
              || longcol2
              || midcol3
              || col4 ,
              col1
            FROM
              tbl

option off

            SELECT
              col1 || longcol2 || midcol3 || col4 ,
              col1
            FROM
              tbl

Option 6: After Comma
option on

            SELECT
              col1
              || longcol2
              || midcol3
              || col4 ,
              col1

option off

            SELECT
              col1
              || longcol2
              || midcol3
              || col4 , col1

Option 7: After AND/OR
option on

                    AND
                    (
                      librarystat.column5 = 'I'
                      OR
                      librarystat.column4 = 'Gold'
                      OR
                      librarystat.column5 = 'Bold'
                    )
                    AND
                    librarystat.column6 <= 'Z74'

option off

                    AND
                    (
                      librarystat.column5    = 'I'
                      OR librarystat.column4 = 'Gold'
                      OR librarystat.column5 = 'Bold'
                    )
                    AND librarystat.column6 <= 'Z74'

Option 8: After "||"
option on

            SELECT
              col1
              ||
              longcol2
              ||
              midcol3
              ||
              col4 , col1
            FROM
              tbl

option off

            SELECT
              col1
              || longcol2
              || midcol3
              || col4 , col1
            FROM
              tbl

Option 9: Number of commas per line

option 1

              SELECT
                store.column1,
                -- =========================================
                --
                -- =========================================
                CAST (store.column2 AS INTEGER) AS column2,
                store.column3,
                store.column4,
                store.column5,
                SUBSTR(store.column6,11,1) AS column6,
                store.column7              AS column7

option 5

              SELECT
                store.column1,
                -- =========================================
                --
                -- =========================================
                CAST (store.column2 AS INTEGER)                          AS column2, store.column3,
                store.column4, store.column5, SUBSTR(store.column6,11,1) AS
                column6, store.column7                                   AS
                column7

Option 10: For brackets in AND, OR, WHEN...

option on

            SELECT
              col1,     -- first field
              longcol2, --second field
              midcol3,  -- 3rd field
            FROM
              tble1
            WHERE
              (
                (
                  1+1
                )
                =2
              )
              AND
              (
                22222*3 = 44
              )
          );

option off

            SELECT
              col1,     -- first field
              longcol2, --second field
              midcol3,  -- 3rd field
            FROM
              tble1
            WHERE ((1   +1)=2)
              AND (22222*3 = 44)

Option 11: For subselect brackets

option on

        WHERE
          EXISTS
          (
            SELECT
              col1,     -- first field
              longcol2, --second field
              midcol3,  -- 3rd field
            FROM

option off

        WHERE
          EXISTS
          (SELECT
            col1,     -- first field
            longcol2, --second field
            midcol3,  -- 3rd field
          FROM

Option 12: For JOIN Statements

option on

              LEFT OUTER JOIN db1.v_table3 librarystat
              ON
                librarystat.column1     = library.column1
                AND librarystat.column2 = library.column2
                OR
                (
                  librarystat.column4     = library.column4

option off

              LEFT OUTER JOIN db1.v_table3 librarystat ON librarystat.column1 =
                library.column1 AND librarystat.column2                       =
                library.column2 OR
                ( librarystat.column4 = library.column4 AND librarystat.column5
                                      = library.column5

Option 13: Max Line Width

option 30

          SELECT
            price.col1 AS
            col1, price.col2
            AS col2,
            price.col3 AS
            col3, MAX(
            price.col4) AS
            col4, MAX(
            price.col5) AS
            col5, MAX(
            price.col6) AS
            col6, -- comment1
            MAX(price.col7)
            AS col7
            /*  comment2 */

option 80

          SELECT
            price.col1 AS col1, price.col2 AS col2, price.col3 AS col3, MAX(
            price.col4) AS col4, MAX(price.col5) AS col5, MAX(price.col6) AS
            col6, -- comment1
            MAX(price.col7) AS col7
            /*  comment2 */

Option 14: Threshold for small SQL

option (value) 20

            FROM
              (SELECT
                library.column1, library.column2, library.column3,
                CASE library.column4
                  WHEN cheap
                  THEN digits(library.column27) concat library.column28
                  ELSE 123456
                END AS column4,
                CASE library.column5
                  WHEN expensive
                  THEN digits(library.column27) concat library.column28
                  ELSE 123456
                END AS library.column6,
                CASE column7
                  WHEN free
                  THEN digits(library.column27) concat library.column28
                  ELSE 123456
                END AS column7

option 10000

            FROM
              ( SELECT library.column1, library.column2, library.column3, CASE
                    library.column4 WHEN cheap THEN digits(library.column27)
                    concat library.column28 ELSE 123456 END AS column4, CASE
                    library.column5 WHEN expensive THEN digits(library.column27
                    ) concat library.column28 ELSE 123456 END AS
                library.column6, CASE column7 WHEN free THEN digits(
                    library.column27) concat library.column28 ELSE 123456 END
                AS column7 FROM

Option 15: Force Linebreak before line comments

option on

          (SELECT
            col1,
            -- first field
            longcol2,
            --second field
            midcol3,
            -- 3rd field
          FROM
            tble1

option off

          (SELECT
            col1,     -- first field
            longcol2, --second field
            midcol3,  -- 3rd field
          FROM
            tble1

Option 16: Blank lines

option Add Extra

      END LOOP;

    END;

  END IF;

END testfunction;
/************************************************************************
/*  Multi line comment */
/************************************************************************/
/***********************************************************************/
--

option Preserve Existing

      END LOOP;
    END;
  END IF;
END testfunction;
/************************************************************************
/*  Multi line comment */
/************************************************************************/

/***********************************************************************/
--

Section CASE linebreaks
option all on

              (SELECT
                library.column1, library.column2, library.column3,
                CASE library.column4
                  WHEN cheap
                  THEN digits(library.column27) concat library.column28
                  ELSE 123456
                END AS column4,

option all off

              (SELECT
                library.column1, library.column2, library.column3, CASE
                    library.column4 WHEN cheap THEN digits(library.column27)
                    concat library.column28 ELSE 123456 END AS column4,

Section White Space.
Option 1: Spaces around Operators:
option no spaces

          WHERE
            (
              (
                1+1
              )
              =2

option 1 space

          WHERE
            (
              (
                1 + 1
              )
              = 2

Option 2: Spaces around commas:

option no spaces

                SUBSTR(store.column6,11,1) AS column6,

option 1 space around

                SUBSTR(store.column6 , 11 , 1) AS column6 ,

Option 3: Spaces around brackets

option no spaces

          (SELECT
            col1
            ||
            longcol2
            ||
            midcol3
            ||
            col4,col1

option 1 space around

          (SELECT
            col1
            ||
            longcol2
            ||
            midcol3
            ||
            col4 , col1

Section Other
Option 1:Delete Comment
option on

CREATE OR REPLACE PACKAGE BODY test1
IS

option off

/* Comment... embedded in double quotes "select embedded_double_query from
mytable" */
/* Embedded in single quotes 'select embedded_single_query from mytable' */
CREATE OR REPLACE PACKAGE BODY test1
IS

Option 2: Put -- comment between /* ... */
option on

    BEGIN
      FOR emp_rec IN c_empl
      LOOP
        /*--Align on comments example*/

option off

      FOR emp_rec IN c_empl
      LOOP
        --Align on comments example

Option 3: Force formatter output on difference

A lot of formatting is only whitespace change - so a non whitespace difference is an error - in production this is too much trouble. When the formatter was being developer it was useful to prevent changes being visible.

Option 4: case change

options keywords uppercase

FUNCTION testfunction(
    p_column12 IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  IF NVL(g_emplid1 , 'X') <> p_emplid THEN

option whole sql lowercase

function testfunction(
    p_column12 in varchar2)
  return varchar2
is
begin
  if nvl(g_emplid1 , 'X') <> p_emplid then

3 comments:

ChrisR said...

The main problems our team has with the formatter (even in 4.0) are the line breaks:

What about for each keyword or symbol you have a drop down which has a choice of when to break line - either 'Before'|'After'|None

for example, we would like to configure AINSI joins to look like

from table_1 a
join table_2 b on (a.id = b.id)

The current formatter lets us control line breaks for JOIN, but not for ON.

The second issue, is that the same formatting is applied to SQL, DDL, and PL/SQL and we would like it to be different for each case.

For example, we don't want to limit the number of commas per line in SQL (but just use line length), whilst in DDL, we wan't to break the line on commas, and not restrict line length.

Thanks for the good work!

Srdjan said...

One of the biggest issue in SQL Developer formatter is I think the format for function or procedure call with a lot of parameters. I.e. I would like to have one parameter per line; however formatter put all parameters in one line or just breaks at max line size. It does not respoect the condition to break on commas.

Unknown said...

Hi, Nice description about Text version of PLSQL Formatter options in SQLDeveloper 4.0.Thanks, its really helped me......

-Aparna
Theosoft