469,125 Members | 1,638 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Theory questions

kiss07
99
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.
Jun 14 '07 #1
30 2814
kiss07
99
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.
Jun 14 '07 #2
kiss07
99
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.
Jun 14 '07 #3
debasisdas
8,127 Expert 4TB
Dear ARUN its nice to see u back in the forum after so long.
Jun 14 '07 #4
r035198x
13,262 8TB
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.
Jun 14 '07 #5
debasisdas
8,127 Expert 4TB
Try writing the local procedures and function in the package body before calling them. That is the diference in oracle.
Jun 14 '07 #6
debasisdas
8,127 Expert 4TB
Try creating Index on the frequently used columns in the query.
Jun 14 '07 #7
debasisdas
8,127 Expert 4TB
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.
Jun 14 '07 #8
debasisdas
8,127 Expert 4TB
Some exceptions
================
1.Unlike regular triggers autonomous triggers can contain COMMIT and ROLLBACK.
2.Also these can execute DDL statments,using native dynamic SQL.
Jun 14 '07 #9
kiss07
99
Dear Debas,

Thanku Very much..

Regards,

Kiss07
Jun 14 '07 #10
kiss07
99
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
Jun 14 '07 #11
debasisdas
8,127 Expert 4TB
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.
Jun 14 '07 #12
kiss07
99
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.
Jun 14 '07 #13
r035198x
13,262 8TB
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.
Jun 14 '07 #14
kiss07
99
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.
Jun 15 '07 #15
debasisdas
8,127 Expert 4TB
Performance wise IN is faster.

But the basic purpose of using IN and EXISTS in sql statments are totally different.
Jun 15 '07 #16
EXISTS is More faster than In Operator
Jun 15 '07 #17
debasisdas
8,127 Expert 4TB
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.
Jun 15 '07 #18
debasisdas
8,127 Expert 4TB
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'
Jun 15 '07 #19
debasisdas
8,127 Expert 4TB
IN clause is generally used to return values which are used as parameters for selecting another set of records. As follows
Expand|Select|Wrap|Line Numbers
  1. SELECT emp_name FROM employees where emp_id IN ( select emp_id FROM another table WHERE some_condition )
  2.  
And EXISTS is used as a decision making statement as follows

Expand|Select|Wrap|Line Numbers
  1. IF EXISTS ( select emp_name from emp where emp_id=1) 
  2. BEGIN
  3. do something; 
  4. END
  5. ELSE 
  6. BEGIN
  7. do another thing; 
  8. END
  9.  
So these statements are basically used for two different purposes.
Jun 15 '07 #20
kiss07
99
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
Jun 18 '07 #21
kiss07
99
Dear friends,

My version is 9i.

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

Expecting Your replies..

Regards,

Kiss07
Jun 18 '07 #22
kiss07
99
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.
Jun 18 '07 #23
kiss07
99
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.
Jun 18 '07 #24
debasisdas
8,127 Expert 4TB
Your question are too theoritical ,

u can find the answeres from any good book/material

to explain all that here is very difficult.
Jun 18 '07 #25
debasisdas
8,127 Expert 4TB
all of your questions aer of grass root level

u need to understand the basics first ,before writing any program or facing any interview.
Jun 18 '07 #26
debasisdas
8,127 Expert 4TB
What do u mean by compiling local procedures.

u want to compile only the local procedures or what. ?
Jun 18 '07 #27
debasisdas
8,127 Expert 4TB
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.
Jun 18 '07 #28
debasisdas
8,127 Expert 4TB
Please follow some good books/manual for the purpose.

Plese do not post theoritical questions here.
Jun 18 '07 #29
MMcCarthy
14,534 Expert Mod 8TB
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
Jun 18 '07 #30
debasisdas
8,127 Expert 4TB
Thanx mary
for saving me from this kiss07
Jun 19 '07 #31

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Xah Lee | last post: by
9 posts views Thread by Ornac | last post: by
15 posts views Thread by designconcepts | last post: by
8 posts views Thread by Amelyan | last post: by
4 posts views Thread by Bob | last post: by
3 posts views Thread by Mayra | last post: by
2 posts views Thread by Omar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.