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
No comments:
Post a Comment