PL SQL INSIDE THE SGBD OR PL SQL FROM APPLICATION
MIGUEL DE BELLIZ, November 11, 2022 - 8:40 am UTC
Thanks a lot.
Many developers think they are smarter by reinventing the wheel. PL SQL was born to improve the wheel and it does a great job... But a company sells a tool that makes square wheels and here they go...
In 2007 I asked my first question to Ask Tom, I never got a wrong answer. Thank you for your contribution.
November 15, 2022 - 6:59 am UTC
glad we could help, and thanks for the feedback
MIGUEL DE BELLIZ, November 11, 2022 - 2:54 pm UTC
I spread this video among those of us who advocate that SQL should live on the server and it went viral.
November 15, 2022 - 7:04 am UTC
SQL / PLSQL in application - possible maintenance nightmare
A reader, November 14, 2022 - 8:24 pm UTC
Based on my experience working with Oracle, probably the best reason to have SQL in the database instead of embedded in the application is that it's easy to identify if changes to the database will break, or have broken, your code. If the SQL is in the application, there's no easy way to tell and you can get a maintenance nightmare.
Let's say you build a database. Later you decide to remove a column from a table. In the database you can see the dependencies with other objects and whether your change will break something. If all your SQL is the application, the only way to know if your change is going to break something is to look through all the application code. Also, you can query dba_objects to check for invalid objects after you make changes to make sure you haven't broken something that you weren't aware of but if all your SQL is in the application you won't know until you run the application.
November 15, 2022 - 7:06 am UTC
And often that SQL in the application is just built as strings - which opens the door to hackers