Rem
Rem Copyright (c) 1998 By Oracle Corporation
Rem
Rem NAME
Rem   irdemo.sql - Demo Program For Invoker-Rights Programs
Rem
Rem
Rem FUNCTION
Rem 
Rem   The Demo Program illustrates the use of Invoker-Rights Programs.  
Rem
Rem   In Pre 8.1, all programs executes with the privileges of its definer.
Rem   Invoker-Rights programs executes with the privileges of the user 
Rem   invoking the program.
Rem
Rem NOTES ON EXECUTING THIS DEMO PROGRAM
Rem
Rem    1) Use svrmgrl for running this demo program.
Rem
Rem    2) Run irdemo.sql as
Rem       @irdemo.sql
Rem
Rem
Rem MODIFIED 
Rem    gviswana      09/30/98 - Add to demo directory
Rem    ukulkarn.in   07/30/98 - Creation.
Rem
Rem

/*
** ##########################################################################
**
**            DESCRIPTION  OF  DEMO  EXAMPLE
**
** ##########################################################################
**
** The Demo Example illustrates the use of Invoker-Rights Programs introduced 
** in 8.1
**
** In the Demo Example, there are two departments
**           a) Finance Department
**           b) Manufacturing Department
**
** Each of the above Department have its own schema for recording Employee 
** Details. 
**     a) Finance Schema and 
**     b) Manufacturing Schema respectively.  
** 
** Whenever a new employee joins the organization, his/her details are stored 
** in Employee_table in the corresponding department's schema. 
** 
** For example, employee joining Finance Department has his/her record stored  
** in Employee Table in Finance Schema. 
** 
** There is a global schema called Company Schema. In company schema, all 
** company related information like all the departments and their 
** corresponding total strength is stored. Whenever a new employee joins 
** the company, the strength of the corresponding department is incremented 
** by one and updated in departments table in Company Schema. 
** 
** Currently each department has its own module(functions/procedures) for 
** performing the following actions. 
**    1) Recording Employee Details when a Employee joins. 
**    2) Deleting Employee Details when a Employee resigns. 
** 
** These modules functionally do pretty much the similar operations.The 
** disadvantage of such a mechanism is that every time a new department is 
** added, a corresponding new module for Employee Joining/resigning process
** needs to be developed/added. 
** 
** Since the process of joining/resigning is same throughout the company, the 
** above scenario leads to duplication of code and maintenance of application 
** becomes difficult. 
** 
** It would be nice to group the Employee Joining/Resigning process in single 
** module (package) at one place 
** i.e in Company Schema and all departments reuse the code residing in Company 
** Schema. 
** 
** Deploying reusable code allows the maintenance of the application to be  
** handled easily. 
** 
** Using Invoker-rights Programs this can be achieved easily as illustrated in 
** the example below. 
**
** How Invoker-Rights programs helps ? 
** -----------------------------------
** Invoker-Rights programs executes with the privileges of the user invoking
** the program. This can be effectively used in above scenario.    
** 
** The Employee Joining/Resigning Module can reside at Company schema/user as  
** Invoker-Rights program. Different departments can invoke the Employee 
** Joining/Resigning module and update corresponding Employee Tables in 
** respective Department Schemas/users.  
** 
** After updating Employee tables, the procedure which updates the total 
** strength of department can be invoked which resides in Company Schema.
** This procedure is not Invoker-rights program and it updates 
** departments_table in Company Schema/user.  
** 
** This can be shown pictorially as follows : 
** 
**
**  Schema Finance             Schema Company           Schema Manufacturing 
** ---------------------      ----------------------|   ----------------- 
** |                   |      |  ______________     |   |               | 
** |      Invoke ______|______|_\|             |/___|___|___Invoke      | 
** |                   |      | /|Emp Join App |\   |   |               | 
** |                   |      |  |_____________|    |   |               | 
** |                   |    __|___|    | (IR) |_____|__ |               | 
** |                   |   /  |        |            |  \| Employee_table| 
** | Employee_table    |  /   |       \|/           |   \    __________ | 
** |    ___________    | /    |Incr_dept_strength   |   |\__\|        | | 
** |    |          |/__|/     |         |  (DR)     |   |   /|        | |  
** |    |          |\  |      |        \|/          |   |    |        | |  
** |    |__________|   |      |Departments_table    |   |    |________| | 
** |                   |      |   ______________    |   |               | 
** |                   |      |   |            |    |   |               | 
** |                   |      |   |____________|    |   |               | 
** ---------------------      -----------------------   ------------------ 
**
**   IR : Invoker-Rights Program 
**   DR : Definer-Rights Program 
**
** Assumptions : 
**
** 1)There are 3 Users  
**     a) Finance 
**     b) Manufacturing 
**     c) Company 
** 
** 2) Each user Finance and Manufacturing have a Employee_table.  
**   The columns of Employee_table are  
**   
**       a) Employee_Id   Varchar2(5)      # Identification Number # 
**       b) Employee_name Varchar2(50)     # Employee Name         # 
**       c) Employee_Address Varchar2(100) # Employee Address      # 
**       c) Dept_code     Varchar2(4)      # Department Code       # 
**       d) Grade         Varchar2(3)      # Grade Of Employee     # 
**       e) Date_Of_Joining Date           # Date Of joining       # 
** 
** 3) A prototype of Employee_table is also created in Company User. 
** 
** 4) Company User has a Departments_Table which keeps track of total 
**    strength of each department. 
**   The columns of Departments_table are 
**       
**      a) Department_code   varchar2(4)    # Department Code   # 
**      b) Department_name   varchar2(30)   # Name of the department # 
**      c) Department_strength Number(4)    # Total Strength Of Dept # 
** 
**
*/

