vnraos

 Profile Answers by vnraos 

• Nov 29th, 2008

 

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.

javedans

 Profile Answers by javedans 

• Jan 8th, 2009

 

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

ananth.oracle

 Profile Answers by ananth.oracle 

• Mar 9th, 2009

 

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.

avis_sarkar

 Profile Answers by avis_sarkar Questions by avis_sarkar

• Oct 14th, 2009

 

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.

renu_db_dev

 Profile Answers by renu_db_dev 

• Jul 2nd, 2010

 

1) Why we use PL/SQL array rather than cursor?

PL/SQL array is a collection through which we can iterate just the way we can through cursor. But in case of Cursor the context switching is for every row fetched while for associative array the context swtiching will be once (using bulk collect). It's the performance which is different.

-Renu.