Function List and PL/SQL packages



  • Thanx!
    Will give it a try.



  • @MAPJe71

    one other thing.
    The script doesn’t rerun the same regexes. Meaning,
    if one line has

    ^def.*()
    

    and the next line also and you move the cursor from the first to the next line
    it doesn’t trigger another research. There must be a difference within the regexes.

    Cheers
    Claudia



  • One thing I forgot to mention,
    special thanks to Scott and Guy which provided
    a lot of information and doing beta testing.
    Without there patient it wouldn’t be what it is.

    THANK YOU!!
    Claudia



  • @MAPJe71 said:

    Although my regular expressions work in RegexBuddy I’m not able to get them to show any packages in Notepad++ Function List.
    This needs more investigation, sorry I can’t give you a solution any time soon :(

    I’m not surprised since my regular expression worked too outside Function List.
    Anyway, thank you very much for trying.



  • @Jean-Marc-Malmedy after using @Claudia-Frank 's (thanks Claudia, awesome script!) script I was able to create a working parser. Needs some fine tuning/cleaning though.



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


Log in to reply