Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Miguel.

Asked: November 09, 2022 - 3:04 pm UTC

Last updated: November 15, 2022 - 7:06 am UTC

Version: Oracle 11G

Viewed 1000+ times

You Asked

It's a constant discussion.
I work with a lot of back end developer and They maintain that firing DML statements from their applications is the same as using calls to procedures or functions.
I insist that there are reasons of practicality, security, performance, etc. but I need the opinion of someone authorized as you are.
Please I need your opinion.
Thank you very much for sharing so much knowledge.
Regards

MIGUEL DE BELLIZ

and Connor said...

This should help


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.
Connor McDonald
November 15, 2022 - 6:59 am UTC

glad we could help, and thanks for the feedback

VIRAL!

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.
Thanks again!
Connor McDonald
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.
Connor McDonald
November 15, 2022 - 7:06 am UTC

Agreed.

And often that SQL in the application is just built as strings - which opens the door to hackers

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library