SQL & PL/SQL code folding with semicolon

SQL & PL/SQL code folding with semicolon

4

    Jun 16, 2022#1

    Hello everyone,

    I am an Oracle database admin and developer. So, I code a lot in SQL and PL/SQL. I believe what I am about to ask is valid for any SQL developer. I have read many topics in the forums and documentation. Also, talked to support for few times.

    Current UE version I use is 26.10.0.72

    My problem is folding with SQL & PL/SQL. UltraEdit code folding tags Open Fold Strings and Close Fold Strings are not defined in pairs as I learned. Whatever I wrote in Open Fold Strings can be fold with any Close Fold Strings tag. These are my folding strings:

    Code: Select all

    /Open Fold Strings = "{Start}" "begin" "if" "loop" "case" "else" "elsif"
    /Close Fold Strings = "{Stop}" "end;" "end if;" "End Loop;" "end case;" "else" "elsif"
    Here is a sample PL/SQL pseudo code, has no meaning and may have some minor errors, but is enough to explain my issues:

    Code: Select all

    Create or replace function sf_test(p number) as
      x1 number;
      x2 number;
      
      cursor c is
        select *
        from   employees
        where  department_id = 50
          and  salary > 4000
          and  manager_id <> 120;
    begin
      
      x1 := p;
      
      for i in 1..10 loop 
        
        Select salary
        into   x2
        from   employees
        where  hire_date < sysdate -10
          and  department_id in (10,20)
          and  manager_id is not null
          and  first_name = 'Mustafa'
          and  last_name  = 'Kal';
          
          
        if x1 > x2 then
          dbms_output.put_line('error');
        elsif x1 = x2 then 
          dbms_output.put_line('interesting');
        else
          dbms_output.put_line('wow');
        end if;
        
      end loop; 
      
      
    end sf_test;
    Here are my issues:
    1. I need to be able to fold SELECT statements (or any other SQL statements like insert, update, delete, etc. or cursor). select statements can be sometimes over 200 lines or even more and I really need to fold them. In my sample code, line 5 (cursor) and line 17 are start point for select statements. Only way to fold a select statement is adding semicolon into Close Fold Strings, but this will mess everything because in PL/SQL all statements end with semicolon like assignment in line 13 ( x1 := p; ). If I add semicolon to Close Fold Strings, then my first begin at line 11 will be fold with line 13 (assignment) because of any open fold string will be ended by any close fold string. I don't know if this is possible to achieve with current design, but I assume, defining Open and Close Fold Strings in pairs would solve it. First tag in Open Fold Strings should be end with first tag in Close Fold Strings, then my definition cloud be like this:

      Code: Select all

      /Open Fold Strings = "select" "insert" "for loop" "begin" "begin" "exception" ...
      /Close Fold Strings = ";" ";" "end loop;" "end;" "exception" "end;" ...
      begin here could be end with both end and exception, whichever comes first in the code.
      Is this possible?
      I sent an email to support last year about this and they told me there will be a new enhancement coming that will change the behavior of folding in early 2022. Did this enhancement arrived? (I don't know what it is.)
    2. Can we use wild characters in folding like *? As far as I understand it is not, right? In PL/SQL a function definition could be end with END; or END FUNCTION_NAME;. So I am not able to fold whole function if I use function name at the end (in my sample code last line: end sf_test;) It would be nice defining close fold strings with a wild character and that way I can also add:

      Code: Select all

      /Open Fold Strings = "function" 
      /Close Fold Strings = "end *;"
      or maybe a simple regular expression pattern like:

      Code: Select all

      /Open Fold Strings = "function \w" 
      /Close Fold Strings = "end \w;"
      That way exact function name could be match (of course owner name could cause some issues here, function names could have a owner prefix: create function MUSTAFA.SF_TEST, but end of the function must be end sf_test without owner.

      By asking this, I am aware that, one end point will be used by to start point, first line of sample code could be folded with last line of the code and also begin statement at line 11 could be folded with last line of code.
    Second issue can be handled any way, but first one is really important to me. Thank you very much.

    6,603548
    Grand MasterGrand Master
    6,603548

      Jun 16, 2022#2

      There are indeed improvements in UltraEdit for Windows v2022.0 regarding to code folding and reindent selection as there is supported now multiple close fold strings in a line. But those improvements are of no help for SQL code folding as described above.

      It is not possible to define matching open/close fold strings in comparison to open/close brace strings which are always matching pairs. Wildcard patterns or regular expressions are still not supported by UltraEdit for Windows v2022.0 for something other than the function strings.

      My first suggestion is to remove the two lines for the Open Fold Strings and the Close Fold Strings and insert instead in first line of the syntax highlighting wordfile for SQL left to File Extensions the keyword EnableCFByIndent to enable code folding by indent. The modification can be seen after saving the wordfile by using Revert to saved (menu/ribbon File) executed on the already opened SQL file. The code folding looks for me with no knowledge about SQL syntax quite good with that configuration for your example, especially on having unchecked the setting Show last line of fold in syntax highlighted files at Advanced - Settings or Configuration - Editor display - Code folding.

      My second suggestion working also fine for the SQL sample with UltraEdit for Windows v2022.0 is the usage of following lines in the SQL syntax highlighting wordfile:

      Code: Select all

      /Open Fold Strings = "case" "else" "elsif" "function" "if " "loop " "select" "{Start}"
      /Close Fold Strings = "begin" "else" "elsif" "end " "end;" "function" "if " "{Stop}"
      Note: The space at end of some open/close fold strings are all very important.
      Best regards from an UC/UE/UES for Windows user from Austria

      4

        Jun 16, 2022#3

        Thank you very much for your reply Mofi. I am really sad about this. I will use UE anyway but it would be a lot easier to be able to fold SQL statements.

        By the way, your last open and close fold string suggestion wouldn't be applicable. If I have more than one SQL or begin-end block then everything become a mess. I understand what you mean tough. Thank you.

        6,603548
        Grand MasterGrand Master
        6,603548

          Jun 17, 2022#4

          Did you try both suggestions, also the one using EnableCFByIndent, i.e. code folding by indent?

          Please post a more complex sample to take more variants into account. I tested the second suggestion with copying and pasting the entire function in posted sample to have it two times in the file and the code folding was fine. I don't know anything about SQL syntax and therefore need a sample to help which contains all possible variants of SQL to find perhaps the right definition for open/fold strings.

          Last but not least one or more UltraEdit scripts (fold all, fold just the block caret is in) can be used for a customized fold working with rules coded in the script.
          Best regards from an UC/UE/UES for Windows user from Austria

          4

            Jun 17, 2022#5

            Hi Mofi,

            I will test your suggestions today. in the meantime here is a little bit more complex pseudo code:

            Code: Select all

            Create or replace function sf_test(p number) as
              x1 number;
              x2 number;
              
              cursor c is
                select *
                from   employees
                where  department_id = 50
                  and  salary > 4000
                  and  manager_id <> 120;
              
              cursor c2 is
                select * 
                from departments
                where department_name like 'A%';
              
              cursor c3(p_location_id number) is
                select *
                from   locations
                where  location_id > p_location_id;
                
              dummy_variable1 number;
              dummy_variable2 varcha2(80);
              
            begin
              
              x1 := p;
              
              for rec in c2 loop
                
                dummy_variable1 := rec.department_id;
                
                for i in 1..10 loop 
                  begin
                    begin
                      Select salary
                      into   x2
                      from   employees
                      where  hire_date < sysdate -10
                        and  department_id in (10,20)
            
                        and  manager_id is not null
                        and  first_name = 'Mustafa'
                        and  last_name  = 'Kal';
                      
                      Select * 
                      into   r_emp
                      from   employees
                      where  employee_id = p;
                      
                    exception
                      when no_data_found then
                        raise_Application_error(-20000, 'nothing here');
                      when others then
                        raise_Application_error(-20000, 'I didn''t see that coming');
                    End;
                    
                    x1 := x1 + r_emp.salary;
                    
                    if x1 > x2 then
                      dbms_output.put_line('error');
                    elsif x1 = x2 then 
                      dbms_output.put_line('interesting');
                    else
                      dbms_output.put_line('wow');
                    end if;
                    
                    begin
                      Select sum(salary)
                      into   x2
                      from   employees
            
                      where  hire_date < sysdate -10
                        and  department_id =40;
                      
                      dummy_variable2 := dummy_variable1 + rec.salary +
                                         rec.department_id +
                                         rec.location_id;
                                         
                    exception
                      when invalid_number then
                        raise_application_error(-20000, 'not a number');
                    end;
                    
                  exception
                    when others then
                      raise_application_error(-20000, 'unknown error');
                  end;
                end loop; 
              end loop;  
              
            end sf_test;
            I tried both solutions but as I said when code become more complex, things get out of the hand. You can use second sample code to see it. Unfortunately, there might be empty lines in a select statement. Also, I cannot indent after first line of a SQL command, changing 15 years of habit is not easy and also not compatible with general SQL coding rules.

            I would be really satisfied if I could define open and fold strings in pairs. Everything would be much easier for SQL developers.

            By the way, is there a way to define multi character string starter char? In Oracle we can use single quote as string character but also we can use q following with single quote and then any sign character to start a string, like ! or %. Ending string is reverse order without q, for example:

            Code: Select all

            a := q'!bla bla blaaa !' ;
            The reason is if you want to use single quote in your string then you must double it:

            Code: Select all

            a := 'bla'' bla '' bla'; => output bla' bla ' bla
            If there are too much single quotes instead of doubling every each of them, we can use q notation:

            Code: Select all

            a := q'!bla' bla ' bla!'; => output bla' bla ' bla
            Thanks.

            6,603548
            Grand MasterGrand Master
            6,603548

              Jun 19, 2022#6

              Code folding by indent works fine on second sample as long as you do not expect that a  SELECT statement is really folded from the line with the keyword SELECT to the end of the statement. There is only the where part folded. That would be okay for me, but I am not working with SQL files. It is of course not common that the syntax of a file is applied to the syntax highlighting of a text editor although for some languages respectively file types like Windows batch files and Perl scripts that is really necessary because of only cmd.exe can really parse batch files and only perl.exe can parse Perl scripts 100% correct.

              I got the impression on finding something useful for folding second SQL sample file that a SELECT statement ends where the next SQL statement begins whatever is the next SQL statement. The UltraEdit syntax highlighting is not designed for SQL parsing and for that reason it is impossible to define open/close fold strings which detects where a SELECT statement really ends as that requires real SQL parsing. I am quite sure that even a paired definition of open/close fold strings would not really help on determining each SELECT statement for folding it according to your second sample.

              That is also the problem with the q notation. Such a very special string definition is not supported by the syntax highlighting of UltraEdit. It would require real SQL parsing because of the character q can exist everywhere in an SQL file which means the context must be taken into account which the SQL parser processing an SQL file character by character can do, but definitely not UltraEdit.

              I suggest to use in the SQL syntax highlighting wordfile:

              Code: Select all

              /Open Fold Strings = "begin" "case" "else" "elsif" "exception" "for" "if " "loop " "{Start}"
              /Close Fold Strings = "else" "elsif" "end " "end;" "exception" "if " "{Stop}"
              Then the blocks with a definite beginning and end statement are detected by UltraEdit´s syntax highlighting engine as blocks for code folding.

              Furthermore, I suggest to use UltraEdit scripts to fold all SELECT statements in active file or just the next SELECT statement according to position of the caret in the active file. The UltraEdit scripts can be added to the Script list with a hotkey or chord assigned to them for fast execution by key. I tested both scripts with UltraEdit for Windows v2022.0.0.102 and also with v26.10.0.72 on your second sample file.

              Script to fold all SELECT statements:

              Code: Select all

              if (UltraEdit.document.length > 0)  // Is any file opened?
              {
                 // Define environment for this script.
                 UltraEdit.insertMode();
                 if (typeof(UltraEdit.columnModeOff) == "function") UltraEdit.columnModeOff();
                 else if (typeof(UltraEdit.activeDocument.columnModeOff) == "function") UltraEdit.activeDocument.columnModeOff();
              
                 // Move caret to top of the active file.
                 UltraEdit.activeDocument.top();
              
                 // Define the parameters for a Perl regular expression search for finding a SELECT statement.
                 UltraEdit.perlReOn();
                 UltraEdit.activeDocument.findReplace.mode=0;
                 UltraEdit.activeDocument.findReplace.matchCase=false;
                 UltraEdit.activeDocument.findReplace.matchWord=false;
                 UltraEdit.activeDocument.findReplace.regExp=true;
                 UltraEdit.activeDocument.findReplace.searchDown=true;
                 if (typeof(UltraEdit.activeDocument.findReplace.searchInColumn) == "boolean")
                 {
                    UltraEdit.activeDocument.findReplace.searchInColumn=false;
                 }
              
                 // Search in a loop for SELECT statements and fold them.
                 while (UltraEdit.activeDocument.findReplace.find("^[\\t ]*+select\\b.*\\r?\\n(?:[\\t ]*+(?!where).*\\r?\\n)*[\\t ]*+where\\b.*(?:\\r?\\n[\\t ]*(?:and\\b.*)?$)*"))
                 {
                    UltraEdit.activeDocument.hideOrShowLines();
                    UltraEdit.activeDocument.key("DOWN ARROW");
                 }
              
                 // Move caret to top of the active file.
                 UltraEdit.activeDocument.top();
              }
              
              Script to fold only the next SELECT statement according to position of the caret in active file:

              Code: Select all

              if (UltraEdit.document.length > 0)  // Is any file opened?
              {
                 // Define environment for this script.
                 UltraEdit.insertMode();
                 if (typeof(UltraEdit.columnModeOff) == "function") UltraEdit.columnModeOff();
                 else if (typeof(UltraEdit.activeDocument.columnModeOff) == "function") UltraEdit.activeDocument.columnModeOff();
              
                 // Move the caret to beginning of the current line.
                 if (UltraEdit.activeDocument.isColNumGt(1))
                 {
                    UltraEdit.activeDocument.gotoLine(0,1);
                 }
                 // Define the parameters for a Perl regular expression search for finding a SELECT statement.
                 UltraEdit.perlReOn();
                 UltraEdit.activeDocument.findReplace.mode=0;
                 UltraEdit.activeDocument.findReplace.matchCase=false;
                 UltraEdit.activeDocument.findReplace.matchWord=false;
                 UltraEdit.activeDocument.findReplace.regExp=true;
                 UltraEdit.activeDocument.findReplace.searchDown=true;
                 if (typeof(UltraEdit.activeDocument.findReplace.searchInColumn) == "boolean")
                 {
                    UltraEdit.activeDocument.findReplace.searchInColumn=false;
                 }
              
                 // Search for next SELECT statement and fold it.
                 if (UltraEdit.activeDocument.findReplace.find("^[\\t ]*+select\\b.*\\r?\\n(?:[\\t ]*+(?!where).*\\r?\\n)*[\\t ]*+where\\b.*(?:\\r?\\n[\\t ]*(?:and\\b.*)?$)*"))
                 {
                    UltraEdit.activeDocument.hideOrShowLines();
                    UltraEdit.activeDocument.key("DOWN ARROW");
                 }
              }
              
              It would be also possible to use macros instead of scripts for folding all or just next SELECT statement as there is no JavaScript specific code necessary.

              I think, the Perl regular expression search string needs a description. There is searched case-insensitive for a line starting with the keyword select after 0 or more horizontal tabs or normal spaces. Such a line is selected completely as well as all lines below of which first word in the line after zero or more tabs/spaces is not the keyword where and next the line beginning with the keyword where up to end of the line. There is additionally selected all lines below the line with where which are either blank lines (empty or containing only tabs/spaces) or lines with the keyword and as first word after zero or more tabs/spaces.

              If the where clause of a SELECT statement can have after zero or more tabs/spaces also other keywords like or and xor, then replace in both scripts inside the search expression the word  and by (?:and|or|xor) for a non-marking group with an OR expression with all keywords which can be used at beginning of a line which belongs to the where clause of a SELECT statement.
              Best regards from an UC/UE/UES for Windows user from Austria

              4

                Jun 20, 2022#7

                Thank you very much for your support and time. I will check those scripts if they work accordingly in my case.