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.

Error Trapping Function(SQLCODE,SQLERRM)

When an exception occurs we can identify the associated ERROR CODE and ERROR MESSAGE by using the exception trapping function.

SQLCODE Function:

• It returns the numeric value for the internal oracle errors.
• It can be assign to a number variables.

SQLERRM Function:

• An error number can be passed to SQLERRM.
• SQLERRM returns the message associated with the error number.
• It can be assign to a varchar2 variables.

INDICES OF Clause In Oracle10g

The INDICES OF clause allows us to load non-contiguous (sparse) arrays by telling Oracle to use just the elements that are populated. Remember in versions prior to 10g that arrays had to be dense and we would use iterators such as [array.FIRST .. array.LAST] or [1 .. array.COUNT] to address them. Check below example

DECLARE
   TYPE my_neted_tbl IS TABLE OF NUMBER;

   var_nt   my_neted_tbl
                        := my_neted_tbl (6, 8, 4, 11, 23, 45, 67, 55, 43, 22);
          /*this we call as dense collection because every index having value*/
BEGIN
   var_nt.DELETE (3, 6);
   /*After deleting data it became a sparse collection*/
   FORALL idx IN INDICES OF var_nt
      INSERT INTO tbl_name
                  (col_name
                  )
           VALUES (var_nt (idx)
                  );
END;

1. Created one nested table
2. In this nested table i am creating 10 index by assigning 10 numeric element.
3. Now i am deleting data for index 3 to 6 value (var_nt.delete(3,6);)  --after deleiting values it became sparse collection
4. By using  FORALL we are inserting data into table , if we are inserting sparse collection then we need to use INDICES OF otherwise it will through error please check below example

Now i am using INDICES OF clause:

Steps1:  Create this table to insert data by using FORALL

CREATE TABLE INDICES_OF_TBL ( id INT, val VARCHAR2(128) );

Steps2:  Create this subprogram and run it.

DECLARE

      TYPE aat_rec IS TABLE OF INDICES_OF_TBL %ROWTYPE
         INDEX BY PLS_INTEGER;
      aa_recs aat_rec;

   BEGIN

      /* Load a sparse array... */
      SELECT object_id, object_name BULK COLLECT INTO aa_recs
      FROM   all_objects
      WHERE  ROWNUM <= 10;

      aa_recs.DELETE(2);
      aa_recs.DELETE(4);
      aa_recs.DELETE(6);

      /* Load table using sparse array... */
      FORALL i IN INDICES OF aa_recs
         INSERT INTO INDICES_OF_TBL 
         VALUES aa_recs(i);

      DBMS_OUTPUT.PUT_LINE(
         TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
         );

   END;
   /

FORALL in Oracle

Bulk Bind or we can say FORALL in Oracle.
The assigning of values to PL/SQL variables in SQL statements is called binding.The binding of an entire collection at once is called bulk binding.

Oracle use two engine to process the PL/SQL code.The PL/SQL engine accepts any valid PL/SQL block or subprogram and SQL engine executes the SQL statements.

Check the below figure how context switch between the PL/SQL and SQL engines.

• PL/SQL engine executes procedural statements and sends SQL statements to the SQL engine.
• SQL engine executes the SQL statements and returns data to the PL/SQL engine(switch between the PL/SQL and SQL engines ).
• If context switch between the PL/SQL and SQL engines is many then it is performance issue.
• That can happen when SQL statements execute inside a loop using collection (index-by table, nested table, varray, or host array) elements as bind variables.

 Check the below example:

DECLARE

TYPE empno_type is table of emp.empno%type

index by pls_integer;

v_emp_no  empno_type;

BEGIN

select empno bulk collect into v_emp_no 

from emp where deptno in (10,20);

for i in v_emp_no.first..v_emp_no.last

loop

update emp set sal=sal+1000 where empno=v_emp_no(i).empno;

commit;

end loop;

END;

in this program UPDATE statement is sent to the SQL engine with each iteration of the FOR loop.

