PL/SQL one time only procedure

• In PL/SQL one-time-only procedure is an anonymous block of code encapsulated within a package body,used for initializing variables and is executed only once when the package is invoked for the first time in a user session.
• It has a BEGIN but not an END;.
• The END; of the package body serves as the end of the one-time-only procedure within the package body.

Syntax:

/*Create package spe*/
create or replace package test as
  Function Fn_Test...; 
  Procedure Prc_Test ...;
End Test;
/

/*Create package body*/

create or replace package body TEST 
as 
 Function Fn_Test
    Begin 
      something that the function does
      return a value;
    end Fn_Test;

 Procedure Prc_Test 
   Begin
      Something that the procedure does
   end Prc_Test;

 Begin
  /*What ever you have here will be executed only once when the package is called for the first time. 
This is an anonymous block. This anonymous block with out end we call as PL/SQL one time only procedure*/
end Test;

/

Question For Trigger

PL/SQL interview question for Trigger

1. What is Trigger? how many type of trigger ?
2. Why we will use trigger?
3. Mutating in Trigger.
4. Can we write trigger over view? Write one program to update view.
5. What is the use of INSTEAD OF in trigger?
6. What is Compound trigger and what is the use of compound Trigger?
7. What is the use of FOLLOWS clause or how to declare the order of trigger?
8. Normal trigger execution order .
9. Difference between ROW level and Statement level trigger?
10. Difference between BEFORE and AFTER trigger?
11. How can we improve the performance of a trigger ?
12. What is a CALL statement? Explain with an example.What is a CALL statement? Explain with an example.
13. What is the difference between database trigger and stored procedure?
14. COMMIT statement can be executed as part of a trigger?



Ans

1. A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. The triggers executes implicitly whenever a particular event takes place.

Type OF Trigger:

i. Application Trigger:

 Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application (ex. Oracle Forms, Oracle Reports)

ii. Database trigger: 

These triggers are fired whenever a data event (such as DML,DDL) or system event (such as logon or shutdown) occurs on a schema or database.

2. Advantages Of Database Trigger:

Improve Data Security:

• Provide enhanced and complex security checks
• Provide enhanced and complex auditing

Improve Data integrity:

• Enforce dynamic data integrity constraints
• Enforce complex referential integrity constraints
• Ensure that related operations are performed together implicitly

3. Mutating in Trigger:

4. Yes, we can write trigger over view.

5.

11. The performance of a trigger can be improved by using column names along with the UPDATE clause in the trigger. This will make the trigger fire when that particular column is updated and therefore, prevents unnecessary action of trigger when other columns are being updated.

12. A CALL statement within a trigger enables you to call a stored procedure within the trigger rather than writing the Procedural Language/Structured Query Language (PL/SQL) code in it, The procedure may be in PL/SQL, C, or Java language. 

Following is an example of the CALL statement:

CREATE OR REPLACE TRIGGER [trigger_name]

BEFORE UPDATE OF [column_name]  ON [table_name]
FOR EACH ROW
WHEN [condition_clause]
CALL [procedure_name]

13. 

• The main difference between database trigger and stored procedure is that the trigger is invoked implicitly and stored procedure is invoked explicitly.
• Transaction Control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, are not allowed within the body of a trigger whereas, these statements can be included in a stored procedure.

14. No, A COMMIT statement cannot be executed as a part of a trigger because it is a Transaction Control statement, which cannot be executed within a trigger body. Triggers fire within transactions and cannot include any Transaction Control statement within its code.