/*
** ########################################################################
** 
**                   CODING  STYLE  USED
**
** #########################################################################
**
**    1.  Uppercase is used for PL/SQL keywords and lower case for defined
**        items.
**
**    2.  Names for local variables are Prefixed with l_
**
**
*/

CONNECT SYSTEM/MANAGER;
SET ECHO OFF;
SET SERVEROUTPUT ON;
SPOOL irdemo.dbg

/*------------------------------------------------------------------------*/
/*     Create user Finance For Storing Information Related To Finance     */
/*------------------------------------------------------------------------*/

CREATE USER FINANCE IDENTIFIED BY FINANCE;
/

GRANT ALL PRIVILEGES TO FINANCE;

/*------------------------------------------------------------------------*/
/*     Create user Mfg For Storing Information Related To Manufacturing   */
/*------------------------------------------------------------------------*/

CREATE USER MFG IDENTIFIED BY MFG;
/

GRANT ALL PRIVILEGES TO MFG;

/*------------------------------------------------------------------------*/
/*     Create user Company For Storing company related information        */
/*------------------------------------------------------------------------*/

CREATE USER COMPANY IDENTIFIED BY COMPANY;
/

GRANT ALL PRIVILEGES TO COMPANY;

CONNECT MFG/MFG;

/*------------------------------------------------------------------------*/
/*     Table for storing Employee Related Information (In Mfg User)       */
/*------------------------------------------------------------------------*/

CREATE TABLE Employee_Table (
    Employee_Id      VARCHAR2(5),
    Employee_Name    VARCHAR2(30),
    Employee_Address VARCHAR2(50),
    Dept_Code        VARCHAR2(5),
    Grade            VARCHAR2(3),
    Joining_Date     DATE );
/

CONNECT FINANCE/FINANCE;

/*------------------------------------------------------------------------*/
/*     Table for storing Employee Related Information (In Finance user )  */
/*------------------------------------------------------------------------*/

CREATE TABLE Employee_Table (
    Employee_Id      VARCHAR2(5),
    Employee_Name    VARCHAR2(30),
    Employee_Address VARCHAR2(50),
    Dept_Code        VARCHAR2(5),
    Grade            VARCHAR2(3),
    Joining_Date     DATE );
/

CONNECT COMPANY/COMPANY;

/*------------------------------------------------------------------------*/
/*          Prototype Of Employee table In company user.                  */
/*          Required for Invoker-Rights Programs to compile.              */
/*------------------------------------------------------------------------*/

