Rough notes on Proxy connections

Oracle® Database Security Guide
12c Release 1 (12.1)
E17607-19
Creating Proxy User Accounts and Authorizing Users to Connect Through Them
http://docs.oracle.com/cd/E16655_01/network.121/e17607/authentication.htm#DBSEG99851

I just want to document in 'Connections with Proxy Authentication' that there are two ways of doing proxy users a single session way and a double session way It looks like people will in future go for the simpler single session way.

There are two ways of doing proxy logins:
where p1 is proxy user and c1 is proxy client:

(Mapping over from the doc link: p1=appuser, c1=preston)
1/single session method (if no 2nd password or distinguished name required)
on main connection popup
user: p1[c1]
password: p1

2/Two session method
Main Connection popup
user: p1
password p1

popup connection authentication
-proxy client is correct - was (2009) labeled proxy user which is the name for p1[

proxy client: c1
none or password or distinguished name

password:
(
To create proxy client with password authentication:
CREATE USER c1 IDENTIFIED BY ;
ALTER USER c1 GRANT CONNECT THROUGH p1 AUTHENTICATED USING PASSWORD;
GRANT create session TO c1;
)

TODO - cover Distinguished Name authentication

User check on sqldeveloper (worksheet) issue

1/provide a full reproducible testcase so a bug can be logged and the issue can be worked in Oracle using the source code. Not sure about 4.0EA but if it happens with production SQLDeveloper and production database - you can go through official Oracle support.
2/Do not install SQLDeveloper 4.0 EA1 on top of another SQLDeveloper installation
3/Use latest Java7
4/Are you using multibyte characters? (Chinese or Japanese being the most widely used in support calls)
5/Try as a operating system different user In case your preferences are corrupt. (you can move your existing preferences out of the way - but using another user seems to avoid any support/user error possibilities.)
6/Try with SQLDeveloper 3.2.2
7/Try with SQLPLUS
8/debugging logging: Most serious java issues cause a message to the screen ('Java Stack Trace') if you start sqldeveloper from ide/sqldeveloper/bin from the command line There is also View->log->Logging Pane - which probably gives too much information [This blog has an introduction to tracing http://totierne.blogspot.co.uk/2013/02/introduction-to-tracing.html entry for the more adventurous - there is lots of information but the important bit is you can see exactly what is being sent to and from the database.]
9/Try the query as run statement, run script, try count (*), try a subset of columns, try to_char() the columns so the data transformation is mostly done in the Oracle Database
10/Are you using thick(i.e.OCI) or thin jdbc connection type? What driver are you using? What database are you using(try 'show jdbc' worksheet command) Is tools-->preferences->database->advanced->Use OCI/Thick driver checked
11/If trying with sys - try as another user.
12/When installing run as the installing user once so the JDK version can be put in the configuration file.
13/Useful SQLPLUS emulation features:
set echo on
set verify on
show jdbc
spool

Text version of PLSQL Formatter options in SQLDeveloper 4.0 (Now With Screenshots)

Introduction

This blog post is to display the different formatting options for Oracle SQLDeveloper.
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. (Now put in - could do with better cropping in the images) .

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

Double bind reference now work

Double bind now works see below:
 
begin
  :v_status := 1234;
  raise_application_error(-20000, 'account past due.');
exception when others then
  :v_status := -1;
end;
/

The double bind variable :v_status confused SQLDeveloper and JDBC works now.
Note this fancy rewrite only happens when necessary - so issues with this approach only affect double bind code.

The internal details are:
 The workaround (internal Oracle Developer) applied to apex has now been automated within sqldeveloper. If (SQLDeveloper user) uses multiple binds sqldeveloper will rewrite:

begin
  :v_status := 1234;
  raise_application_error(-20000, 'account past due.');
exception when others then
  :v_status := -1;
end;
/

to:


1/setup:
2/rewritten plsql:
3/get output variables:
 
1/setup:
in java :v_statusinit =  java bind v_status

2/rewritten plsql:
declare
myvstatus number:=:v_statusinit;
begin
  begin
    myvstatus := 1234;
    raise_application_error(-20000, 'account past due.');
  exception when others then
    myvstatus := -1;
  end;
  :v_status := myvstatus;
end;
/
Note - additional code is actually written with no newlines, so debug/error line numbers are not affected.

3/get output variables:
in java java bind v_status = :v_status

The only nasty side effect is if you put a varchar2(20) into a varchar2(10) bind it will error out (rather than truncate) at the "myvstatus number:=:v_statusinitmy;"  stage. Could possibly code for this - but would need to handle for example varchar2(20 byte) and varchar2(20 chat) code paths for the rewritten PL/SQL.

Rip SQL into and out of Java

Advanced parameters: to rip sql in and out of say java using Output type, Enclosed In, Escaped By. so:

SQL.append("SELECT 1 FROM dual"); 

 -> make a change ->

(all one line)
SQL.append("SELECT 1,xxxxxxxxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxx FROM dual"); 
 
-> go to right click advanced format output as StringBuffer with:
 enclosed in " 
escaped by \ 
becomes: 

SQL.append("SELECT \n");
SQL.append(" 1 , xxxxxxxxxxxxxxxxxxxxxxxxxxxxx , xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \n");
SQL.append(" , xxxxxxxxxxxxxxxxxxxxxxxxx , xxxxxxxx \n");
SQL.append("FROM \n");
SQL.append(" dual");

i.e. it rips out the SQL (removing \n), formats it (core formatter into SQL)  then puts in the SQL.append (" at the start of the line and \n"); at the end of the line "); at the end of the last line.

What can go wrong? Fancy java. Should work but less tested for vb/sql in sql/php ...

What I should fix? Advanced formatter settings are not remembered between right clicks.

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