PL/SQL Development Workflow

Answered

Hi,

I'm currently trying to understand what the best workflow is for working on PL/SQL packages using DataGrip / IntelliJ Ultimate.

Within my project, I have my PL/SQL packages stored in source files in various directories under version control. The package specs are contained in separate files to the package bodies.

When working on a package, I would open the package spec and body files using the project frame.

Observation - I typically get invalid errors and warnings when viewing the package spec and body files because declarations in the spec are not considered when the body file is inspected. This results in "Variable xxxx is never used" warnings in the spec file and "Unable to resolve column xxxx' errors in the body file. If I were to place the package spec and body into the same file, then I would still get lots of "Unable to resolve column xxxx" errors.

Next step would be to execute the scripts containing the package spec and body and then open the version of the package in the database and develop there. No inspection issues are identified with the package here - which is good!

Once the code change is complete and unit tested in the database, it is time to apply the changes back to the source files under version control.

I have to manually keep track of all the packages that have been changed and then either manually use 'SQL Generator' or 'Generate DLL to Clipboard' on each package to obtain the updated source. The updated source then needs to be split (into spec / body) and pasted into each source file and saved.

This seems quite cumbersome and there is always the risk that a package change has been missed by a developer. I therefore wonder if I'm missing some feature within the IDE that might streamline development on the database. I did wonder whether DDL Data Sources might be the solution to synch the source in version control with the database but understand that only applied to objects like tables.

So I'd appreciate any comments on how I could improve my workflow or maybe whether the IDE could be improved to make this easier.

Thanks,

Mark

3 comments
Comment actions Permalink

Halloway,

Thank you for detailed feedback. Let me make some suggestions to help you to improve your flow a bit.

 

0
Comment actions Permalink

> I typically get invalid errors and warnings when viewing the package spec and body files because declarations in the spec are not considered when the body file is inspected.

In your case it's good practice to set SQL Dialects mapping

and to create an SQL Data Source containing a folder with your SQL scripts

When you open any script file you'll get proper completion, e.g.:


0
Comment actions Permalink

Additionally, please up vote the following issues:
- https://youtrack.jetbrains.com/issue/DBE-3852 (Database in version control)
- https://youtrack.jetbrains.com/issue/DBE-8621 (Improve discoverability of new DBs added externaly after refreshing)

0

Please sign in to leave a comment.