CREATE TABLE Employee_Table (
    Employee_Id      VARCHAR2(5),
    Employee_Name    VARCHAR2(30),
    Employee_Address VARCHAR2(50),
    Dept_Code        VARCHAR2(5),
    Grade            VARCHAR2(3),
    Joining_Date     DATE );
/

/*------------------------------------------------------------------------*/
/*  Table for storing department wise Employee Strength                   */
/*------------------------------------------------------------------------*/

CREATE TABLE Departments_Table (
    Department_Code  VARCHAR2(5),
    Department_Name  VARCHAR2(30),
    Department_Strength NUMBER(6) );
/


/*------------------------------------------------------------------------*/
/*   Procedure which increments employee strength by one. This Procedure  */
/*   expects department_code as a parameter.                              */
/*   This procedure is Definer-Rights program and is called when a new    */
/*   Employee joins any Department. The Department Strength Of that Dept  */
/*   is incremented by one.                                               */
/*------------------------------------------------------------------------*/

CREATE OR REPLACE PROCEDURE Increment_Dept_Strength(Deptcd VARCHAR2)
IS
BEGIN
       UPDATE Departments_Table
	SET Department_strength = Department_Strength + 1
       WHERE Department_code = Deptcd;
END Increment_Dept_Strength;
/
SHOW ERRORS


/*------------------------------------------------------------------------*/
/*   Procedure which decrements employee strength by one. This Procedure  */
/*   expects department_code as a parameter.                              */
/*   This procedure is Definer-Rights program and is called when a        */
/*   Employee resigns from any Department. The Department Strength Of     */
/*   that Dept is decremented by one.                                     */
/*------------------------------------------------------------------------*/

CREATE OR REPLACE PROCEDURE Decrement_Dept_Strength(Deptcd VARCHAR2)
IS
BEGIN
       UPDATE Departments_Table
	SET Department_strength = Department_Strength - 1
       WHERE Department_code = Deptcd;
END Decrement_Dept_Strength;
/
SHOW ERRORS


/*------------------------------------------------------------------------*/
/*              Procedure to Initialize Departments_table.                */
/*------------------------------------------------------------------------*/

CREATE OR REPLACE PROCEDURE Initialize_Departments
IS
BEGIN
      INSERT INTO Departments_Table 
	 VALUES('FIN','Finance & Accounting ',0);
       INSERT INTO Departments_Table 
	 VALUES('MFG','Manufacturing        ',0);
END Initialize_Departments;
/
SHOW ERRORS



/*------------------------------------------------------------------------*/
/* Package which consists of Procedures for                               */
/*      a) Inserting Employee Details - when a new Employee Joins         */
/*      b) Deleting Employee Details - When a employee Resigns            */
/* Note that the procedures are Invoker-Rights Program. This means that   */
/* the procedures executes with the privileges of the invoker and not     */
/* definer.        In this case Definer is Company user.                  */
/* This allows us to reuse the code. Different departments can invoke the */
/* procedures when a new employee joins or a employee resigns the dept.   */
/*------------------------------------------------------------------------*/

CREATE OR REPLACE PACKAGE Emp_Pack AUTHID CURRENT_USER IS
    PROCEDURE Insert_Emp_Details(Empid Varchar2, Empname Varchar2, 
      Empadd Varchar2, Deptcd Varchar2, Grade Varchar2);
    PROCEDURE Delete_Emp_Details(EmpId Varchar2, Deptcd Varchar2);
END Emp_Pack;
/
SHOW ERRORS


/*------------------------------------------------------------------------*/
/*    Define body of the Package                                          */
/*------------------------------------------------------------------------*/

