PL/SQL - Pipe Function and Mutating Trigger

GeekInterview.com > Interview Questions > Oracle > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 237 of 240

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: 3

Last Update: March 09, 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

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

javedans

Member Since: January 2009 Contribution: 1

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 | No

1 0

Overall Rating: +1

March 09, 2009 15:50:00

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 | No

1 0

Overall Rating: +1

Go To Top