• Login
Community
  • Login

Function List and PL/SQL packages

Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
29 Posts 5 Posters 31.9k Views
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M
    MAPJe71
    last edited by Mar 8, 2017, 9:10 AM

    @Jean-Marc-Malmedy could you try this one:

    			<parser
    				displayName="SQL-mehods"
    				id         ="sql_syntax"
    				commentExpr="(?x)                                               # Utilize inline comments (see `RegEx - Pattern Modifiers`)
    								(?s:\x2F\x2A.*?\x2A\x2F)                        # Multi Line Comment
    							|	(?m-s:-{2}.*$)                                  # Single Line Comment
    							"
    			>
    				<classRange
    					mainExpr    ="(?x)                                          # Utilize inline comments (see `RegEx - Pattern Modifiers`)
    							(?mi)                                               # case insensitive
    							^\h*                                                # optional leading blanks
    							CREATE\s+(?:OR\s+REPLACE\s+)?PACKAGE\s+(?:BODY\s+)? # start-of-package indicator
    							(?:\w+\.)?                                          # schema name, optional
    							(?'PACKAGE_ID'\w+)                                  # package name
    							(?s:.*?)                                            # whatever, until...
    							^\h*END(?:\s+\k'PACKAGE_ID')?\s*;                   # ...end-of-package indicator
    						"
    				>
    					<className>
    						<nameExpr expr="(?i:PACKAGE\s+(?:BODY\s+)?)\K(?:\w+\.)?\w+" />
    					</className>
    					<function
    						mainExpr="^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*\([^()]*\)"
    					>
    						<functionName>
    							<funcNameExpr expr="\w+" />
    						</functionName>
    					</function>
    				</classRange>
    				<function
    					mainExpr="^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*\([^()]*\)"
    				>
    					<functionName>
    						<nameExpr expr="\w+" />
    					</functionName>
    				</function>
    			</parser>
    
    1 Reply Last reply Reply Quote 0
    • J
      Jean-Marc Malmedy
      last edited by Mar 8, 2017, 1:37 PM

      Wonderful, it works.

      I just had to make to small modifications:

      • making the parenthesis optional for the declaration of function or procedure
      • starting the declaration of a function or procedure outside a package with “create or replace”

      This is my final version of the parser:

              <parser
                  displayName="SQL-mehods"
                  id         ="sql_syntax"
                  commentExpr="(?x)                                               # Utilize inline comments (see `RegEx - Pattern Modifiers`)
                                  (?s:\x2F\x2A.*?\x2A\x2F)                        # Multi Line Comment
                              |   (?m-s:-{2}.*$)                                  # Single Line Comment
                              "
              >
                  <classRange
                      mainExpr    ="(?x)                                          # Utilize inline comments (see `RegEx - Pattern Modifiers`)
                              (?mi)                                               # case insensitive
                              ^\h*                                                # optional leading blanks
                              CREATE\s+(?:OR\s+REPLACE\s+)?PACKAGE\s+(?:BODY\s+)? # start-of-package indicator
                              (?:\w+\.)?                                          # schema name, optional
                              (?'PACKAGE_ID'\w+)                                  # package name
                              (?s:.*?)                                            # whatever, until...
                              ^\h*END(?:\s+\k'PACKAGE_ID')?\s*;                   # ...end-of-package indicator
                          "
                  >
                      <className>
                          <nameExpr expr="(?i:PACKAGE\s+(?:BODY\s+)?)\K(?:\w+\.)?\w+" />
                      </className>
                      <function
                          mainExpr="^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(\([^()]*\)){0,1}"
                      >
                          <functionName>
                              <funcNameExpr expr="\w+" />
                          </functionName>
                      </function>
                  </classRange>
                  <function
                      mainExpr="^\h*CREATE\s+(?:OR\s+REPLACE\s+)?(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(\([^()]*\)){0,1}"
                  >
                      <functionName>
                          <nameExpr expr="\w+" />
                      </functionName>
                  </function>
              </parser>
      

      Many many thanks for your help.

      Jean-Marc

      1 Reply Last reply Reply Quote 0
      • M
        MAPJe71
        last edited by Mar 8, 2017, 1:41 PM

        @Jean-Marc-Malmedy FYI: {0,1} can be replaced with a ?.

        1 Reply Last reply Reply Quote 0
        • J
          Jean-Marc Malmedy
          last edited by Mar 8, 2017, 2:02 PM

          Yes, indeed. Thanks for the suggestion.

          1 Reply Last reply Reply Quote 0
          • M
            MAPJe71
            last edited by Mar 8, 2017, 2:02 PM

            You’re welcome!

            1 Reply Last reply Reply Quote 0
            • G
              Gábor Madács
              last edited by Gábor Madács Sep 16, 2017, 10:03 PM Sep 16, 2017, 10:02 PM

              HI! Wonderful, I came here for this… :D

              I have tried his solution, but immediately bumped into a serious problem:
              I can see only the first method in my package. :(

              if i understand correctly, the classRange - mainExpr should match for the whole package body.
              I think the problem lies here at the last line:

              ^\h*END(?:\s+\k'PACKAGE_ID')?\s*;
              

              this matches the first “end;” in the code.

              In a real-world code there is lots of “end;” statements, as we can close methods with it, and even there is unnamed blocks inside them.

              Example:

              CREATE OR REPLACE PACKAGE BODY sch_001.pck_001 AS
                PROCEDURE p_proc_001(pn_id PLS_INTEGER) IS
                  sd date;
                BEGIN
                   -- unnamed block begin: "try-catch" in PL/SQL
                   begin
                      select sysdate into sd from dual;
                   exception when others then
                       sd := null;
                   end;
                   -- do something else
                END;
              END pck_001;
              /
              

              Please help me on this.
              Unfortunately I’m not really familiar with greedy multi-line regexp statements…

              For start, it would be OK, if it could end only on the end of file…

              It would be a huge help for us! :)

              M 1 Reply Last reply Sep 16, 2017, 10:55 PM Reply Quote 0
              • M
                MAPJe71 @Gábor Madács
                last edited by Sep 16, 2017, 10:55 PM

                @Gábor-Madács
                I don’t understand your problem.

                1 Reply Last reply Reply Quote 0
                • G
                  Gábor Madács
                  last edited by Sep 17, 2017, 11:22 AM

                  Thank you for the quick response!

                  Your results are better than mine… :)

                  What parser do you use? This is by the one from the last post from Jean-Marc Malmedy.
                  I have also tried the one before this - posted by you - that is showed the second procedure too, but out of the body.
                  (That’s because of here no “CREATE” in the procedure name - that is a difference between the two version. “CREATE” is for procedures not in packages.)

                  Please post your fine parser version! :)

                  Gabor

                  M 1 Reply Last reply Sep 17, 2017, 2:38 PM Reply Quote 0
                  • M
                    MAPJe71 @Gábor Madács
                    last edited by Sep 17, 2017, 2:38 PM

                    @Gábor-Madács See my post on GitHub.

                    1 Reply Last reply Reply Quote 0
                    • G
                      Gábor Madács
                      last edited by Sep 17, 2017, 10:05 PM

                      @MAPJe71 Wow… Thank you very much! :D
                      (May I note, now the “I don’t understand your problem.” seems somewhat unfounded… ;) )

                      Work like a charm! (Looks like a charm, indeed… ;) )

                      Hope, it will be part of the official NPP distribution!

                      Thank you again!

                      Gabor

                      1 Reply Last reply Reply Quote 0
                      • Marco GonzalezM
                        Marco Gonzalez
                        last edited by Dec 3, 2020, 12:14 AM

                        Beautiful work! But i have a little bit problem.
                        When the procedure or function name is missing after the “end”, it seems the parser consumes the file to the end skipping other functions!

                        Example:
                        function foo is
                        Begin
                        –Todo function logic…
                        null;
                        End;

                        function bar is…

                        In this case function bar (and other functions after it) will be skipped! I know it is not the best practice but it’s pretty common.
                        although it’s simple to fix that on the source code I will appreciate your help to fix the parser.

                        Thanks in advance!

                        1 Reply Last reply Reply Quote 0
                        • Marco GonzalezM
                          Marco Gonzalez
                          last edited by Dec 3, 2020, 11:56 PM

                          Hi guys, after a few little changes it seems to work now for all my Plsql files…
                          <?xml version=“1.0” encoding=“UTF-8” ?>
                          <!-- ==========================================================================\

                          To learn how to make your own language parser, please check the following
                          link:
                          https://npp-user-manual.org/docs/function-list/

                          |
                          =========================================================================== -->
                          <NotepadPlus>
                          <functionList>
                          <!-- ========================================================= [ PL/SQL ] -->
                          <parser
                          displayName=“SQL-mehods”
                          id =“sql_syntax”
                          commentExpr=“(?x) # Utilize inline comments (see RegEx - Pattern Modifiers)
                          (?s:\x2F\x2A.?\x2A\x2F) # Multi Line Comment
                          | (?m-s:-{2}.
                          $) # Single Line Comment
                          " >
                          <classRange
                          mainExpr =”(?x) # Utilize inline comments (see RegEx - Pattern Modifiers)
                          (?mi) # case insensitive
                          ^\h* # optional leading blanks
                          (CREATE\s+(?:OR\s+REPLACE\s+)?)?PACKAGE\s+(?:BODY\s+)? # start-of-package indicator
                          (?:\w+.)? # schema name, optional
                          (?‘PACKAGE_ID’\w+) # package name
                          (?s:.?) # whatever, until…
                          ^\h
                          END(?:\s+\k’PACKAGE_ID’)\s*; # …end-of-package indicator
                          " >
                          <className><nameExpr expr=“(?i:PACKAGE\s+(?:BODY\s+)?)\K(?:\w+.)?\w+” /></className>
                          <function mainExpr=“^\h*(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(([^()]))?“>
                          <functionName><funcNameExpr expr=”\w+" /></functionName>
                          </function>
                          </classRange>
                          <function mainExpr="^\h
                          (CREATE\s+(?:OR\s+REPLACE\s+)?)?(?i:FUNCTION|PROCEDURE)\s+\K\w+\s*(([^()]*))?” >
                          <functionName><nameExpr expr=“\w+” /></functionName>
                          </function>
                          </parser>
                          </functionList>
                          </NotepadPlus>

                          I also indented it my own way, sorry for that, enjoy it!

                          1 Reply Last reply Reply Quote 1
                          • First post
                            Last post
                          The Community of users of the Notepad++ text editor.
                          Powered by NodeBB | Contributors