[NB:In such cases, if the SQL statement affects five or more database rows, the use of bulk binds can improve performance considerably.]

Using BULK BIND to improve performance:

• Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. 
• With bulk binds, entire collections(not just individual elements) are passed to the SQL engines for UPDATE the record at once.

Example:

DECLARE

 TYPE empno_type is table of emp.empno%type

 index by pls_integer;

 v_emp_no  empno_type;

BEGIN

 select empno bulk collect into v_emp_no 

 from emp where deptno in (10,20);

 forall i in v_emp_no.first..v_emp_no.last

    update emp set sal=sal+1000 where empno=v_emp_no(i);

commit;

END;

FORALL Statement:

• The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine.

• It allows only DML (INSERT,UPDATE AND DELETE)

• The collection subscript cannot be an expression.

• All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error.

Example:

DECLARE

   TYPE NumList IS TABLE OF NUMBER;

   depts NumList := NumList(10, 20, 30, 40);

BEGIN

   depts.DELETE(3);  -- delete third element

   FORALL i IN depts.FIRST..depts.LAST

      DELETE FROM emp WHERE deptno = depts(i);

      -- raises an "element does not exist" exception

END;

FORALL with SAVE EXCEPTION:

Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of individual insert, update, delete, or merge statements generated by the FORALL.

• Using FORALL  i am inserting 100 record into a table, after 50 record failed with error.Then remaining record will not insert into the table. 
• Using FORALL with SAVE EXCEPTION , the error record will ignore( or error record will insert into your error log table) and remaining record will insert .

Create one table call exception_test.

Example:

  DECLARE

  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();

  l_error_count  NUMBER;

  

  ex_dml_errors EXCEPTION;

  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

BEGIN

  -- Fill the collection.

  FOR i IN 1 .. 100 LOOP

    l_tab.extend;

    l_tab(l_tab.last).id := i;

  END LOOP;

  -- Cause a failure.

  l_tab(50).id := NULL;

  l_tab(51).id := NULL;

  

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.

  BEGIN

    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS

      INSERT INTO exception_test

      VALUES l_tab(i);

  EXCEPTION

    WHEN ex_dml_errors THEN

      l_error_count := SQL%BULK_EXCEPTIONS.count;

      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);

      FOR i IN 1 .. l_error_count LOOP

        DBMS_OUTPUT.put_line('Error: ' || i || 

          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||

          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

      END LOOP;

  END;

END;

/

BULK COLLECT in Oracle

The BULK COLLECT clause tell to SQL engine to bulk bind output collect before returning them to PL/SQL engine, we can use BULK COLLECT in SELECT INTO ,FETCH INTO ,RETURNING INTO clause.


BULK COLLECT in SELECT INTO Clause:

BULK COLLECT in FETCH INTO Clause:

BULK COLLECT in RETURNING Clause:

BULK COLLECT With LIMIT:
Collection is stored in the PGA memory, not the SGA memory. SGA memory is sared by all sessions connected by Oracle Database but PGA memory is allocated for each session. Thus if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, i assition to the memory allocated to the SGA. 

To avoid this memory problem Oracle use the LIMIT clause. 
Example:

DECLARE

CURSOR emp_cur  IS 
 SELECT * FROM EMP;

TYPE emp_aat IS TABLE OF emp_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_emp emp_aat;
V_LIMIT_VAL NUMBER :=5;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur BULK COLLECT INTO l_emp LIMIT V_LIMIT_VAL;
FOR IDX IN 1..l_emp.COUNT
LOOP
 --write your logic
 null;
END LOOP;
END LOOP;
CLOSE emp_cur;

END;
/

Collections In PL/SQL

An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value. Collections are used in some of the most important performance optimization features of PL/SQL, such as BULK COLLECT.(Collection is a group of element of same type.Each element in the list has a unique position number or label we called as "subscript".).

PL/SQL provides three collection types :

1. PL/SQL Table or Index-by tables or Associative array
2. Nested table
3. Variable-size array or Varray.

Syntax :

1. Collection Type Definition:

TYPE type_name IS 
{ assoc_array_type_def|nested_table_type_def| varray_type_def} ;

2. assoc_array_type_def:

TABLE OF element_type [ NOT NULL ]
[ INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) } ]

