Skip to content

Add support for Oracle PL/SQL (Procedures, Functions, Packages, Triggers, Types, Cursors, Loops) #6635

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 27 commits into from
Mar 29, 2025
Merged

Add support for Oracle PL/SQL (Procedures, Functions, Packages, Triggers, Types, Cursors, Loops) #6635

merged 27 commits into from
Mar 29, 2025

Conversation

joaostorrer
Copy link
Contributor

This is quite a big one.
It adds support for Oracle PL/SQL objects like Procedures, Functions, Types, Packages, Anonymous Blocks, If statements, Case statements, Loops, Cursors and Collections.

Some objects were copied from T-SQL dialect.

The tests statements were copied from the official Oracle PL/SQL Documentation

Closes #1217.
Closes #6568.

Brief summary of the change made

Are there any other side effects of this change that we should be aware of?

Pull Request checklist

  • Please confirm you have completed any of the necessary steps below.

  • Included test cases to demonstrate any code changes, which may be one or more of the following:

    • .yml rule test cases in test/fixtures/rules/std_rule_cases.
    • .sql/.yml parser test cases in test/fixtures/dialects (note YML files can be auto generated with tox -e generate-fixture-yml).
    • Full autofix test cases in test/fixtures/linter/autofix.
    • Other.

  • Added appropriate documentation for the change.

  • Created GitHub issues for any relevant followup/future enhancements if appropriate.

@joaostorrer
Add initial support for Oracle PL/SQL. Including anonymous blocks, if…
d7eb476 
…-else statements and create procedure statements.
@joaostorrer
Add support to SELECT INTO clause, column type reference and row ty…
9448050 
…pe reference and fix edges for merge and insert statements in Oracle.
@joaostorrer
Add support for CREATE TRIGGER, CASE EXPRESSION and anonymous blo…
e7e13ac 
…cks with declare statements in Oracle.
@joaostorrer
Add support for collections, FOR LOOP, FOR ALL and `COMPOUND TRIG…
56950a2 
…GER` statements in Oracle.
@joaostorrer
Fix edges in FOR and FORALL statements in Oracle.
8daf8e3
@joaostorrer
Fix edges for compound triggers in Oracle.
2c8646c
@joaostorrer
Add support for CREATE FUNCTION in Oracle.
fc80708
@joaostorrer
Add support for CREATE FUNCTION, ALTER FUNCTION and `ALTER TRIGGE…
e754f6a 
…R` statements in Oracle.
@joaostorrer
Refactoring.
a768bfd
@joaostorrer
Add support for CREATE TYPE and CREATE TYPE BODY statements in Or…
a51c8d4 
…acle.
@joaostorrer
Add support for cursors, records and CREATE PACKAGE statements in O…
c65195a 
…racle.
@joaostorrer
Refactoring.
83c46fa
@joaostorrer
Merge.
693482f
@joaostorrer
Refactoring, Add tests for the remaining classes in Oracle and add su…
2a3e048 
…pport for `WHILE LOOP`, `OPEN` and `CONTINUE` statements in Oracle.
@joaostorrer
Add support for ALTER PACKAGE, DROP PACKAGE, DROP PROCEDURE and…
6e89ddc 
… `DROP TYPE` statements in Oracle.
@joaostorrer
Refactoring.
f447874
@joaostorrer
Merge branch 'main' into oracle_plsql
49c0cf5
@github-actions GitHub Actions
Copy link
Contributor

github-actions bot commented Feb 6, 2025 •
edited
Loading

Coverage Results ✅

Name    Stmts   Miss  Cover   Missing
-------------------------------------
TOTAL   19499      0   100%

248 files skipped due to complete coverage.

WittierDinosaur
WittierDinosaur reviewed Feb 7, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 7, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 7, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 9, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 9, 2025
View reviewed changes
@joaostorrer
Alter class DeclareStatementSegment to DeclareSegment and alter t…
5ffda87 
…ype of class `CreateTriggerStatementSegment` to `create_trigger_statement`.
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
src/sqlfluff/dialects/dialect_oracle.py
OneOf("IS", "AS", optional=True),
AnyNumberOf(Ref("DeclareSegment"), optional=True),
Ref("BeginEndSegment", optional=True),
Ref("DelimiterGrammar", optional=True),
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

here

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This need to be kept, because procedures can be declared inside DeclareSegment and CreatePackageStatementSegment. If I remove it the parser raises an error.

WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
src/sqlfluff/dialects/dialect_oracle.py Outdated
Dedent,
"END",
Ref("ObjectReferenceSegment", optional=True),
Ref("DelimiterGrammar", optional=True),
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

not sure about this one - is it a statement?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This is not a statement, it's an anonymous block. Oracle don't define it as a statement.
Removed the DelimiterGrammar.

WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
src/sqlfluff/dialects/dialect_oracle.py
OneOf("IS", "AS", optional=True),
AnyNumberOf(Ref("DeclareSegment"), optional=True),
Ref("BeginEndSegment", optional=True),
Ref("DelimiterGrammar", optional=True),
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

here

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This need to be kept, because functions can be declared inside DeclareSegment and CreatePackageStatementSegment. If I remove it the parser raises an error.

WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
WittierDinosaur
WittierDinosaur reviewed Feb 11, 2025
View reviewed changes
src/sqlfluff/dialects/dialect_oracle.py
"BEGIN",
Ref("OneOrMoreStatementsGrammar"),
Sequence("END", Ref("TimingPointGrammar")),
Ref("DelimiterGrammar"),
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

does this need a delimiter?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Actually it does, because it's part of the trigger block. If I remove this delimiter the parser thinks it's the end of the trigger and raises an error.

@WittierDinosaur
Copy link
Contributor

Nearly there, just one final merge conflict

@joaostorrer
Copy link
Contributor Author

Done.
I had to keep the keywords DELETING, INSERTING, LOOP, UPDATING and WITH as reserved, even if they don't appear as reserved in the oracle docs so Sqlfluff don't think it is a SingleIdentiferGrammar in ExitStatementSegment and LoopStatementSegment.

WittierDinosaur
WittierDinosaur approved these changes Mar 29, 2025
View reviewed changes
Copy link
Contributor

@WittierDinosaur WittierDinosaur left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@WittierDinosaur WittierDinosaur added this pull request to the merge queue Mar 29, 2025
Merged via the queue into sqlfluff:main with commit 9e29b2f Mar 29, 2025
28 checks passed
@joaostorrer joaostorrer deleted the oracle_plsql branch March 31, 2025 14:30
@netsgnut netsgnut mentioned this pull request May 29, 2025
Closed
3 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@WittierDinosaur WittierDinosaur WittierDinosaur approved these changes

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

Oracle: unparsable section CREATE OR REPLACE TRIGGER Support of Oracle PLSQL?
2 participants
@joaostorrer @WittierDinosaur