Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Adriano Ferreira.

Asked: October 26, 2016 - 8:42 am UTC

Last updated: October 26, 2016 - 9:28 am UTC

Version: Oracle SOA Suite

Viewed 1000+ times

You Asked

Hello,

I'm working on a SOA project and we're using stored procedures to work with Oracle RAC in OSB.
This keep the project clean, organized, avoiding XA and others benefits. The only bad thing is a little extra time to work with.

We decided to use this approach because we have a lot of concerns about transactions (to avoid XA), and to achieve better performance, keep code centralized and other benefits.

However I've seen that the market on general, at least in Brazil, doesn't adopt this approach just to keep rapid development and many projects has problems because of wrong implementations o using XA and others.

Thus bring me to one million dollar question: It is a bad practice adopt the use of packages with storedprocedures/functions a bad practice?

Thanks for advance.
Best Regards.

and Chris said...

Using PL/SQL packages is very, very good practice!

Bryn Llewellyn has published a detailed whitepaper explaining the advantages of using PL/SQL:

https://blogs.oracle.com/plsql-and-ebr/resource/Why_Use_Plsql_Whitepaper7.pdf

Toon Koppelaars also has some interesting findings re performance. Putting your SQL inside PL/SQL can reduce your database CPU consumption!

https://drive.google.com/file/d/0B7jyeB8kxFPjU0VySkF3UHhoVnM/edit

So it sounds like you're doing the right thing by using PL/SQL :)

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

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