3. nested_table_type_def:

TABLE OF element_type [ NOT NULL ]

4. varray_type_def:

{VARRAY | VARYING ARRAY } ( size_limit )
OF element_type [ NOT NULL ]

5. element_type:

{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE
}

[element_type: The data type of the collection element (any PL/SQL data type except REF CURSOR)].

6. Collection Variable Declarations:

collection_name  type_name;

The type definition of a nested table or varray can appear either in the declarative part of a block, subprogram, package specification, or package body (in which case it is local to the block, subprogram, or package) or in the CREATE TYPE Statement (in which case it is a standalone stored type).


Here are some guidelines when we need to use which collection:

Use index by tables when:

• Your program needs small lookups.
• The collection can be made at run time in the memory when the package or procedure is initialized.
• The data volume is unknown beforehand.
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential).
• You do not need to store the collection in the database.

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change 
• when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database.
• The number of elements of the varray is known in advance.
• The data from the varray is accessed in sequence.
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray.

Check Associative Array(Index by Table) Vs Nested Table Vs VARRAY

Examples:
Check three type of collection use in program

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

Check All The Collection Methods

Check Collection Exceptions

Difference Between Collection and Record

Interview Questions

PL/SQL Record

A PL/SQL Record is a group of related data items stored in individual fields, each with its own attribute name and data type. To access any field of a record, we use the dot (.) operator.

PL/SQL can handle the following type of records:

• Table Based Record.
• Cursor Based Record.
• User Define Records.

Table Based Record:

%ROWTYPE attributes enables a programmer to create table based and cursor based records.

We are using the EMP table to show the concept of Table Based Record.

DECLARE 
  emp_rec emp%rowtype; 
BEGIN 
   SELECT * into emp_rec 
   FROM emp
   WHERE empno=7839 ;  
   dbms_output.put_line('Employee ID: ' || emp_rec.empno); 
   dbms_output.put_line('Employee Name: ' || emp_rec.ename); 
   dbms_output.put_line('Employee Department: ' || emp_rec.deptno); 
   dbms_output.put_line('Employee Salary: ' || emp_rec.sal); 
END;  

Cursor Based Record:

For Cursor Based Record also we are using  %ROWTYPE. To show the concept of Cursor Based Record we are using MEP table.

DECLARE 
   CURSOR emp_cur is 
      SELECT EMPNO, ENAME, SAL  
      FROM emp; 
   emp_rec emp_cur%rowtype; 
BEGIN 
   OPEN emp_cur; 
   LOOP 
      FETCH emp_cur into emp_rec; 
      EXIT WHEN emp_cur%notfound; 
      DBMS_OUTPUT.put_line(emp_rec.empno || ' ' || emp_rec.ename); 
   END LOOP; 
END;

User Define Record:

PL/SQL provides a user-defined record type that allows you to define the different record structures. 

These records consist of different fields. Suppose you want to keep track of your employee details. 

You might want to track the following attributes about each employee−

• Employee Name
• Employee ID
• Employee SAL
• Employee Department Number

Now we are creating the above record

DECLARE
TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
 emp_id   number,
emp_sal   number,
emp_dent_no number
);

/*Now we are declaring record-name  type_name;*/

EMP_record1 employee_details_typ ;
EMP_record2 employee_details_typ ;
.....

Example:

DECLARE 
  TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
 emp_id   number,
emp_sal   number,
emp_dent_no number
);
   emp_datils1 employee_details_typ;
   emp_datils2 employee_details_typ;
