Function List and PL/SQL packages



  • @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>
    


  • 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



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



  • Yes, indeed. Thanks for the suggestion.



  • You’re welcome!



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



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



  • 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



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



  • @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



  • 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!



  • 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!


Log in to reply