Skip to Main Content
  • Questions

  • Stored Functions - 8i - Apps 11i - Oracle Reports 6.0

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jay.

Asked: September 03, 2002 - 9:07 am UTC

Last updated: March 07, 2007 - 4:22 pm UTC

Version: 8.1.7.3.0

Viewed 1000+ times

You Asked

While I see that you don't use Oracle Reports, you probably can answer this question intuitively anyway.

I am going to be spending a lot of time Modifying Oracle Reports to be used in Oracle Apps 11i.

Instead of adding tables to the base queries in the report or adding functions using the report tool, I have created a package to store commonly used functions. I am calling these functions

Question:
A) I am worried about performance and
B) Is this idea/practice inherently flawed.

Perceived benefits:
1) Central location for common questions.
2) Easier to append reports and see what the original/standard was.
3) Performance because it is stored in the database server?

Thanks for reviewing my question and for this site which I have only recently discovered.

and Tom said...

a) you should be
b) not necessarily


Functions used in the select (eg: select f(x) from t) are usually OK if there are hundreds of rows in the result set. Functions that are used in the predicate (eg: select * from t where y = f(x) ) can be really bad.

It all depends on how many times they are executed. You should just pretend that a stored function will take somewhere between 1/100 and 1/1000 of a second to call. You do that 10 times - no problem. 100 times -- no problem. Do it 100,000 times and *big problem*.

So, just be aware that there will be overhead and there will be times they work great and times when you have to code around them.



Rating

  (5 ratings)

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

Comments

Thank you!

Jay Kiefer, September 03, 2002 - 3:38 pm UTC

Sincerely, Thank You very much (5+ question - 5+ for site)

May I follow up?
I was thinking select f(x) from t where x is a column(s) of t. The functions may be nothing more than stored select statments (no procedural logic).

Can you compare/contrast using a function to my other choices.

May other choices (that I know of) are
a) join the additional tables in From clause
b) use an Oracle Reports 6.0 function (which I think translates to a trigger)



Tom Kyte
September 03, 2002 - 3:55 pm UTC

c) select (select)

eg:

scott@ORA817DEV.US.ORACLE.COM> select dname, (select count(*) from emp where emp.deptno=dept.deptno) cnt
2 from dept;

DNAME CNT
-------------- ----------
ACCOUNTING 3
RESEARCH 5
SALES 6
OPERATIONS 0

scott@ORA817DEV.US.ORACLE.COM>

difference between Oracle9i and 11i

nikhilesh, May 23, 2005 - 4:48 am UTC

Dear Tom,
My friends say that Oracle 11i is higher version of oracle 9i. In fact i always considered 11i is different than 9i and 9i is a database on the other hand 11i is an application.
Could you please clear this confusion???

Thanks a lot.


Tom Kyte
May 23, 2005 - 10:51 am UTC

11i is an "application" like human resources, ERP, CRM

10g is the current shipping production release of the database.
9i is the last version of the database.

last version of oracle

Taha Mohammed Abu Taha, June 08, 2005 - 8:38 am UTC

I wont to learn the Oracle language (11i version), i have
pack about oracle 8i & 9i but don't know anything about
oracle 10g , becouse i fenished the B.Sc degree
in computer scince in Augest,2004.
plz i need to help to learn oracle and do the examination.
thanck u,
best regared
taha abu taha


Tom Kyte
June 08, 2005 - 9:14 am UTC

11i is Oracle Applications, not the database.

10g is the latest database version and all documentation is freely available on otn.oracle.com

11i application administration guide

A reader, March 07, 2007 - 4:08 pm UTC

Is there a oracle documentation for 11i application suite for administration of "oracle E-business suite"
Tom Kyte
March 07, 2007 - 4:22 pm UTC

http://www.oracle.com/applications/home.html

that is a top level page to everything available online. the e-bus suite is rather "large", doubt you'll find a single document.

admin guide for APPS

A reader, March 08, 2007 - 8:59 am UTC

I am really looking for the admin guide APPS. Tried tahiti.oracle.com not much luck
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