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
Monday, 22 July 2013
Sunday, 21 July 2013
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
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.
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.
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.
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
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
Subscribe to:
Posts (Atom)