This discussion is archived
13 Replies Latest reply: Nov 21, 2012 8:30 AM by TurlochO'Tierney RSS

Searching for deailed info on code formatting rules

EdStevens Guru
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points