CREATE OR REPLACE PACKAGE BODY Emp_pack IS

    /* ---------------------------------------------------------*/
    /*       Procedure to Insert Employee Details.              */
    /* As the procedure is invoker-rights program, it executes  */
    /* with the privileges of the invoker. For example, if the  */
    /* procedure is called by finance department then all the  */
    /* external names in DML statements are resolved in finance */
    /* user. Here "Employee_Table" is resolved in Invoker's Schema*/
    /* But external name "Increment_Dept_Strength" resolves to  */
    /* company schema because it is Definer-right Program.       */
    /* --------------------------------------------------------*/

    PROCEDURE Insert_Emp_Details(Empid Varchar2, Empname Varchar2, 
      Empadd Varchar2, Deptcd Varchar2, Grade Varchar2)
    IS
      /* Variable declared to get current user name and print */
      l_current_user  VARCHAR2(30);
    BEGIN
	/* Get Current User Name */
        SELECT username INTO l_current_user FROM USER_USERS;   
	DBMS_OUTPUT.NEW_LINE();
	DBMS_OUTPUT.PUT('Connect As '||l_current_user);
	DBMS_OUTPUT.PUT(' And Add Details For '||RTRIM(Empname));
	DBMS_OUTPUT.PUT_LINE(' having Id '||RTRIM(empid));

	INSERT INTO Employee_table VALUES(Empid, Empname, Empadd, Deptcd,
	  Grade, SYSDATE);

        /* Increment Department strength */
        Increment_Dept_Strength(Deptcd);
    END Insert_Emp_Details;


    /* ---------------------------------------------------------*/
    /*       Procedure to Delete Employee Details.              */
    /* This procedure is also Invoker-Rights program.Same comments*/
    /* as for Insert_emp_details apply to this procedure        */
    /* ---------------------------------------------------------*/

    PROCEDURE Delete_Emp_Details(EmpId Varchar2, Deptcd Varchar2)
    IS
      /* Variable declared to get current user name and print */
      l_current_user  VARCHAR2(30);
    BEGIN
	/* Get Current User Name */
        SELECT username INTO l_current_user FROM USER_USERS;   
	DBMS_OUTPUT.NEW_LINE();
	DBMS_OUTPUT.PUT('Connect As '||l_current_user);
	DBMS_OUTPUT.PUT_LINE(' And Delete Details Of Employee Having Id '
	 ||RTRIM(EmpId));

       DELETE Employee_Table
	WHERE Employee_id = EmpId;

        /* Decrement Department strength */
       Decrement_Dept_Strength(Deptcd);
    END Delete_Emp_Details;

END Emp_Pack;
/
SHOW ERRORS

/*-----------------------------------------------------------------------*/
/* Grant Execute privileges On Emp_Pack to users Mfg and Finance         */
/*-----------------------------------------------------------------------*/

GRANT EXECUTE ON Emp_Pack TO MFG;
/

GRANT EXECUTE ON Emp_Pack TO FINANCE;
/

/*-----------------------------------------------------------------------*/
/* Initialize the Departments_table                                      */
/*-----------------------------------------------------------------------*/

BEGIN
   Initialize_Departments;
END;
/


/*-----------------------------------------------------------------------*/
/* Simulate that few Employee Joins Manufacturing Department.            */
/*-----------------------------------------------------------------------*/

CONNECT MFG/MFG;
SPOOL OFF;
SET ECHO OFF;

SET SERVEROUTPUT ON
SPOOL irdemo.lis


DECLARE

   /* Declare cursor to display Names and Empids of Employee Joined */  
   /* Note that the Table "Employee_table" in Cursor l_c1 refers to */
   /* Employee_table in MFG user.                                   */

   l_emp_id  VARCHAR2(5);
   l_emp_name VARCHAR2(30);
   CURSOR l_c1 IS  SELECT Employee_id, Employee_Name
		  FROM Employee_Table;
BEGIN

   /* Insert Employee Details For new Employees */

   Company.Emp_Pack.Insert_Emp_Details('M001','John Miller','New York','MFG',
      'A1');

   Company.Emp_Pack.Insert_Emp_Details('M002','Kings','California','MFG',
      'B1');

   Company.Emp_Pack.Insert_Emp_Details('M003','Martin','Texas','MFG',
      'A2');

   Company.Emp_Pack.Insert_Emp_Details('M004','James','New York','MFG',
      'A1');

   Company.Emp_Pack.Insert_Emp_Details('M005','Ford','New York','MFG',
      'A1');

   /* Display the Employee Ids and Names of the Employees Joined */
   /* Note that this also checks that the Employee records are inserted */
   /* into Employee_table of MFG user(the invoker).              */

   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE(); 
   DBMS_OUTPUT.PUT('*** List Details Of Employees in MFG User ***');
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('   Employee Id               Employee Name'); 
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   OPEN l_c1;
   LOOP
	FETCH l_c1 INTO l_emp_id, l_emp_name;
	EXIT WHEN l_c1%NOTFOUND;
        DBMS_OUTPUT.NEW_LINE();
	DBMS_OUTPUT.PUT_LINE('     '||l_emp_id||'                    '||
	RTRIM(l_emp_name));
   END LOOP;
   CLOSE l_c1;
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE();
END;
/

