Skip to Main Content

SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment

Dilly1990Jan 16 2020 — edited Apr 19 2020

We are migrating from Toad to Sql developer and below are few requirement to format the code on the Sql developer.

All the code snippets show below are from PL/Sql.

1) Line break after the hint on the Select

SELECT /*+ use_hash(rtd st incl str) */ rtd.str_no AS str_no

,TRUNC (rtd.tx_dte_tme) AS tx_dte

,NVL (st.trml_typ_cd,’UNK’) AS trml_typ_cd

Expected:

SELECT /*+ use_hash(rtd st incl str) */

rtd.str_no AS str_no

,TRUNC ( rtd.tx_dte_tme) AS tx_dte

,NVL (st.trml_typ_cd, ‘UNK’) AS trml_typ_cd

2) Extra line break before Cursor declaration only when variable/constant defined above.

c_defaultBsStrNo CONSTANT STORE.str_no%TYPE := 0;

CURSOR l_rtlTndrDtlCur IS

SELECT * from dual

Expected:

c_defaultBsStrNo CONSTANT STORE.str_no%TYPE := 0;

CURSOR l_rtlTndrDtlCur IS

SELECT * from dual

3) Remove line beak on Procedure / Function / Cursor parameter declaration. Line break are need only when the declaration exceeds MAX CHAR LINE WIDTH

PROCEDURE insTndrOnly (

p_runDte IN DATE

,p_txDte IN DATE

);

Expected :

PROCEDURE insTndrOnly (p_runDte IN DATE, p_txDte IN DATE);

4) When invoking the procedure, line breaksBeforeComma on each parameter and also parenthesis need to be aligned

pastedImage_11.png

Expected:

pastedImage_12.png

5) if_stmt – Line break before on the and_expr.

IF (l_prevStrNo = l_tndrCrctnChgRec.str_no) AND (l_prevTrmlNo = l_tndrCrctnChgRec.trml_no) AND (l_prevTxNo = l_tndrCrctnChgRec.tx_no) AND (l_prevTxDte = l_tndrCrctnChgRec.tx_dte) AND (l_prevTrmlTypCd = l_tndrCrctnChgRec.trml_typ_cd) AND (l_prevRunDTe = l_tndrCrctnChgRec.run_dte)

THEN l_prevTndrActnCd = 0;

   ELSE l_prevTndrActnCd = 1;

   END IF;

Expected:

pastedImage_16.png

Below are the changes made to the Advanced format:

pastedImage_20.png

I would really appreciate, if you could help me out.

Comments
Post Details
Added on Jan 16 2020
10 comments
2,384 views