enthuguy
asked on
Oracle SQL syntax check without executing
Hi Experts,
Is there a way to check Oracle SQL syntax check on bunch of sql files. E.g Parenthesis missing, semi colon missing. etc etc.
Scenario:
Developers check-in their sqls in git, as part of deployment process we would like to validate all the sql and highlight issues before we execute on an environment.
please help
Is there a way to check Oracle SQL syntax check on bunch of sql files. E.g Parenthesis missing, semi colon missing. etc etc.
Scenario:
Developers check-in their sqls in git, as part of deployment process we would like to validate all the sql and highlight issues before we execute on an environment.
please help
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can always run an explain plan. That would check syntax and not run it. Plus you have the bonus of generating a plan. They really should be checking the plan and tuning it before deploying it anyway.
ASKER
@gurpsbassi, Thanks very much!
I agree, thats developer responsibility to test the script on their local and ensure it works fine and then raise pull request. But in few instance, we had couple of syntax issues. But few clarifications pls (sorry its not relevant to this question)
1. To integrate with Liquibase, what is the input for liquibase? just .SQL file or developer should convert sql to YAML? please help me with some resource to learn more about liquibase
2. re: Container, In my scenario, developer check-in incremental scripts every fortnight, If we use Docker, how to apply/execute fortnight build scripts on the db running on container. Can we execute and retain db changes (every build and save) shutdown container, on next build can we just start container and execute next build and so on.
@johnsone, Thanks :)
could you help me how to execute the sql using commandline pls?
they way we have is, we have a master caller sql. which will call other .sql file. so basically we execute only the master script. but it might execute 20 other sqls
I agree, thats developer responsibility to test the script on their local and ensure it works fine and then raise pull request. But in few instance, we had couple of syntax issues. But few clarifications pls (sorry its not relevant to this question)
1. To integrate with Liquibase, what is the input for liquibase? just .SQL file or developer should convert sql to YAML? please help me with some resource to learn more about liquibase
2. re: Container, In my scenario, developer check-in incremental scripts every fortnight, If we use Docker, how to apply/execute fortnight build scripts on the db running on container. Can we execute and retain db changes (every build and save) shutdown container, on next build can we just start container and execute next build and so on.
@johnsone, Thanks :)
could you help me how to execute the sql using commandline pls?
they way we have is, we have a master caller sql. which will call other .sql file. so basically we execute only the master script. but it might execute 20 other sqls
with liquibase you supply your database script in XML / JSON / YAML format. This is so that it is compatible with all RDBMS vendors.
However it still gives you the flexibility to supply a <sql></sql> section where you can put in whatever sql you like. You can also supply a <rollback></rollback> section to write a rollback script. This is useful if you want to rollback to a previous script version. Liquibase tracks each script in a changelog database table and versions it.
Best bet is to look into the liquibase project online http://www.liquibase.org.
I would start off by creating a new git project for the database scripts and have your CI (e.g jenkins) build it.
Your CI could spin up a docker container with version 1.x of the project and try and apply your changes. Liquibase has good integration with jenkins https://wiki.jenkins-ci.org/display/JENKINS/Liquibase+Runner
When liquidate executes it always checks the target database changeling table to detect what script version is the latest. It then only applies the delta.
developers will of course need to get into the mindset of always testing making all database changes through liquidate and not going into the database and hacking around. Once this discipline is there, it gets easier.
However it still gives you the flexibility to supply a <sql></sql> section where you can put in whatever sql you like. You can also supply a <rollback></rollback> section to write a rollback script. This is useful if you want to rollback to a previous script version. Liquibase tracks each script in a changelog database table and versions it.
Best bet is to look into the liquibase project online http://www.liquibase.org.
I would start off by creating a new git project for the database scripts and have your CI (e.g jenkins) build it.
Your CI could spin up a docker container with version 1.x of the project and try and apply your changes. Liquibase has good integration with jenkins https://wiki.jenkins-ci.org/display/JENKINS/Liquibase+Runner
When liquidate executes it always checks the target database changeling table to detect what script version is the latest. It then only applies the delta.
developers will of course need to get into the mindset of always testing making all database changes through liquidate and not going into the database and hacking around. Once this discipline is there, it gets easier.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.