4 Replies Latest reply: May 24, 2012 2:25 PM by Turloch O'Tierney-Oracle RSS

    code formatter breaking my literals asunder

    KevinDavis
      The code formatter should respect my literal new lines rather than breaking my literals asunder,

      ||ltrim(sys_connect_by_path("Customer Name", ':

      :'), ':

      ')

      for one can not use a function such as chr(10) in sys_connect_by_path.
        • 1. Re: code formatter breaking my literals asunder
          Turloch O'Tierney-Oracle
          Hi KevinDavis,

          Give a full example (with valid few lines of code) with before and after formatting.
          That might be enough.
          I may need version number and the formatter options used
          (formatter options can be stored/exported as an xml file)

          -Turloch
          SQLDeveloper Team
          • 2. Re: code formatter breaking my literals asunder
            KevinDavis
            Built in SQL Worksheet:
            create table a as select '1' a, '2' b from dual;
            create procedure p
            as
            begin
            for r in
            (select ltrim(sys_connect_by_path(a, ':
            :'), ':
            ')
            from a
            connect by prior a = b
            )
            loop
            null;
            end loop;
            end;
            /

            As formatted (ctrl-F7) in SQL Dev proc editor:
            CREATE OR REPLACE
            PROCEDURE p
            AS
            BEGIN
            FOR r IN
            (SELECT ltrim(sys_connect_by_path(a, ':

            :'), ':

            ')
            FROM a
            CONNECT BY prior a = b
            )
            LOOP
            NULL;
            END LOOP;
            END;

            Furthermore, the formatter operation is not idempotent as exhibited by the result of a second ctrl-F7:
            CREATE OR REPLACE
            PROCEDURE p
            AS
            BEGIN
            FOR r IN
            (SELECT ltrim(sys_connect_by_path(a, ':



            :'), ':



            ')
            FROM a
            CONNECT BY prior a = b
            )
            LOOP
            NULL;
            END LOOP;
            END;

            About
            -----

            Oracle SQL Developer 3.1.07
            Version 3.1.07
            Build MAIN-07.42
            Copyright © 2005, 2011 Oracle. All Rights Reserved.
            IDE Version: 11.1.1.4.37.59.48
            Product ID: oracle.sqldeveloper
            Product Version: 11.2.0.07.42

            Version
            -------

            Component     Version
            =========     =======
            Java(TM) Platform     1.6.0_11
            Oracle IDE     3.1.07.42
            Versioning Support     3.1.07.42

            Code Style Profiles xml:
            <?xml version = '1.0' encoding = 'UTF-8'?>
            <SQLprofiles class="oracle.dbtools.proformatter.treePreferences.style.profile.CodingStyleSQLProfiles" xmlns="http://xmlns.oracle.com/jdeveloper/110000/default-coding-sql-style-profiles">
            <profileMap class="java.util.HashMap">
            <Item>
            <Key>1:Old Preferences</Key>
            <Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
            <alignAs>true</alignAs>
            <alignComma>false</alignComma>
            <alignComment>true</alignComment>
            <alignConcat>true</alignConcat>
            <alignDecl>true</alignDecl>
            <alignEqual>true</alignEqual>
            <alignKeyword>false</alignKeyword>
            <alignOperator>true</alignOperator>
            <bracketSpacing>0</bracketSpacing>
            <bracketSpacingAndOrWhen>false</bracketSpacingAndOrWhen>
            <breakAfterAnd>false</breakAfterAnd>
            <breakAfterComma>true</breakAfterComma>
            <breakAfterConcat>false</breakAfterConcat>
            <breakBeforeAnd>true</breakBeforeAnd>
            <breakBeforeComma>false</breakBeforeComma>
            <breakBeforeComment>false</breakBeforeComment>
            <breakBeforeConcat>true</breakBeforeConcat>
            <breakCase>true</breakCase>
            <breakCaseAndOr>true</breakCaseAndOr>
            <breakCaseElse>true</breakCaseElse>
            <breakCaseThen>true</breakCaseThen>
            <breakCaseWhen>true</breakCaseWhen>
            <breakJoin>true</breakJoin>
            <breakKeyword>false</breakKeyword>
            <breakSchema>0</breakSchema>
            <breakSelectBracket>false</breakSelectBracket>
            <colored>false</colored>
            <commaSpacing>0</commaSpacing>
            <dblIndent>false</dblIndent>
            <forceDifference>true</forceDifference>
            <indentAnd>false</indentAnd>
            <lineNum>999</lineNum>
            <lineWidth>999</lineWidth>
            <memberOrderHashStructure/>
            <moreNewlines>false</moreNewlines>
            <name>Old Preferences</name>
            <numCommas>1</numCommas>
            <numSpaces>2</numSpaces>
            <operatorSpacing>0</operatorSpacing>
            <quoteChar>0</quoteChar>
            <replaceComment>false</replaceComment>
            <smallSql>80</smallSql>
            <sourceSql>1</sourceSql>
            <sqlSourceEnclosed>0</sqlSourceEnclosed>
            <sqlsourceCopied>false</sqlsourceCopied>
            <suppressComment>false</suppressComment>
            <targetSql>0</targetSql>
            <uppercase>0</uppercase>
            <useTab>false</useTab>
            </Value>
            </Item>
            <Item>
            <Key>1:SQL</Key>
            <Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
            <alignAs>true</alignAs>
            <alignComma>false</alignComma>
            <alignComment>true</alignComment>
            <alignConcat>true</alignConcat>
            <alignDecl>true</alignDecl>
            <alignEqual>true</alignEqual>
            <alignKeyword>false</alignKeyword>
            <alignOperator>true</alignOperator>
            <bracketSpacing>0</bracketSpacing>
            <bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
            <breakAfterAnd>false</breakAfterAnd>
            <breakAfterComma>true</breakAfterComma>
            <breakAfterConcat>false</breakAfterConcat>
            <breakBeforeAnd>true</breakBeforeAnd>
            <breakBeforeComma>false</breakBeforeComma>
            <breakBeforeComment>false</breakBeforeComment>
            <breakBeforeConcat>true</breakBeforeConcat>
            <breakCase>true</breakCase>
            <breakCaseAndOr>true</breakCaseAndOr>
            <breakCaseElse>true</breakCaseElse>
            <breakCaseThen>true</breakCaseThen>
            <breakCaseWhen>true</breakCaseWhen>
            <breakJoin>true</breakJoin>
            <breakKeyword>true</breakKeyword>
            <breakSchema>0</breakSchema>
            <breakSelectBracket>true</breakSelectBracket>
            <colored>false</colored>
            <commaSpacing>0</commaSpacing>
            <dblIndent>false</dblIndent>
            <forceDifference>true</forceDifference>
            <indentAnd>false</indentAnd>
            <lineNum>10</lineNum>
            <lineWidth>80</lineWidth>
            <memberOrderHashStructure/>
            <moreNewlines>false</moreNewlines>
            <name>SQL</name>
            <numCommas>1</numCommas>
            <numSpaces>2</numSpaces>
            <operatorSpacing>0</operatorSpacing>
            <quoteChar>0</quoteChar>
            <replaceComment>false</replaceComment>
            <smallSql>20</smallSql>
            <sourceSql>1</sourceSql>
            <sqlSourceEnclosed>0</sqlSourceEnclosed>
            <sqlsourceCopied>false</sqlsourceCopied>
            <suppressComment>false</suppressComment>
            <targetSql>0</targetSql>
            <uppercase>0</uppercase>
            <useTab>false</useTab>
            </Value>
            </Item>
            <Item>
            <Key>2:SQL</Key>
            <Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
            <alignAs>true</alignAs>
            <alignComma>false</alignComma>
            <alignComment>true</alignComment>
            <alignConcat>true</alignConcat>
            <alignDecl>true</alignDecl>
            <alignEqual>true</alignEqual>
            <alignKeyword>false</alignKeyword>
            <alignOperator>true</alignOperator>
            <bracketSpacing>0</bracketSpacing>
            <bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
            <breakAfterAnd>false</breakAfterAnd>
            <breakAfterComma>true</breakAfterComma>
            <breakAfterConcat>false</breakAfterConcat>
            <breakBeforeAnd>true</breakBeforeAnd>
            <breakBeforeComma>false</breakBeforeComma>
            <breakBeforeComment>false</breakBeforeComment>
            <breakBeforeConcat>true</breakBeforeConcat>
            <breakCase>true</breakCase>
            <breakCaseAndOr>true</breakCaseAndOr>
            <breakCaseElse>true</breakCaseElse>
            <breakCaseThen>true</breakCaseThen>
            <breakCaseWhen>true</breakCaseWhen>
            <breakJoin>true</breakJoin>
            <breakKeyword>true</breakKeyword>
            <breakSchema>0</breakSchema>
            <breakSelectBracket>true</breakSelectBracket>
            <colored>false</colored>
            <commaSpacing>0</commaSpacing>
            <dblIndent>false</dblIndent>
            <forceDifference>true</forceDifference>
            <indentAnd>false</indentAnd>
            <lineNum>10</lineNum>
            <lineWidth>80</lineWidth>
            <memberOrderHashStructure/>
            <moreNewlines>false</moreNewlines>
            <name>SQL</name>
            <numCommas>1</numCommas>
            <numSpaces>2</numSpaces>
            <operatorSpacing>0</operatorSpacing>
            <quoteChar>0</quoteChar>
            <replaceComment>false</replaceComment>
            <smallSql>20</smallSql>
            <sourceSql>2</sourceSql>
            <sqlSourceEnclosed>0</sqlSourceEnclosed>
            <sqlsourceCopied>false</sqlsourceCopied>
            <suppressComment>false</suppressComment>
            <targetSql>0</targetSql>
            <uppercase>0</uppercase>
            <useTab>false</useTab>
            </Value>
            </Item>
            <Item>
            <Key>3:SQL</Key>
            <Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
            <alignAs>true</alignAs>
            <alignComma>false</alignComma>
            <alignComment>true</alignComment>
            <alignConcat>true</alignConcat>
            <alignDecl>true</alignDecl>
            <alignEqual>true</alignEqual>
            <alignKeyword>false</alignKeyword>
            <alignOperator>true</alignOperator>
            <bracketSpacing>0</bracketSpacing>
            <bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
            <breakAfterAnd>false</breakAfterAnd>
            <breakAfterComma>true</breakAfterComma>
            <breakAfterConcat>false</breakAfterConcat>
            <breakBeforeAnd>true</breakBeforeAnd>
            <breakBeforeComma>false</breakBeforeComma>
            <breakBeforeComment>false</breakBeforeComment>
            <breakBeforeConcat>true</breakBeforeConcat>
            <breakCase>true</breakCase>
            <breakCaseAndOr>true</breakCaseAndOr>
            <breakCaseElse>true</breakCaseElse>
            <breakCaseThen>true</breakCaseThen>
            <breakCaseWhen>true</breakCaseWhen>
            <breakJoin>true</breakJoin>
            <breakKeyword>true</breakKeyword>
            <breakSchema>0</breakSchema>
            <breakSelectBracket>true</breakSelectBracket>
            <colored>false</colored>
            <commaSpacing>0</commaSpacing>
            <dblIndent>false</dblIndent>
            <forceDifference>true</forceDifference>
            <indentAnd>false</indentAnd>
            <lineNum>10</lineNum>
            <lineWidth>80</lineWidth>
            <memberOrderHashStructure/>
            <moreNewlines>false</moreNewlines>
            <name>SQL</name>
            <numCommas>1</numCommas>
            <numSpaces>2</numSpaces>
            <operatorSpacing>0</operatorSpacing>
            <quoteChar>0</quoteChar>
            <replaceComment>false</replaceComment>
            <smallSql>20</smallSql>
            <sourceSql>3</sourceSql>
            <sqlSourceEnclosed>0</sqlSourceEnclosed>
            <sqlsourceCopied>false</sqlsourceCopied>
            <suppressComment>false</suppressComment>
            <targetSql>0</targetSql>
            <uppercase>0</uppercase>
            <useTab>false</useTab>
            </Value>
            </Item>
            <Item>
            <Key>4:SQL</Key>
            <Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
            <alignAs>true</alignAs>
            <alignComma>false</alignComma>
            <alignComment>true</alignComment>
            <alignConcat>true</alignConcat>
            <alignDecl>true</alignDecl>
            <alignEqual>true</alignEqual>
            <alignKeyword>false</alignKeyword>
            <alignOperator>true</alignOperator>
            <bracketSpacing>0</bracketSpacing>
            <bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
            <breakAfterAnd>false</breakAfterAnd>
            <breakAfterComma>true</breakAfterComma>
            <breakAfterConcat>false</breakAfterConcat>
            <breakBeforeAnd>true</breakBeforeAnd>
            <breakBeforeComma>false</breakBeforeComma>
            <breakBeforeComment>false</breakBeforeComment>
            <breakBeforeConcat>true</breakBeforeConcat>
            <breakCase>true</breakCase>
            <breakCaseAndOr>true</breakCaseAndOr>
            <breakCaseElse>true</breakCaseElse>
            <breakCaseThen>true</breakCaseThen>
            <breakCaseWhen>true</breakCaseWhen>
            <breakJoin>true</breakJoin>
            <breakKeyword>true</breakKeyword>
            <breakSchema>0</breakSchema>
            <breakSelectBracket>true</breakSelectBracket>
            <colored>false</colored>
            <commaSpacing>0</commaSpacing>
            <dblIndent>false</dblIndent>
            <forceDifference>true</forceDifference>
            <indentAnd>false</indentAnd>
            <lineNum>10</lineNum>
            <lineWidth>80</lineWidth>
            <memberOrderHashStructure/>
            <moreNewlines>false</moreNewlines>
            <name>SQL</name>
            <numCommas>1</numCommas>
            <numSpaces>2</numSpaces>
            <operatorSpacing>0</operatorSpacing>
            <quoteChar>0</quoteChar>
            <replaceComment>false</replaceComment>
            <smallSql>20</smallSql>
            <sourceSql>4</sourceSql>
            <sqlSourceEnclosed>0</sqlSourceEnclosed>
            <sqlsourceCopied>false</sqlsourceCopied>
            <suppressComment>false</suppressComment>
            <targetSql>0</targetSql>
            <uppercase>0</uppercase>
            <useTab>false</useTab>
            </Value>
            </Item>
            <Item>
            <Key>5:SQL</Key>
            <Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
            <alignAs>true</alignAs>
            <alignComma>false</alignComma>
            <alignComment>true</alignComment>
            <alignConcat>true</alignConcat>
            <alignDecl>true</alignDecl>
            <alignEqual>true</alignEqual>
            <alignKeyword>false</alignKeyword>
            <alignOperator>true</alignOperator>
            <bracketSpacing>0</bracketSpacing>
            <bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
            <breakAfterAnd>false</breakAfterAnd>
            <breakAfterComma>true</breakAfterComma>
            <breakAfterConcat>false</breakAfterConcat>
            <breakBeforeAnd>true</breakBeforeAnd>
            <breakBeforeComma>false</breakBeforeComma>
            <breakBeforeComment>false</breakBeforeComment>
            <breakBeforeConcat>true</breakBeforeConcat>
            <breakCase>true</breakCase>
            <breakCaseAndOr>true</breakCaseAndOr>
            <breakCaseElse>true</breakCaseElse>
            <breakCaseThen>true</breakCaseThen>
            <breakCaseWhen>true</breakCaseWhen>
            <breakJoin>true</breakJoin>
            <breakKeyword>true</breakKeyword>
            <breakSchema>0</breakSchema>
            <breakSelectBracket>true</breakSelectBracket>
            <colored>false</colored>
            <commaSpacing>0</commaSpacing>
            <dblIndent>false</dblIndent>
            <forceDifference>true</forceDifference>
            <indentAnd>false</indentAnd>
            <lineNum>10</lineNum>
            <lineWidth>80</lineWidth>
            <memberOrderHashStructure/>
            <moreNewlines>false</moreNewlines>
            <name>SQL</name>
            <numCommas>1</numCommas>
            <numSpaces>2</numSpaces>
            <operatorSpacing>0</operatorSpacing>
            <quoteChar>0</quoteChar>
            <replaceComment>false</replaceComment>
            <smallSql>20</smallSql>
            <sourceSql>5</sourceSql>
            <sqlSourceEnclosed>0</sqlSourceEnclosed>
            <sqlsourceCopied>false</sqlsourceCopied>
            <suppressComment>false</suppressComment>
            <targetSql>0</targetSql>
            <uppercase>0</uppercase>
            <useTab>false</useTab>
            </Value>
            </Item>
            <Item>
            <Key>6:SQL</Key>
            <Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
            <alignAs>true</alignAs>
            <alignComma>false</alignComma>
            <alignComment>true</alignComment>
            <alignConcat>true</alignConcat>
            <alignDecl>true</alignDecl>
            <alignEqual>true</alignEqual>
            <alignKeyword>false</alignKeyword>
            <alignOperator>true</alignOperator>
            <bracketSpacing>0</bracketSpacing>
            <bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
            <breakAfterAnd>false</breakAfterAnd>
            <breakAfterComma>true</breakAfterComma>
            <breakAfterConcat>false</breakAfterConcat>
            <breakBeforeAnd>true</breakBeforeAnd>
            <breakBeforeComma>false</breakBeforeComma>
            <breakBeforeComment>false</breakBeforeComment>
            <breakBeforeConcat>true</breakBeforeConcat>
            <breakCase>true</breakCase>
            <breakCaseAndOr>true</breakCaseAndOr>
            <breakCaseElse>true</breakCaseElse>
            <breakCaseThen>true</breakCaseThen>
            <breakCaseWhen>true</breakCaseWhen>
            <breakJoin>true</breakJoin>
            <breakKeyword>true</breakKeyword>
            <breakSchema>0</breakSchema>
            <breakSelectBracket>true</breakSelectBracket>
            <colored>false</colored>
            <commaSpacing>0</commaSpacing>
            <dblIndent>false</dblIndent>
            <forceDifference>true</forceDifference>
            <indentAnd>false</indentAnd>
            <lineNum>10</lineNum>
            <lineWidth>80</lineWidth>
            <memberOrderHashStructure/>
            <moreNewlines>false</moreNewlines>
            <name>SQL</name>
            <numCommas>1</numCommas>
            <numSpaces>2</numSpaces>
            <operatorSpacing>0</operatorSpacing>
            <quoteChar>0</quoteChar>
            <replaceComment>false</replaceComment>
            <smallSql>20</smallSql>
            <sourceSql>6</sourceSql>
            <sqlSourceEnclosed>0</sqlSourceEnclosed>
            <sqlsourceCopied>false</sqlsourceCopied>
            <suppressComment>false</suppressComment>
            <targetSql>0</targetSql>
            <uppercase>0</uppercase>
            <useTab>false</useTab>
            </Value>
            </Item>
            </profileMap>
            </SQLprofiles>
            • 3. Re: code formatter breaking my literals asunder
              KevinDavis
              The problem occurs with both "Old Preferences" and "SQL" code styles.
              • 4. Re: code formatter breaking my literals asunder
                Turloch O'Tierney-Oracle
                Hi KevinDavis,

                Reproduced and logged
                Bug 14114689 - CODE FORMATTER BREAKING MY LITERALS ASUNDER (USING MULTI LINE LITERALS)
                for one can not use a function such as chr(10) in sys_connect_by_path.
                Looked up that issue - would work if the sql was dynamically generated - but that might affect your code too much.

                Some formatters have comments for do not format start and end markers, if you want to make a request for that in the
                SQLDeveloper Exchange http://sqldeveloper.oracle.com/ .

                -Turloch
                SQLDeveloper Team