RDBMS PLSQL Program 40 Script to create user in oracle other than sysadmin to generate triggers

connect system/password;

CREATE USER bca

  IDENTIFIED BY bca

  DEFAULT TABLESPACE USERS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

-- 2 Roles for DEVELOPER 

GRANT CONNECT TO bca CONTAINER=ALL;

GRANT RESOURCE TO bca CONTAINER=ALL;

ALTER USER bca DEFAULT ROLE NONE;

-- 3 System Privileges for DEVELOPER 

GRANT CREATE SESSION TO bca;

GRANT CREATE TABLE TO bca;

GRANT CREATE TRIGGER TO bca;

GRANT CREATE SEQUENCE TO bca;

-- 2 Tablespace Quotas for DEVELOPER 

ALTER USER bca QUOTA UNLIMITED ON SYSAUX;

ALTER USER bca QUOTA UNLIMITED ON USERS;

RDBMS PLSQL Program 39 Program to call package to demonstrate procedure overloading

 SET Serveroutput ON

DECLARE

veno emp.eno%type;

vename emp.ename%type;

vesalary  emp.esalary%type;

vedoj emp.edoj%type;

vedno emp.edno%type;

veno1 emp.eno%type;

vename1 emp.ename%type;

vesalary1  emp.esalary%type;

BEGIN 

veno := '&veno';

vename := '&vename';

vesalary := &vesalary;

vedoj := '&vedoj';

vedno := '&vedno';

veno1 := '&veno1';

vename1 := '&vename1';

vesalary1 := &vesalary1;

emp_package.addemp(veno1,vename1,vesalary1);

emp_package.addemp(veno,vename,vesalary,vedoj,vedno);

END; 

/

RDBMS PLSQL Program 38 Program to create package body to demonstrate procedure overloading

 CREATE OR REPLACE PACKAGE BODY emp_package AS 

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type)

    IS 

   BEGIN 

      INSERT INTO emp (eno,ename,esalary) 

         VALUES(veno, vename, vesalary); 

   END; 

   

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type)

   IS 

   BEGIN 

      INSERT INTO emp (eno,ename,esalary,edoj,edno) 

         VALUES(veno, vename, vesalary, vedoj, vedno); 

   END; 

END emp_package; 

/

RDBMS PLSQL Program 37 Program to create package specification to demonstrate procedure overloading

 CREATE OR REPLACE PACKAGE emp_package AS 

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type); 

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type); 

END emp_package; 

/

RDBMS PLSQL Program 36 Program to call package

 SET Serveroutput ON

DECLARE 

   veno emp.eno%type; 

BEGIN 

veno := '&veno';

DBMS_OUTPUT.PUT_LINE(emp_name.find_name(veno)); 

END; 

/

RDBMS PLSQL Program 35 Program generate package body with single function in it

CREATE OR REPLACE PACKAGE BODY emp_name AS  

    FUNCTION find_name(veno emp.eno%TYPE) return varchar IS 

vename emp.ename%TYPE; 

BEGIN 

            SELECT ename INTO vename 

             FROM emp 

            WHERE eno = veno; 

            RETURN vename; 

    END; 

END emp_name; 

/

RDBMS PLSQL Program 34 Program generate package specification with single function in it

CREATE OR REPLACE PACKAGE emp_name AS 

   FUNCTION find_name(veno emp.eno%type)return varchar; 

END emp_name; 

/

RDBMS PLSQL Program 33 Program to call package

 SET Serveroutput ON

DECLARE 

   veno emp.eno%type; 

BEGIN 

veno := '&veno';

emp_sal.find_sal(veno); 

END; 

/

RDBMS PLSQL Program 32 Program generate package body with single procedure in it

 CREATE OR REPLACE PACKAGE BODY emp_sal AS  

   PROCEDURE find_sal(veno emp.eno%TYPE) IS 

vesalary emp.esalary%TYPE; 

BEGIN 

      SELECT esalary INTO vesalary 

      FROM emp 

      WHERE eno = veno; 

      dbms_output.put_line('Salary: '|| vesalary); 

   END; 

END emp_sal; 

/

RDBMS PLSQL Program 31 Program generate package specification with single procedure in it

CREATE OR REPLACE PACKAGE emp_sal AS 

   PROCEDURE find_sal(veno emp.eno%type); 

END emp_sal; 

/