Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, IFTIKHAR.

Asked: July 04, 2024 - 10:19 am UTC

Last updated: July 22, 2024 - 12:58 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi,

What are the best practices to develop CRUD/DML APIs (using packages or subprograms) in Oracle. If i want to insert, read, update or delete data, i want to do it through APIs. Furthermore, what can be the possible cons of using APIs instead of using normal queries in web applications?

Thank you.

and Connor said...

A good discussion on CRUD here

https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:672724700346558185

I think a very good balanced position is:

- use transactional APIs instead of table APIs
- APIs just for insert/update/delete is fine. Let people have all the flexibility they want for query (and just grant then READ not SELECT privilege so they can't lock rows)

Rating

  (2 ratings)

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

Comments

A reader, July 19, 2024 - 7:29 am UTC

Thank you! you are the Houdini of oracle.

A reader, July 19, 2024 - 7:36 am UTC

plus can you please elaborate more on the "(and just grant then READ not SELECT privilege so they can't lock rows)"?
Chris Saxon
July 22, 2024 - 12:58 pm UTC

The SELECT privilege allows users to run SELECT ... FOR UPDATE statements which lock the affected rows. Worst case this allows denial of service attacks by running generic SELECT ... FOR UPDATE, locking out genuine changes.

The READ privilege only allows SELECT; the FOR UPDATE clause is disallowed.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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