BEGIN 
   -- employee1 specification 
   emp_datils1.emp_name  := 'Rabindra'; 
   emp_datils1.emp_id := 2315;  
   emp_datils1.emp_sal := 50000; 
   emp_datils1.emp_dent_no := 10;  
   -- employee1  specification 
   emp_datils2.emp_name  := 'Samir'; 
   emp_datils2.emp_id := 2316;  
   emp_datils2.emp_sal := 50000; 
   emp_datils2.emp_dent_no := 20;   
  

END; 

Trigger Execution Order

We are all aware that we can write multiple triggers on a single table based on the business requirement.

Execution order when a trigger is fired:

1. BEFORE statement trigger fires first.
2. Next BEFORE row level trigger fires, once for each row affected. 
3. Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers.
4. Finally the AFTER statement level trigger fires.

Prior to 11g, there was no sure answer for this type of situation. Any one trigger (if they are of the same type) can get executed first and there is no guarantee that triggers will be executed again in the same order. This can create issues with the  data. In 11g, Oracle introduced 'FOLLOWS' clause to control the execution order of the triggers when they are of the same type.

FOLLOWS clause in Oracle11g Trigger

The FOLLOWS clause lets you control the firing order of triggers that are defined on the same table and have the same timing point.

Order of Trigger Firing :

If two or more triggers with different timing points (BEFORE, AFTER, INSTEAD OF) are defined for the same statement on the same table, then they fire in the following order:

• All BEFORE statement triggers
• All BEFORE row triggers
• All AFTER row triggers
• All AFTER statement triggers

If two or more triggers are defined with the same timing point, and the order in which they fire is important, then you can control the firing order using the FOLLOWS clause

FOLLOWS

This clause lets you specify the relative firing order of triggers of the same type. Use FOLLOWS to indicate that the trigger being created should fire after the specified triggers.

The specified triggers must already exist, they must be defined on the same table as the trigger being created, and they must have been successfully compiled. They need not be enabled.

You can specify FOLLOWS in the definition of a simple trigger with a compound trigger target, or in the definition of a compound trigger with a simple trigger target. In these cases, the FOLLOWS keyword applies only to the section of the compound trigger with the same timing point as the sample trigger. If the compound trigger has no such timing point, then FOLLOWS is quietly ignored.

Compound Triggers In Oracle 11g

Compound triggers are the database DML triggers which 'compounds' or combines all the triggering timings under one trigger body.
The triggering timings available for database triggers are

• BEFORE STATEMENT 
• AFTER STATEMENT 
• BEFORE EACH ROW
• AFTER EACH ROW

Trigger level variables can be defined which would be visible and accessible in all the timing blocks.
Compound trigger body does not contain any exception section but recommends each block to contain its own exception handler under exception section.

• Trigger firing sequence can be set in Oracle 11g using FOLLOWS keyword
• Compound Triggers to encapsulate multiple triggering timings in one body
• Triggers can be created in ENABLED/DISABLED mode
• The DML triggers in 11g are 25% faster than their earlier versions in terms of compilation, execution and firing according to Oracle.

When to use Compound Triggers:

• Mutating table error ORA-04091
• Multi thread behavior maintains persistent state of session variables till the statement execution finishes. These are defined in the declaration section of the compound trigger.
• Enhanced performance in bulk operations
• Supports encapsulation of multiple program units; thus enhances code interactivity

Compound Triggers Syntax:

CREATE OR REPLACE TRIGGER [TRIGGER NAME]
FOR [DML] ON [TABLE NAME]
COMPOUND TRIGGER
-- Initial section 
-- Declarations 
-- Subprograms
Optional SECTION
BEFORE STATEMENT IS
...;
Optional SECTION
AFTER STATEMENT IS
...;
Optional SECTION
BEFORE EACH ROW IS
...;
Optional SECTION
AFTER EACH ROW IS
...;
END;

Note that none of the timing blocks should be duplicated. Oracle server raises exception PLS-00676 if duplicate timing blocks is found in the compound trigger definition.