/*-----------------------------------------------------------------------*/
/* Simulate that few Employee Joins Finance Department.                  */
/* Also simulate few Employee Resign From Finance Department             */
/*-----------------------------------------------------------------------*/

CONNECT FINANCE/FINANCE;

SET SERVEROUTPUT ON

DECLARE

   /* Declare cursor to display Names and Empids of Employee Joined */  
   /* Note that the Table "Employee_table" in Cursor l_c1 refers to */
   /* Employee_table in FINANCE user.                               */

   l_emp_id  VARCHAR2(5);
   l_emp_name VARCHAR2(30);
   CURSOR l_c1 IS  SELECT Employee_id, Employee_Name
		  FROM Employee_Table;
BEGIN

   /* Insert Employee Details For new Employees */

   Company.Emp_Pack.Insert_Emp_Details('F001','James Martin','Washington','FIN',
      'A2');

   Company.Emp_Pack.Insert_Emp_Details('F002','Henry','California','FIN',
      'A1');

   Company.Emp_Pack.Insert_Emp_Details('F003','Bruce','California','FIN',
      'A1');

   Company.Emp_Pack.Insert_Emp_Details('F004','Clerk','Texas','FIN',
      'C1');

   /* Delet Employee Details For Employee 'F002' */

   Company.Emp_Pack.Delete_Emp_Details('F002','FIN');

   /* Display the Employee Ids and Names of the Employees Joined         */
   /* Note that this also checks that the Employee records are           */
   /* inserted/deleted into/from Employee_table of FINANCE user(invoker) */

   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.PUT_LINE('*** List Details Of Employees In Finance User ***');
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('   Employee Id               Employee Name'); 
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   OPEN l_c1;
   LOOP
	FETCH l_c1 INTO l_emp_id, l_emp_name;
	EXIT WHEN l_c1%NOTFOUND;
        DBMS_OUTPUT.NEW_LINE();
	DBMS_OUTPUT.PUT_LINE('     '||l_emp_id||'                    '||
	RTRIM(l_emp_name));
   END LOOP;
   CLOSE l_c1;
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE();
END;
/

CONNECT COMPANY/COMPANY;

SET SERVEROUTPUT ON

DECLARE

   /* Declare cursor to display Department Name and Employee Strength */  
   /* of the Department.                                              */

   l_dept_strength  NUMBER(4);
   l_dept_name  VARCHAR2(30);
   CURSOR l_c1 IS SELECT Department_Name, Department_Strength
		  FROM Departments_table;
BEGIN

   /* Display Department Name and Employee Strength Of the Department */
   /* This also checks that the procedure calls increment_dept_strength */
   /* and decrement_dept_strength are Definer-Rights Programs         */

   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.PUT('** List Department Wise Strength From Company User **');
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE(' Department Name            Employee Strength');
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   OPEN l_c1;
   LOOP
       FETCH  l_c1 INTO l_dept_name, l_dept_strength;
       EXIT WHEN l_c1%NOTFOUND;
       DBMS_OUTPUT.NEW_LINE();
       DBMS_OUTPUT.PUT_LINE('  '||l_dept_name||'               ' ||
	 RTRIM(l_dept_strength));
   END LOOP;
   CLOSE l_c1;
   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
   DBMS_OUTPUT.NEW_LINE();
   DBMS_OUTPUT.NEW_LINE();
END;
/

SPOOL OFF;

/*******  Clean Up **********************************/

CONNECT SYSTEM/MANAGER

DROP USER FINANCE CASCADE;
/

DROP USER MFG CASCADE;
/

DROP USER COMPANY CASCADE;
/
