Theory questions

[kiss07]

Dear Friends,

My oracle Version is 9i.my doubts is trigger related question.Anybody clear my doubts.Is possible to create DDL OR TCL OR DML inside trigger use execute immediate statements?

Expecting Your replies..

Regards,

Kiss07.

[kiss07]

Dear Friends,

How can i compile package body?In my requirements lot of local procedures
in package body so i face some problems in package body occur errors in local procedures.

Expecting Your Replies...

Regards,

Kiss07.

[kiss07]

Dear friends,

My basic requirements is mention below:

I maintained a table emp it contains all employee details and salary details etc..after one month insert lot of records in day by day order nearly lakhs..
that period execution time is very very low .What can i do improve query performance , please send reply..

Regards,

Kiss07.

[debasisdas]

Dear ARUN its nice to see u back in the forum after so long.

[r035198x]

Dear Friends,

How can i compile package body?In my requirements lot of local procedures
in package body so i face some problems in package body occur errors in local procedures.

Expecting Your Replies...

Regards,

Kiss07.

Post the error messages that you're getting for each procedure.

[debasisdas]

Try writing the local procedures and function in the package body before calling them. That is the diference in oracle.

[debasisdas]

Try creating Index on the frequently used columns in the query.

[debasisdas]

Yes u can go for DML commands.

Can't go for TCL commands.

Can't go for DDL commands.

because DDL commands automatically enforces COMMIT which is not allowed in a trigger.

[debasisdas]

Some exceptions
================
1.Unlike regular triggers autonomous triggers can contain COMMIT and ROLLBACK.
2.Also these can execute DDL statments,using native dynamic SQL.

[kiss07]

Dear Debas,

Thanku Very much..

Regards,

Kiss07

[kiss07]

Dear friends,

What is the purpose of using Materialised View?
Why we are Using M.view in oracle 9i.?
Any advantages compare to normal view?

Expecting Your replies..

Regards,

Kiss07

[debasisdas]

Materialised View is mainly used for data replication purpose.

The basic differnece between a normal view and that of a matrialized view is that

Anormal view automatically gets updated ,as soon as the base table data changes. over which the user has no control.

This can be controlled through a materialized view.

[kiss07]

Dear Friends,

What is performanceTuning?

why we are using performance tuning and also what is expain plan ?

same as what is sql tuning?


Regards,

kiss07.

[r035198x]

Dear Friends,

What is performanceTuning?

why we are using performance tuning and also what is expain plan ?

same as what is sql tuning?


Regards,

kiss07.

kiss, this is a theoretical question which you can get the answer to through google or reading a text. This is what we here at TSDN advise people to do for these types of questions. We have told you this before and hope you won't keep ignoring our responses. We don't want to do *that* again for a longer period.

[kiss07]

Dear friends,

My oracle version is 9i.

Anybody send reply which one is Faster IN or EXISTS in Sql Tuning Operation

Expecting Your Replies ..

Regards,

Kiss07.

[debasisdas]

Performance wise IN is faster.

But the basic purpose of using IN and EXISTS in sql statments are totally different.

[amoldiego]

EXISTS is More faster than In Operator

[debasisdas]

There are a couple of different opinions, although most thought that EXISTS was faster. It turns out that like most things in our wonderful software world there is not clear cut answer. In general the below holds true:

#1.If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.

#2.If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.

[debasisdas]

Generally speaking EXISTS is better if the sub-query will have a larger and most costly result set than the outer query. Its the other way around for 'IN'

[debasisdas]

IN clause is generally used to return values which are used as parameters for selecting another set of records. As follows

SELECT emp_name FROM employees where emp_id IN ( select emp_id FROM another table WHERE some_condition )
 

And EXISTS is used as a decision making statement as follows

IF EXISTS ( select emp_name from emp where emp_id=1) 
BEGIN
do something; 
END
ELSE 
BEGIN
do another thing; 
END
 

So these statements are basically used for two different purposes.

[kiss07]

Dear friends,

My Oracle version is 9i.

what are the disadvantages in packages? and also anysize or limit in (number of)procedure and function in used in package specification and body.

and

also in program side which situation use in procedure and which situation use in functions tell me about briefly.please

Expecting Your repies soon...

Regards,

Kiss07

[kiss07]

Dear friends,

My version is 9i.

What is difference between week typed cursor and strong typed cursor?

Expecting Your replies..

Regards,

Kiss07

[kiss07]

Dear friends,

I faced one interview question . one table emp contains 10 rows.


select * from emp;

10 rows selected

after that simply display 10 rows ,what are the backround process are done before displaying 10 rows are selected.


Expecting Your replies..

Regards,

Kiss07.

[kiss07]

Dear Friends,

My version is 9i.

I create lot of local procedures in package body,how can i compile that local procedures, any idea ?

Expecting Your replies..

Regards,

Kiss07.

[debasisdas]

Your question are too theoritical ,

u can find the answeres from any good book/material

to explain all that here is very difficult.

[debasisdas]

all of your questions aer of grass root level

u need to understand the basics first ,before writing any program or facing any interview.

[debasisdas]

What do u mean by compiling local procedures.

u want to compile only the local procedures or what. ?

[debasisdas]

After explaining u all these days if u lack the basic idea and don't know the basic difference between procedure and function ,only GOD may help you.

[debasisdas]

Please follow some good books/manual for the purpose.

Plese do not post theoritical questions here.

[MMcCarthy]

Kiss07

You have ignored repeated warning regarding bombarding the forum with these kinds of theoretical questions which can be answered by any basic book on Oracle. Even a temporary ban on your account has had no effect.

We now have no choice but to permanently ban your account.

Here are a couple of free oracle tutorial sites which I suggest you visit.

http://www.skillbuilders.com/Tutoria...0Tutorials.cfm
http://w2.syronex.com/jmr/edu/db/

There are many more but these should get you started

ADMIN

[debasisdas]

Thanx mary
for saving me from this kiss07