1

We have an application where we add SQL changes in SQL files and they are run during install in test and other environment. So we around 25 people working and if someone makes any mistake then it breaks in test environment as those are DDL, DML and some time even syntax errors.

Now to avoid that I was thinking about building SQl validator that will run a execute plan on the query in the SQL file. The SQL statements are written as single line or multiple lines. We have to format them and then find out the syntax error or any other error then report it. So that while install in test it should break and save lots of time and rework.

Now we do manual review of the SQL files but after that also we have few errors which are not caught and errors out in test.

Any suggestion would be highly appreciated.

Improve this question
0

1 Answer 1

Reset to default
0

You say you want a SQL validator but what you probably need is a better development process.

Let me guess - you have a single development server that everybody works on. They make changes there, hope it doesn't break anything, extract SQL statements from that database, and then apply them to other servers.

That's how most Oracle developers work. It's painful and does not scale. There's a better way using some simple software development methodologies developed decades ago.

  1. Version control as the single source of truth. Most Oracle shops only use version control as a glorified backup. Here's a quick test - drop every schema on the development server. If you can't get back up and running from the source code repository in 5 minutes, you're not really using version control.

    Ask yourself an almost philosophical question - where does the true, ideal version of your product exist? Even if you're building a database product, the answer should be "version-controlled text files", not "the development database".

  2. An infinite number of databases and schemas. This is easier than it sounds - every Oracle developer and tester runs Oracle on their desktop. Then they can create as many instances and schemas as they want. I've seen many Java programmers do this, but sadly most Oracle developers think it's impossible.

  3. Automated tests. Automated unit tests give you confidence. It sounds like right now you have 0 confidence in the code. You shouldn't be worried about syntax errors - that's a Programming 101 problem. Errors will decrease significantly if everyone on the team is constantly building and testing code. Things will still break sometimes - you can add something like continuous integration or just shame people who constantly break the builds.

The combination of those three things are the ultimate SQL validator. Version control to ensure you have the right code. Local instances to make it easy to install and validate the code. And automated tests to do the validation.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.