GeekInterview.com
  I am new, Sign me up!
 
GeekInterview.com  >  Interview Questions  >  Oracle  >  PL/SQL
Go To First  |  Previous Question  |  Next Question 
 PL/SQL  |  Question 237 of 241    Print  
Pipe Function and Mutating Trigger
1) Why we use pl/sql array rather than cursor?
2) In which condition we use function overloading?
3) What is pipe function?
4) What is the mutating trigger? What should be the condition?



  
Total Answers and Comments: 4 Last Update: October 14, 2009     Asked by: harit79 
  
 Sponsored Links

 
 Best Rated Answer
Submitted by: ananth.oracle
 
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger. The best way to avoid this error is use autonomous transaction in triggers so that the child transaction will be independent of the parent transaction.

The Oracle DBMS_PIPE package provies a mechanism that can be used for indirect communication between two or more co-operating Oracle sessions.

Above answer was rated as good by the following members:
SUNDAY8
November 29, 2008 13:49:09   #1  
vnraos Member Since: April 2008   Contribution: 3    

RE: Pipe Function and Mutating Trigger
Harit
1.Cursor is basically a select statement with a where condition which returns more than one row mostly. The fetch from cursor being a part of a procedure or function are later updated(like increasing salary etc) or deleted (stock older than two years moved to another location etc.)

2.Where as arrays are used to store more than one phone number or email ids or addresses of a person. And arrays are DataType. Can be Number Character Objecttype etc.

Thus cursor can select as many columns from a table and acts as a memory area which is explicitly managed by programmer to store and clear once the defined purpoe is over. Array(Varray) is a datatype of any pl/sql variable.

3.Overloaded function or procedure are broadly same program units with same name different parameters. To give an easy understandability you can follow this convention. For example you can create two procedures with name Admit_student(Group Major) and Admit_student(Group Major Class) for admitting students . In the first case you wish to supply only
Group (Science Biology IT) and in the second case you wish to supply Class(Honours Ordinary) also.

4. A trigger is defined for an DML (Insert Update or Delete)action and is refering to the table which it is changing. For example The trigger is defined on a Table Employees "After Insert" and the body of the trigger prefers to "Insert in to Employee (dept) values("New Department") where employee_name "Mohan"..... "One of the suggested solution is to use a view. I have given a very crude example. You may refer to Scott Urman book or anyother.

 
Is this answer useful? Yes | No
January 08, 2009 12:16:12   #2  
javedans Member Since: January 2009   Contribution: 8    

RE: Pipe Function and Mutating Trigger
Database triggers are like system defined stores procedures which will fire automatically when an insert update and delete take place on the specified table. There are two type of trigger available.

1. State level trigger: This trigger is use when trigger body will not depend on trigger action. Means statement trigger will always either it will update any record or not.


2. Row level trigger: This trigger is use when trigger body will depend on trigger action. Means when trigger will insert/update/delete any record then only it will fire.

Trigger Timing: There are two type trigger timing available


1. Before Trigger
2. After Trigger



 
Is this answer useful? Yes | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
March 09, 2009 15:50:00   #3  
ananth.oracle Member Since: March 2009   Contribution: 3    

RE: Pipe Function and Mutating Trigger
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger. The best way to avoid this error is use autonomous transaction in triggers so that the child transaction will be independent of the parent transaction.

The Oracle DBMS_PIPE package provies a mechanism that can be used for indirect communication between two or more co-operating Oracle sessions.

 
Is this answer useful? Yes | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
October 14, 2009 06:48:17   #4  
avis_sarkar Member Since: January 2009   Contribution: 2    

RE: Pipe Function and Mutating Trigger

Triggers are PLSQL block or Procedures which get executed whenever:
1) an INSERT DELETE or UPDATE happens on a table.
2) DDL like ALTER or CREATE happens on a table.
3) an INSTEAD OF trigger on a view is issued - If a DML operation is issued on a view the INSTEAD OF trigger defines what actions take place.

Now A triggering statement contains:
Trigger timing
For table: BEFORE AFTER
For view: INSTEAD OF

Triggering event: INSERT UPDATE or DELETE
Table name: On table or view
Trigger type: Row or statement
WHEN clause: Restricting condition
Trigger body: is the PL/SQL block

Statement Type - Trigger body executes once for the triggering event. A statement trigger executes once even if no rows are affected at all.

Row Trigger - Trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows. INSTEAD OF triggers are row triggers.

Mutating Table: A mutating table is a table that is currently being modified by an UPDATE DELETE or INSERT statement or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action. A table is not considered mutating for STATEMENT triggers.

The triggered table itself is a mutating table as well as any table referencing it with the FOREIGN KEY constraint. This restriction prevents a row trigger from seeing an inconsistent set of data.


 
Is this answer useful? Yes | No


 
Go To Top


 Sponsored Links

 
About Us -  Privacy Policy -  Terms and Conditions -  Contact -  Ask Question -  Propose Category -  Site Updates 

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape