13 Replies Latest reply: Nov 21, 2012 10:30 AM by Turloch O'Tierney-Oracle RSS

    Searching for deailed info on code formatting rules

    EdStevens
      SQL Developer 3.2.20.09

      Am looking for some detailed explanation of the various options for the SQL Formatting rules. The help, under 'Database: SQL Formatter' is pretty thin. I'd like to see something that explains the behavior of each discreet formatting option. While some may seem self-evident, not all are and even those that appear to be don't seem to behave the way someone might think or want. It would be nice if there were some detailed document to help with "that's not what I wanted" types of questions.
        • 1. Re: Searching for deailed info on code formatting rules
          rp0428
          >
          Am looking for some detailed explanation of the various options for the SQL Formatting rules.
          >
          Good luck with that; I gave up long ago.

          The SQL Developer User's Guide is easier to use than the online help but, as far as I can tell, contains about the same limited info.
          http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#CHDCAGJI

          You can do the actual formatting offline if you want
          http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#sthref219

          But that just runs the same code and doesn't explain anything.
          • 2. Re: Searching for deailed info on code formatting rules
            EdStevens
            rp0428 wrote:
            >
            Am looking for some detailed explanation of the various options for the SQL Formatting rules.
            >
            Good luck with that; I gave up long ago.

            The SQL Developer User's Guide is easier to use than the online help but, as far as I can tell, contains about the same limited info.
            http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#CHDCAGJI

            You can do the actual formatting offline if you want
            http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#sthref219
            Actually, I had seen that second one, and thought it was awfully dangerous to have a command named the same as a standard OS command. Especially an os command as destructive as 'format' . . . I won't go into details, but will say I dodged a bullet on that one ...
            But that just runs the same code and doesn't explain anything.
            Ok. I guess the important thing is to know I haven't overlooked the obvious.

            Thx.
            • 3. Re: Searching for deailed info on code formatting rules
              Jeff Smith Sqldev Pm-Oracle
              We can always endeavor to improve the help, and I've noted this as an area of improvement for future releases.

              In the meantime, you can usually get a pretty good idea of what a preference does based on the code preview panel as you toggle it on and off.

              I'm assuming that's failed you, so are there a few preferences you had questions about? We can try to get you better answers on those right now.
              • 4. Re: Searching for deailed info on code formatting rules
                rp0428
                >
                Ok. I guess the important thing is to know I haven't overlooked the obvious.
                >
                Maybe I overlooked the obvious. When you said this
                >
                I'd like to see something that explains the behavior of each discreet formatting option. While some may seem self-evident, not all are and even those that appear to be don't seem to behave the way someone might think or want.
                >
                I assumed you were looking for a detailed explanation of each check box or value and how to use it. Now I am wondering if you even got into the actual formatting specification dialog where you select the checkboxes or values.

                Did you? Tools -> Preferences -> SQL Formatter -> Oracle Formatting.

                Then in the Profile dropdown select SQL and then select Edit.

                Now the tree in the left pane has all of the options that you can set. In 3.2.20.09 there are 7 children
                1. InputOutput
                2. Alignment
                3. Indentation
                4. Line Breaks
                5. CASE Linebreaks
                6. White Space
                7. Other

                You can expand each of those tree items to see the details.

                Is THAT what you were looking for? Or did you already find that and need more information about some or all of those options?
                • 5. Re: Searching for deailed info on code formatting rules
                  EdStevens
                  rp0428 wrote:
                  >
                  Ok. I guess the important thing is to know I haven't overlooked the obvious.
                  >
                  Maybe I overlooked the obvious. When you said this
                  >
                  I'd like to see something that explains the behavior of each discreet formatting option. While some may seem self-evident, not all are and even those that appear to be don't seem to behave the way someone might think or want.
                  >
                  I assumed you were looking for a detailed explanation of each check box or value and how to use it. Now I am wondering if you even got into the actual formatting specification dialog where you select the checkboxes or values.

                  Did you? Tools -> Preferences -> SQL Formatter -> Oracle Formatting.

                  Then in the Profile dropdown select SQL and then select Edit.

                  Now the tree in the left pane has all of the options that you can set. In 3.2.20.09 there are 7 children
                  1. InputOutput
                  2. Alignment
                  3. Indentation
                  4. Line Breaks
                  5. CASE Linebreaks
                  6. White Space
                  7. Other

                  You can expand each of those tree items to see the details.

                  Is THAT what you were looking for? Or did you already find that and need more information about some or all of those options?
                  Yes, I found the formatting options, though I thought their location to be a bit obtuse, and I had to dig a bit. I really would have expected them to be under "Code Editor" instead of "Database" (Tools > Preferences > Database > SQL Formatter). My questions start there. See my response to Jeff Smith for details.
                  • 6. Re: Searching for deailed info on code formatting rules
                    EdStevens
                    Jeff Smith SQLDev PM wrote:
                    We can always endeavor to improve the help, and I've noted this as an area of improvement for future releases.

                    In the meantime, you can usually get a pretty good idea of what a preference does based on the code preview panel as you toggle it on and off.

                    I'm assuming that's failed you, so are there a few preferences you had questions about? We can try to get you better answers on those right now.
                    Yes, several questions.

                    First, right off the bat is the profiles. It would seem rather obvious with this option that one can have different profiles, each with its own combination of rules. But how does one determine which profile is in use at any given time? I see nothing in the help about profiles.

                    Yes, I noticed that the example pane would reflect some of the rules. And for those that are reflected in the example pane - some are pretty self-evident, but others fall under the category of "Ok, I see that, but fail to understand the meaning/logic behind it and how it might apply in a less simplistic example.

                    Then there are the items that just leave me scratching my head on what they even mean. Like all three items under "InputOutput". Or under "Line Breaks, the term "Schema Type", "large SQL" "small SQL", "More Newlines". Or under "Other", the item "Force formatter output on difference".

                    And under "Line Breaks" (which is the area I'm most interested in) things are not working as expected ... primarily and especially breaks before/after comma and before/after concatenation ||.

                    Oh, and while we're at it, there's a glaring spelling error .. "Threashold for small SQL"
                    • 7. Re: Searching for deailed info on code formatting rules
                      Jeff Smith Sqldev Pm-Oracle
                      But how does one determine which profile is in use at any given time?
                      The active profile in the Profile Selector seen in the Preferences dialog determines which profile is in use.
                      things are not working as expected ... primarily and especially breaks before/after comma and before/after concatenation ||.
                      Can you provide an example showing your expected vs actual?

                      Here's a quick writeup on how to get leading commas when formatting your SQL
                      http://www.thatjeffsmith.com/archive/2011/11/sql-developer-quick-tip-leading-comma-formatting/

                      If you set the 'Schema Type' to small SQL - you have to mind the 'Threshold for small SQL' as that allows you to leave code lines that don't meet that threshold untouched, which is nice if you want to avoid unnecessary line breaks for very small lines of code.

                      Also, I think the reason we have the Formatting rules under Database and not Code Editor is b/c we employ the formatting in many places in the product - not just the code editors.
                      Like all three items under "InputOutput".
                      That's for the Advanced Format - which lets you take your SQL statement and have it re-formatted for ASP, C#, Java, etc.
                      • 8. Re: Searching for deailed info on code formatting rules
                        EdStevens
                        Jeff Smith SQLDev PM wrote:
                        But how does one determine which profile is in use at any given time?
                        The active profile in the Profile Selector seen in the Preferences dialog determines which profile is in use.
                        check.
                        things are not working as expected ... primarily and especially breaks before/after comma and before/after concatenation ||.
                        Can you provide an example showing your expected vs actual?
                        Ok, here's some raw sql:
                        create or replace 
                        PROCEDURE fix_jobs_timezone(
                            p_jobschema_in IN VARCHAR2 default null) authid current_user
                        IS
                          no_schema EXCEPTION;
                          no_mailrcpt exception;
                          
                        type sched_jobs_tbl_type
                        IS
                          TABLE OF dba_scheduler_jobs.job_name%TYPE INDEX BY binary_integer;
                          t_sched_jobs sched_jobs_tbl_type;
                          
                          c_program_name VARCHAR2(30)  := 'fix_jobs_timezone';
                          
                          l_mailto       VARCHAR2(512) := 'estevens';
                          l_mailsubj     VARCHAR2(80)  := 'TEST - IGNORE';
                          l_mailmsg      varchar2(1024);
                          l_sqlcode      NUMBER;
                          l_errm         VARCHAR2(64);
                          l_job          VARCHAR2(128);
                        BEGIN
                          scott.set_job_run_ctx('JOB_BATCH_NUM', TO_CHAR(scott.PROGRAM_LOG_BATCH_SEQ.NEXTVAL));
                            
                          scott.LOG (c_program_name, '*** Program Start - ' || c_program_name || ' ***');
                            
                          IF p_jobschema_in IS NULL THEN
                            raise no_schema;
                          END IF;
                          
                          scott.LOG (c_program_name, 'Processing jobs for schema ' ||p_jobschema_in);
                            
                          SELECT job_name bulk collect INTO t_sched_jobs FROM dba_scheduler_jobs WHERE owner = p_jobschema_in ORDER BY job_name ;
                            
                          FOR i IN t_sched_jobs.first .. t_sched_jobs.last
                          LOOP
                            l_job := '"' || p_jobschema_in || '"."'||t_sched_jobs(i) || '"';
                            scott.LOG (c_program_name, 'Processing job '|| l_job);
                              
                            dbms_scheduler.set_attribute_null (l_job, 'START_DATE');
                          END LOOP;
                          
                          scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
                            
                        EXCEPTION
                        WHEN no_schema THEN
                          scott.LOG (c_program_name, 'Missing input parameter');
                          scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
                            
                          l_mailsubj := l_mailsubj || ' - Missing input parameter';
                          l_mailmsg := 'Procedure requires a schema name as an input parameter.';
                          scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
                            
                          
                        WHEN no_data_found OR value_error THEN
                          scott.LOG (c_program_name, 'No jobs selected for processing' ) ;
                          scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
                        
                          l_mailsubj := l_mailsubj || ' - no jobs processed';
                          l_mailmsg := 'No jobs were found to process for schema ' || p_jobschema_in;
                          scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
                          
                        WHEN OTHERS THEN
                          l_sqlcode := SQLCODE;
                          l_errm    := SUBSTR(SQLERRM, 1,64);
                          scott.LOG (c_program_name, l_errm);
                          l_mailsubj := l_mailsubj || ' - ' || l_errm;
                          l_mailmsg  := 'Error code: '|| l_sqlcode|| ': ' ||l_errm;
                          scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
                            
                          scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
                        END;
                        And here's after formatting with a profile using 'small sql' and breaks before commas and concatenation. Other values of that profile that look like they could be key are
                        - number of commas per line = 1
                        - max line witdth = 80
                        threshold for small sql = 20
                        CREATE OR REPLACE
                        PROCEDURE fix_jobs_timezone(
                            p_jobschema_in IN VARCHAR2 DEFAULT NULL) authid current_user
                        IS
                          no_schema   EXCEPTION;
                          no_mailrcpt EXCEPTION;
                        type sched_jobs_tbl_type
                        IS
                          TABLE OF dba_scheduler_jobs.job_name%TYPE INDEX BY binary_integer;
                          t_sched_jobs sched_jobs_tbl_type;
                          c_program_name VARCHAR2(30)  := 'fix_jobs_timezone';
                          l_mailto       VARCHAR2(512) := 'estevens';
                          l_mailsubj     VARCHAR2(80)  := 'TEST - IGNORE';
                          l_mailmsg      VARCHAR2(1024);
                          l_sqlcode      NUMBER;
                          l_errm         VARCHAR2(64);
                          l_job          VARCHAR2(128);
                        BEGIN
                          scott.set_job_run_ctx('JOB_BATCH_NUM', TO_CHAR(
                          scott.PROGRAM_LOG_BATCH_SEQ.NEXTVAL));
                          scott.LOG (c_program_name, '*** Program Start - ' || c_program_name || ' ***'
                          );
                          IF p_jobschema_in IS NULL THEN
                            raise no_schema;
                          END IF;
                          scott.LOG (c_program_name, 'Processing jobs for schema ' ||p_jobschema_in);
                          SELECT
                            job_name bulk collect
                          INTO
                            t_sched_jobs
                          FROM
                            dba_scheduler_jobs
                          WHERE
                            owner = p_jobschema_in
                          ORDER BY
                            job_name ;
                          FOR i IN t_sched_jobs.first .. t_sched_jobs.last
                          LOOP
                            l_job := '"'                                || p_jobschema_in || '"."'||t_sched_jobs(i) || '"';
                            scott.LOG (c_program_name, 'Processing job '|| l_job);
                            dbms_scheduler.set_attribute_null (l_job, 'START_DATE');
                          END LOOP;
                          scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
                        EXCEPTION
                        WHEN no_schema THEN
                          scott.LOG (c_program_name, 'Missing input parameter');
                          scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
                          l_mailsubj := l_mailsubj                        ||
                          ' - Missing input parameter';
                          l_mailmsg := 'Procedure requires a schema name as an input parameter.';
                          scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
                        WHEN no_data_found OR value_error THEN
                          scott.LOG (c_program_name, 'No jobs selected for processing' ) ;
                          scott.LOG (c_program_name, '*** Program End - '           || c_program_name || ' ***');
                          l_mailsubj := l_mailsubj                                  || ' - no jobs processed';
                          l_mailmsg  := 'No jobs were found to process for schema ' || p_jobschema_in;
                          scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
                        WHEN OTHERS THEN
                          l_sqlcode := SQLCODE;
                          l_errm    := SUBSTR(SQLERRM, 1,64);
                          scott.LOG (c_program_name, l_errm);
                          l_mailsubj := l_mailsubj    || ' - ' || l_errm;
                          l_mailmsg  := 'Error code: '|| l_sqlcode|| ': ' ||l_errm;
                          scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
                          scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
                        END;
                        As you can see, none of the commas or '||' caused a line break. In fact, if I had manually formatted my code and then asked SQL Dev to apply formatting rules ... it actually removed line breaks. Using just a few lines of simplified test case things worked mostly as I would have expected, but the above block made me persue this line because I thought I was missing something.
                        Here's a quick writeup on how to get leading commas when formatting your SQL
                        http://www.thatjeffsmith.com/archive/2011/11/sql-developer-quick-tip-leading-comma-formatting/

                        If you set the 'Schema Type' to small SQL - you have to mind the 'Threshold for small SQL' as that allows you to leave code lines that don't meet that threshold untouched, which is nice if you want to avoid unnecessary line breaks for very small lines of code.
                        Ok, but there again .. unless I've missed it somehow, the whole concept of what "schema type" means and how it is used is missing from the docs. How am I to know what I'm doing when I select 'small SQL' vs 'large SQL' vs 'customized sql' vs '1 line sql'.
                        >
                        Also, I think the reason we have the Formatting rules under Database and not Code Editor is b/c we employ the formatting in many places in the product - not just the code editors.
                        OK.
                        >
                        Like all three items under "InputOutput".
                        That's for the Advanced Format - which lets you take your SQL statement and have it re-formatted for ASP, C#, Java, etc.
                        Other observations while collecting demo data

                        when in the Edit dialog for a given format profile ... "um, uh, which profile am I working with here? ... guess I'll have to move some windows around and see, since the edit window itself doesn't tell me."

                        Saw some other areas that made me say "Really?", but I don't want to get into a lengthy wish list - especially since I'm just starting to use this tool. I'd like to focus on what's there that I'm overlooking.
                        • 9. Re: Searching for deailed info on code formatting rules
                          Jeff Smith Sqldev Pm-Oracle
                          You're not seeing line breaks because the formatter is just applying those in SQL statements, not in PL/SQL.

                          So this will format -

                          select sysdate || 'stuff' || sydate || 'morestuff' from dual; -->

                          SELECT sysdate
                          || 'stuff'
                          || sydate
                          || 'morestuff'
                          FROM dual;

                          This will not -

                          begin
                          dbms_output.put_line(sysdate || 'stuff' || sysdate || 'more_stuff');
                          end;

                          This discrepancy needs addressed. The preferences dialog and the Help needs updated to reflect the scope of the different formatting rules.
                          it actually removed line breaks.
                          Blank lines should be preserved - that's been targeted for a fix in a future release.
                          • 10. Re: Searching for deailed info on code formatting rules
                            EdStevens
                            Jeff Smith SQLDev PM wrote:
                            You're not seeing line breaks because the formatter is just applying those in SQL statements, not in PL/SQL.

                            So this will format -

                            select sysdate || 'stuff' || sydate || 'morestuff' from dual; -->

                            SELECT sysdate
                            || 'stuff'
                            || sydate
                            || 'morestuff'
                            FROM dual;

                            This will not -

                            begin
                            dbms_output.put_line(sysdate || 'stuff' || sysdate || 'more_stuff');
                            end;

                            This discrepancy needs addressed. The preferences dialog and the Help needs updated to reflect the scope of the different formatting rules.
                            it actually removed line breaks.
                            Blank lines should be preserved - that's been targeted for a fix in a future release.
                            Ok. At least I know I'm not going crazy or overlooking the obvious! ;-)

                            Thanks for sticking with me on this.
                            • 11. Re: Searching for deailed info on code formatting rules
                              Turloch O'Tierney-Oracle
                              Hi EdStevens,

                              There is currently an option to put in newlines *"Line Breaks->More Newlines"* after the format (around blocks/loops)
                              (and after the formatter has stripped the users original newlines). (The option is off by default)

                              I added newlines where it seemed best and achievable in the time available.

                              See the example below for formatted output
                              I have not received any feedback on this - it is off by default.

                              The code below(1) shows output before and after this option is applied uses the test PLSQL code already in the screens for setting the formatter options.

                              -Turloch
                              SQLDeveloper Team

                              (1)Code after and before formatting follows:
                              After:
                              
                              /* 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(
                                  p_column12 IN VARCHAR2)
                                RETURN VARCHAR2
                              IS
                              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
                                        (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
                                        );
                                     
                                      IF emp_rec.empl_rcd# > 0 THEN
                                       
                                        INSERT
                                        INTO table1
                                          /*---------
                                          ---------*/
                                          (
                                            col1,
                                            col2,
                                            col3,
                                            col4,
                                            col5,
                                            col6,
                                            col7
                                          )
                                        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 */
                                        FROM
                                          (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
                                          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
                                              (SELECT INTEGER(SUBSTR(onelibrarysales.column1,11,10)) AS column1,
                                                SUBSTR(onelibrarysales.column2,21,10)                AS column2 ,
                                                onelibrarysales.column3,
                                                onelibrarysales.column4,
                                                SUBSTR(onelibrarysales.column5,31,6) AS column5,
                                                SUBSTR(onelibrarysales.column6,37,2) AS column6,
                                                SUBSTR(onelibrarysales.column7,39,6) AS column7
                                              FROM
                                                (SELECT alllibrarysales.column1,
                                                  alllibrarysales.column2,
                                                  MAX(alllibrarysales.column3)                                                                                                                            AS alllibrarysales.column3 ,
                                                  MAX(CHAR(alllibrarysales.column4,iso) concat CHAR(alllibrarysales.column5,iso) concat digits(alllibrarysales.column6) concat (alllibrarysales.column7)) AS column5
                                                FROM
                                                  (SELECT libraryprod.column1,
                                                    libraryprod.column2,
                                                    libraryprod.column3,
                                                    libraryprod.column4,
                                                    libraryprod.column5,
                                                    libraryprod.column6,
                                                    libraryprod.column7
                                                  FROM
                                                    (SELECT tv.column1,
                                                      tv.column2,
                                                      MAX(digits(tv.column3) concat digits(tv.column4) ) AS librarymax
                                                    FROM db1.v_table1 tv
                                                    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)
                                                    GROUP BY tv.column1,
                                                      tv.column2
                                                    ) AS libraryprod,
                                                    db1.table2 th
                                                  WHERE th.column1 =libraryprod.column1
                                                  AND th.column2   =libraryprod.column2
                                                  ) AS alllibrarysales
                                                GROUP BY alllibrarysales.column1,
                                                  alllibrarysales.column2
                                                ) AS onelibrarysales
                                              ) AS library
                                            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 )
                                            AND ( librarystat.column5 = 'I'
                                            OR librarystat.column4    = 'Gold'
                                            OR librarystat.column5    = 'Bold' )
                                            AND librarystat.column6  <= 'Z74'
                                            ) AS x
                                          ) AS price
                                        WHERE price.column1 < 'R45'
                                        OR ( price.column2  = 'R46'
                                        AND price.column3   = 6 )
                                        GROUP BY price.column1,
                                          price.column2,
                                          price.column3,
                                          price.column4,
                                          price.column5,
                                          price.column6,
                                          price.column7 ;
                                     
                                      END IF;
                                   
                                    END LOOP;
                                 
                                  END;
                               
                                END IF;
                              
                              END testfunction;
                              /************************************************************************
                              /*  Multi line comment */
                              /************************************************************************/
                              /***********************************************************************/
                              --
                              -- ************************************************************************/
                              -- ** Several single line comments -
                              -- **
                              -- ************************************************************************/
                              --
                              
                              END
                              PACKAGE;
                              
                              
                              Before:
                              
                              /* 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(
                                  p_column12 IN VARCHAR2)
                                RETURN VARCHAR2
                              IS
                              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
                                        (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
                                        );
                                      IF emp_rec.empl_rcd# > 0 THEN
                                        INSERT
                                        INTO table1
                                          /*---------
                                          ---------*/
                                          (
                                            col1,
                                            col2,
                                            col3,
                                            col4,
                                            col5,
                                            col6,
                                            col7
                                          )
                                        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 */
                                        FROM
                                          (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
                                          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
                                              (SELECT INTEGER(SUBSTR(onelibrarysales.column1,11,10)) AS column1,
                                                SUBSTR(onelibrarysales.column2,21,10)                AS column2 ,
                                                onelibrarysales.column3,
                                                onelibrarysales.column4,
                                                SUBSTR(onelibrarysales.column5,31,6) AS column5,
                                                SUBSTR(onelibrarysales.column6,37,2) AS column6,
                                                SUBSTR(onelibrarysales.column7,39,6) AS column7
                                              FROM
                                                (SELECT alllibrarysales.column1,
                                                  alllibrarysales.column2,
                                                  MAX(alllibrarysales.column3)                                                                                                                            AS alllibrarysales.column3 ,
                                                  MAX(CHAR(alllibrarysales.column4,iso) concat CHAR(alllibrarysales.column5,iso) concat digits(alllibrarysales.column6) concat (alllibrarysales.column7)) AS column5
                                                FROM
                                                  (SELECT libraryprod.column1,
                                                    libraryprod.column2,
                                                    libraryprod.column3,
                                                    libraryprod.column4,
                                                    libraryprod.column5,
                                                    libraryprod.column6,
                                                    libraryprod.column7
                                                  FROM
                                                    (SELECT tv.column1,
                                                      tv.column2,
                                                      MAX(digits(tv.column3) concat digits(tv.column4) ) AS librarymax
                                                    FROM db1.v_table1 tv
                                                    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)
                                                    GROUP BY tv.column1,
                                                      tv.column2
                                                    ) AS libraryprod,
                                                    db1.table2 th
                                                  WHERE th.column1 =libraryprod.column1
                                                  AND th.column2   =libraryprod.column2
                                                  ) AS alllibrarysales
                                                GROUP BY alllibrarysales.column1,
                                                  alllibrarysales.column2
                                                ) AS onelibrarysales
                                              ) AS library
                                            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 )
                                            AND ( librarystat.column5 = 'I'
                                            OR librarystat.column4    = 'Gold'
                                            OR librarystat.column5    = 'Bold' )
                                            AND librarystat.column6  <= 'Z74'
                                            ) AS x
                                          ) AS price
                                        WHERE price.column1 < 'R45'
                                        OR ( price.column2  = 'R46'
                                        AND price.column3   = 6 )
                                        GROUP BY price.column1,
                                          price.column2,
                                          price.column3,
                                          price.column4,
                                          price.column5,
                                          price.column6,
                                          price.column7 ;
                                      END IF;
                                    END LOOP;
                                  END;
                                END IF;
                              END testfunction;
                              /************************************************************************
                              /*  Multi line comment */
                              /************************************************************************/
                              /***********************************************************************/
                              --
                              -- ************************************************************************/
                              -- ** Several single line comments -
                              -- **
                              -- ************************************************************************/
                              --
                              END
                              PACKAGE;
                              • 12. Re: Searching for deailed info on code formatting rules
                                EdStevens
                                Turloch O'Tierney wrote:
                                Hi EdStevens,

                                There is currently an option to put in newlines *"Line Breaks->More Newlines"* after the format (around blocks/loops)
                                (and after the formatter has stripped the users original newlines). (The option is off by default)
                                Thanks for the comment. I'd have to say that this is another option that is not documented, and nowhere close to being self-evident.
                                I added newlines where it seemed best and achievable in the time available.
                                ??

                                Are you saying you coded this feature in SQL Dev, and were constrained by deadlines on how rich to make it? Or are you talking about your posted example.

                                If the former, I must say I am impressed with the availability of the actual developers on this. I haven't seen that since I was working with MicroFocus Cobol back in the late '80's.


                                Just for the case of whatever level of real customer feedback may be occurring in this thread, After working with SQL Dev for only about a week (but being a full-time oracle DBA since 7.3 was still warm out of the oven) , the two things that have really jumped out at me are what have been mentioned in this thread. To summarize:

                                1) A near total lack of any documentation of the SQL Formatter
                                2) lack of formatting support within a PL/SQL block.

                                I'm not sure which is more surprising.
                                • 13. Re: Searching for deailed info on code formatting rules
                                  Turloch O'Tierney-Oracle
                                  Hi EdStevens,
                                  EdStevens wrote:
                                  Turloch O'Tierney wrote:
                                  Hi EdStevens,

                                  There is currently an option to put in newlines *"Line Breaks->More Newlines"* after the format (around blocks/loops)
                                  (and after the formatter has stripped the users original newlines). (The option is off by default)
                                  Thanks for the comment. I'd have to say that this is another option that is not documented, and nowhere close to being self-evident.
                                  I added newlines where it seemed best and achievable in the time available.
                                  ??

                                  Are you saying you coded this feature in SQL Dev, and were constrained by deadlines on how rich to make it? Or are you talking about your posted example.
                                  I am saying I coded this feature in SQL Dev and I was constrained by deadlines on how rich to make it.

                                  -Turloch
                                  SQLDeveloper team