Community
    • Login

    Function List and PL/SQL packages

    Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
    29 Posts 5 Posters 30.2k 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.
    • Jean-Marc MalmedyJ
      Jean-Marc Malmedy
      last edited by

      Yes, indeed. Thanks for the suggestion.

      1 Reply Last reply Reply Quote 0
      • MAPJe71M
        MAPJe71
        last edited by

        You’re welcome!

        1 Reply Last reply Reply Quote 0
        • Gábor MadácsG
          Gábor Madács
          last edited by Gábor Madács

          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! :)

          MAPJe71M 1 Reply Last reply Reply Quote 0
          • MAPJe71M
            MAPJe71 @Gábor Madács
            last edited by

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

            1 Reply Last reply Reply Quote 0
            • Gábor MadácsG
              Gábor Madács
              last edited by

              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

              MAPJe71M 1 Reply Last reply Reply Quote 0
              • MAPJe71M
                MAPJe71 @Gábor Madács
                last edited by

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

                1 Reply Last reply Reply Quote 0
                • Gábor MadácsG
                  Gábor Madács
                  last edited by

                  @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

                    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

                      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