Why Use Oracle Stored ProceduresI 1

[pmmgpgp]

My organization is new to CR (we have the newest XI) and my knowledge of Oracle SQL PL/SQL (we have 9i) is limited.

I've never used a Stored Procedures or a RefCursors.

Some of my "more knowledgable", "better informed" or "more experienced" coworkers seem to prefer using "Stored Procedures" to straight SQL or "PL/SQL" and I don't seem to be able to communicate by questions to them ( we all are native english speakers, but they are way geekier than I am ).

Aside from "personal preference" are there measurable or noticeable advantages to using "Stored Procedures" ?
"increased or better performance",
"more capabilities",
"simplification" ,
ETC.

Any pointers, guidance or opinion will be greatfully appreciated.


Dave, Your Friend in Beautiful Roanoke, VA

 

[satinsilhouette]

Yes and no, Stored procs let the database do all the processing and crystal just handles the formatting and display for the most part.

If you have complicated calculations, a view on the database of a stored proc would be the efficient way to mill through thousands or millions or teradata of records.

You just have to examine what your heaviest table of records is and determine where best to put the processing.

I am all in favor of sending whatever I can down to the database, let it be the workhorse.

 

[pmmgpgp]

satinsilhouette:
I fully understand about letting the database do the work.

My plan is to exploit the "Command Object" as much as possible; to compose my SQL externally and just paste it into the Database expert.

Thanks for input.
Dave

Dave, Your Friend in Beautiful Roanoke, VA

 

[synapsevampire]

You might consider using Views or SPs in lieu of the Command Object.

The downside being that if the database changes, your reports will break, whereas you might be able to adjust the View or SP and not break reports.

SPs have the advantage of advanced functionality, and they have precompiled executiuon plans, so they will generally outrun a View or pass through SQL (which is what you intend).

The disadvantage is with parameter passing, to pass multiple values to an SP, you must pass it as a comma delimited string and then parse it in the SP, meaning more work and a bad user experience.

Whereas a View doesn't have this limitation, and you can generally get a good deal of reusability from Views.

-k