Function List and PL/SQL packages
-
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
-
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 (seeRegEx - Pattern Modifiers)
(?s:\x2F\x2A.?\x2A\x2F) # Multi Line Comment
| (?m-s:-{2}.$) # Single Line Comment
" >
<classRange
mainExpr =”(?x) # Utilize inline comments (seeRegEx - 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!
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login