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)

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 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