Function List and PL/SQL packages

Claudia Frank

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

Jean-Marc Malmedy

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

MAPJe71

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

MAPJe71

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

Jean-Marc Malmedy

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

MAPJe71

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

Jean-Marc Malmedy

Yes, indeed. Thanks for the suggestion.

MAPJe71

You’re welcome!

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

MAPJe71

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

Gábor Madács

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

MAPJe71

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

Gábor Madács

@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

Marco Gonzalez

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!

Marco Gonzalez

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>

<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…
^\hEND(?:\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!