Subprograms in PL/SQL.

Subprograms in PL/SQL:
Subprograms are used to provide modularity and encapsulate a sequence of statements.

• Once subprograms are built and validated, they can be used in a number of applications.
• Subprogram also provide abstraction.
• Subprograms are named PL/SQL blocks that can accepts parameters.
• A Subprogram can also have a declarative part, an executable part and an exception handling part.

Important features of subprogram:
Modularity:

• Subprograms allow us to break a program into manageable, well-defined logical codules.

Reusability:

• Subprograms once executed can be used in any number of application.

Maintainability: 

• Subprograms can simplify maintenance, because if a subprogram is affected, only its definition changes.

Continue reading →

Difference between Anonymous and Named PL/SQL block.

Difference between Anonymous and Named PL/SQL block:

Anonymous PL/SQL Block	Named PL/SQL Block
1.This is an unnamed PL/SQL Block.	1.This is a named PL/SQL Block.
2.These are stored in operating system.	2.These are stored in oracle database.
3.There is no security.	3.Oracle is providing security.
4.There is no information hiding facility.	4.There is a hiding facility.
5.It requires every time compilation process.	5.It is compiled once and ready to execute
6.Granting privileges on this is not possible.	6.Granting privileges ispossible.
7.These are not accessible to other program.	7.These are accessible by other oracle tools like SQL*PLUS, Oracle Forms,Reports.

Continue reading →

Clauses In PL/SQL.

Clauses In PL/SQL:
Returning Clause:

• This clause is valid at the end of any DML statement.
• It is used to get information about the row or rows just processed.
• Variable is the PL/SQL or SQL expression, which can include columns or pseudocolumns of the current table.
• Variable is the PL/SQL variable into which the result will be stored.

BULK COLLECT Clause:

• It used to collect more than I row at a time.
• It is used as part of the SELECT INTO, FETCH INTO, or RETURNING INTO  Clause and will retrieve rows from the query into the indicated collections.  

BULK COLLECT clause with SELECT:
Illustrations

• Write a PL/SQL Program to delete the 30 department employees salary if the deleted employee salary is above 2500 insert that employee details into the “delete_log” table.

SQL>DECLARE
  TYPE name IS TABLE OF
    Emp.ename%TYPE INDEX BY BINARY_INTEGER;
    TYPE pays IS TABLE OF
    emp.sal%TYPE INDEX BY BINARY_INTEGER;
    n name;
    p pays;
BEGIN
    SELECT ename,sal BULK COLLECT INTO n,p
    FROM emp;
    FOR i IN 1..n.COUNT
    LOOP
      display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
    END LOOP;
END;

BULK COLLECT clause DELETE:
Table  delete_log
ename   varchar2(20),
basic   number(7,2),
dod     timestamp

• Write a PL/SQL program to hike the salary 35% of 20 department employees the hicked salary is above 2500 insert employee details into a trace table. (hint: create a ‘trace’ table column like ename varchar2(20),vsal number(10),dou  timestamp.).

SQL>DECLARE
    TYPE name IS TABLE OF
    emp.ename%TYPE INDEX BY BINARY_INTEGER;
    TYPE pays IS TABLE OF
    Emp.sal%TYPE INDEX BY BINARY_INTEGER;
    n name;
    p pays;
  BEGIN
    DELETE FROM emp
    WHERE deptno=30
    RETURNING ename,sal BULK COLLECT INTO n,p;
    FOR i IN 1..n.COUNT
    LOOP
       Display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
           IF p(i)>2500 THEN
             INSERT INTO delete_log
             VALUES(n(i),p(i),sysdate);
          END IF;
    END LOOP;
  END;

BULK COLLECT clause UPDATE:
SQL>DECLARE
   TYPE name IS TABLE OF
   emp.ename%TYPE INDEX BY BINARY_INTEGER;
   TYPE pays IS TABLE OF
   Emp.sal%TYPE INDEX BY BINARY_INTEGER;
   n name;
   p pays;
 BEGIN
   UPDATE emp SET sal=sal+sal*0.35
   WHERE deptno=20
   RETURNING ename,sal BULK COLLECT INTO n,p;
     FOR i IN 1..n.COUNT
       LOOP
         Display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
         IF p(i)>2500 THEN
         INSERT INTO trace VALUES(n(i),p(i),sysdate);
       END IF;
   END LOOP;
 END;

BULK COLLECT clause CURSOR
SQL>DECLARE
       TYPE name IS TABLE OF
     emp.ename%TYPE INDEX BY BINARY_INTEGER;
       TYPE pays IS TABLE OF
       emp.sal%TYPE INDEX BY BINARY_INTEGER;
      CURSOR c_bulkcollect IS
      SELECT ename,sal FROM EMP;
      n name;
       p pays;
  BEGIN
       OPEN c_bulkcollect;
       FETCH c_bulkcollect BULK COLLECT INTO n,p;
       FOR i IN 1..n.COUNT
        LOOP
           Display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
         END LOOP;
  END;

Continue reading →

Nested type Collection.

Nested type Collection:
           TYPE type_name IS RECORD
           (EmpRecord Emp%ROWTYPE,
           DeptRecord dept%ROWTYPE,
           Element RECORD);
SQL>DECLARE
                      TYPE pf_info is RECORD
                      (pfno NUMBER(4),
                      amount NUMBER(14,2));
                      TYPE emp_res IS RECORD
                      (eid NUMBER(4),
                      name VARCHAR2(20),
                      basic NUMBER(12,2),
                      pf pf_info);
                      TYPE etab IS TABLE OF emp_rec
                      INDEX BY BINARY_INTEGER;
                      ctr  NUMBER(3):=1;
                      e etab;
           BEGIN
                      FOR i IN(SELECT empno,ename,sal basic,
                      Sal*0.12 pamt FROM emp
                      WHERE sal>2000)
                      LOOP
                                 e(ctr).eid:=i.empno;
                                 e(ctr).name:=i.ename;
                                 e(ctr).basic:=i.basic;
                                 e(ctr).pf.pfno:=i.empno+5;
                                 e(ctr).pf.amount:=i.pamt;
                                 ctr:=ctr+1;
                      END LOOP;
                                 display(‘employee detailes are:’);
                      FOR MyIndex in 1..e.count
                      loop 
                                 display(e(MyIndex).eid||’ ‘||e(MyIndex).name||’ ‘||e(MyIndex).basic||’ ‘||e(MyIndex).pf.pfno||’ ‘||e(MyIndex).pf.amount);
                      END LOOP;
           END;

Continue reading →

Write a PL/SQL Program to i.Hike the Employee salary as 100 if employee salary is in between 0 to 1000. ii.Hike the Employee salary as 200 if employee salary is in between 1001 to 2000. iii.Other than these two condition hike 300 insert the update employee details into the those table.

Write a PL/SQL Program to 
i.Hike the Employee salary as 100 if employee salary is in between 0 to 1000.
ii.Hike the Employee salary as 200 if employee salary is in between 1001 to 2000.
iii.Other than these two condition hike 300 insert the update employee details into the those table.

SQL>DECLARE
                     TYPE eno IS TABLE OF
                     emp.empno%TYPE INDEX BY BINARY_INTEGER;
                     TYPE name IS TABLE OF
                     emp.ename%TYPE INDEX BY BINARY_INTEGER;
                     TYPE pays IS TABLE OF
                     emp.sal%TYPE INDEX BY BINARY_INTEGER;
                     e eno;
                     n name;
                     p pays;
                     ctl number:=1;
             BEGIN
                                  FOR i IN(SELECT empno,ename,sal FROM emp)
                                  LOOP
                                  e(ctl):=i.empno;
                                  n(ctl):=i.ename;
                                  p(ctl):=i.sal;
                                  ctl:=ctl+1;
                                  END LOOP;
                                  for MyIndex IN 1..e.count
                                  LOOP
                                  IF p(MyIndex) BETWEEN 0 AND 1000 THEN
                                  p(MyIndex):=p(MyIndex)+100;
                                  ELSIF p(MyIndex) BETWEEN 1001 AND 2000 THEN
                                  p(MyIndex):=p(MyIndex)+200;
                                  ELSE
                                  p(MyIndex):=p(MyIndex)+300;
                                  END IF;
                                  UPDATE emp SET sal=p(MyIndex)
                                  WHERE empno=e(MyIndex);
                                  INSERT INTO trace              VALUE(n(MyIndex),p(MyIndex),sysdate);
                                  Display(n(MyIndex)||’ ‘||p(MyIndex));
                                  END LOOP;
          END;

Continue reading →

Referencing PL/SQL Table.

Referencing PL/SQL Table:

• PL/SQL_Tablename(Primary_key_value);
• PRIMARY_KEY_VALUE belongs to type BINARY_INTEGER.
• The primary key value can be negative indexing need not start with 1.
• The method make PL/SQL table easier to use are.

COUNT:

• Returns the number of elements that a PL/SQL table currently contais.

SQL>DECLARE

                    TYPE name IS TABLE OF

                    VARCHAR2(50) INDEX BY BINARY_INTEGER;

                    n name;

          BEGIN

                    n(0):=’Siva’;

                    n(1):=’Rama’;

                    n(2):=’Krishna’;

                    display(‘The full name is ‘||n(0)||’ ‘||n(1)||’ ‘||n(2));

          END;

Note: In oracle 11g Negative index are not allowed.

                    Trace table:

                    ename varchar2(20),

                    usal number(7,2),

                    dou timestamp

Continue reading →

PL/SQL Tables.

PL/SQL Tables:

• PL/SQL tables are temporary array-like objects used in a PL/SQL Block.
• They are modelled as database tables, but are not same.
• PL/SQL tables are very dynamic in operation, giving the simulation to pointers in ‘C’ language.
• PL/SQL TABLES use a “PRIMARY KEY” to give array like access to rows.
• PL/SQL table can be declared in the declarative part of any block. Subprogram or package.
• It is similar to an array in Third generation language.
• PL/SQL table should contain two components.

• A “PRIMARY KEY” of data type BINARY_INTEGER, that indexes the PL/SQL table.

• A column of a scalar or Record data type which stores the PL/SQL  table elements.

• PL/SQL table can increase in size dynamically as they are unconstrainted.
• A PL/SQL TABLE must be declare in two steps.

1)First we define a table type. 

2)We declare a variable of PL/SQL table as data type.

Syntax:

TYPE<Type name> IS TABLE OF 

{column type OR Table.column%TYPE OR PL/SQL RECORD} 

INDEX BY BINARY_INTEGER;

• The number of rows in PL/SQL table can increase dynamically, hence a PL/SQL table can grow as new rows are added.

Continue reading →

Defining PL/SQL Record.

Defining PL/SQL Record:
To define a user defined PL/SQL data type.

• Type name -> is the name of the Record type.
• Element  name -> it is the name of the field within the record.
• Element data type -> it is the data type of the element.
• Expr -> it is the field type OR a nInitial value.
• The NOT NULL constraint prevents the assigning of NULL’ s to those fields.
• Element declaration are like variable declaration each Element has a unique name and a specific data type.
• We must create the data type first and then declare an identifier using the declared data type.

Illustration:
SQL>DELCARE
                  TYPE erec IS RECORD
                  (veno NUMBER(4),
                  Vname emp.ename%TYPE,
                  Basic emp.sal%TYPE,
                  i dept%ROWTYPE,
                  vgross number(16,2));
                  e erec;
         BEGIN
                  e.veno:=&employe;
                  select ename,sal,dept.deptno,dname into
                  e.vname,e.basic,e.i.deptno,e.i.dname
                  from emp,dept
                  where emp.deptno=dept.deptno and empno=e.vono;
                  e.vgross:=e.basic+e.basic*0.25+e.basic*0.35-e.basic*0.12;
                  display(e.veno||’ ‘||e.basic||’e.i.deptno||’ ‘||e.i.dname||’ ‘||e.vgross);
         END;

Continue reading →

Understanding PL/SQL Collections.

Understanding PL/SQL Collections:

• PL/SQL, similar to other programming languages such as C,C++, allows using arrays and records.
• PL/SQL has two composite types : records and collections.

PL/SQL Records:

• A PL/SQL Record is allows you to treat several variables as a unit.
• PL/SQL Record are similar to structure in C.
• When a “RECORD TYPE” of fields are declared then they can be manipulated as a unit through out the Application.

Note:

• In the composite data type RECORD, we can specify the data type of the column.
• Each RECORD defined can have as many Fields as necessary.
• Fields declared as ‘not null’ must be initialized in the declaration part.
• A record can be initialized in its declaration part unlike PL/SQL tables, which doesn’t allow initialization in the declaration part.
• The DEFAULT key word can also be used when defining fields.
• A RECORD can be the component of another RECORD.

Syntax:
                          TYPE type_name IS RECORD
                          (Element1<data type>,
                          Element2<data type>,
                          Element3<data type>,
                          Element4<data type>,
                          Elementn<data type>);
Field_declaration syntax is 
                          Elementname{ Elementdatatype(size) OR
                                       Recordvariable%TYPE OR
                                       Table.column%TYPE  OR
                                       Table%ROWTYPE}
                                       [[NOT NULL]{:=OR DEFAULT} expr]

Continue reading →

Trapping Non-Predefined oracle server errors

Trapping Non-Predefined oracle server errors:

• We can associate a named exception with a particular oracle error.
• The Non-predefined oracle server error is trapped by declaring it first or by using the OTHERS exception handle.
• The declare EXCEPTION is  RAISED implicitly by the oracle server.
• The PL/SQL PRAGMA EXCEPTION_INIT() can be used for associating EXCEPTION name with an oracle error number.
• The PRAGMA EXCEPTION_INTI() tells the PL/SQL engine completely to associate an EXCEPTION name with an oracle error number.
• The PRAGMA EXCEPTION_INIT() allows programmer to refer to any internal EXCEPTION by the name and associate that to specific handles.
• Pragma is a directive of compiler which tells compiler to associate error no with user declared exception at compile time.

Steps:
         1.Declare Exception.
         2.Associate Exception with Oracle error No.
Using Pragma
Exception_init(exception_name,oracle_error_number);
         3.Handle the raised exception.

• Exeption_name is the name of an exception declare prior to the pragma.
• Oracle_error_number is the desied error code to be associate with this named exception.

SQL>DECLARE
                  Pk_vio EXCEPTION;
                  PRAGMA EXCEPTION_INIT(pk_vio,-00001);
         BEGIN
                  Insert into emp(empno,ename,sal,deptno)
                  values(&eno,’&ename’,’&sal,&deptno);
         EXCEPTION
                  when pk_vio then
                  dbms_output.put_line(‘Duplicate empno is not allowed here’);
         END;

Continue reading →

Write a PL/SQL Program to hike the i.Employee salary as 35% if employee having a salary. ii.If employee not having a salary rise the user exception and give the salary 3000.

Write a PL/SQL Program to hike the

  i.Employee salary as 35% if employee having a salary.

  ii.If employee not having a salary rise the user exception and give the salary 3000.

SQL>DECLARE
                  Salary_missing EXCEPTION;
                  i emp%rowtype;
         BEGIN
                  i.empno:=&eno;
                  SELECT ename,sal into i.ename, i.sal
                  FROM emp
                  WHERE empno=i.empno;                      
                  IF i.sal IS NULL THEN
                           RAISE salary_missing;
                  ELSE
                           i.sal:=i.sal+i.sal*0.25;
                           UPDATE emp SET sal=i.sal
                           WHERE empno=i.empno;
                           display(‘The emp det are ‘||i.ename||’ ‘||i.sal);
                  END IF;
                  EXCEPTION
                           WHEN no_data_found THEN
                           display(i.empno||’ is not exists’);
                           display(SQLCODE||’ ‘||SQLERRM);
                           WHEN salary_missing THEN
                           display(‘The emp is not having any salary so give salary as 3000’);
                           UPDATE emp SET sal=i.sal
                           WHERE empno=i.empno;
                           display(SQLCODE||’ ‘||SQLERRM);
                           WHEN others then
                           display(‘The SUHE’);
                  END;

Continue reading →

Write a PL/SQL Program to hike the employee salary as 35% if entered employee salary not less than(<) 2000. If salary is less than(<) 2000 display the message and suspend the program process.

Write a PL/SQL Program to hike the employee salary as 35% if entered employee salary not less than(<) 2000. If salary is less than(<) 2000 display the message and suspend the program process.

SQL>DECLARE
  i emp%rowtype;
BEGIN
  i.empno:=&eno;
  SELECT ename,sal into i.ename,ilsal
  from emp
  where empno=i.empno;
    IF i.sal<2000 THEN
      Raise_application_error(-20345,’The emp sal is less than 2000 so no updation’);
      i.sal:=i.sal+i.sal*0.35;
      UPDATE emp set sal=i.sal
      WHERE empno=i.empno;
    ELSE
      i.sal:=i.sal+i.sal*0.35;
      UPDATE emp set sal=i.sal
      WHERE empno=i.empno;
      display(‘The emp det are’||i.ename||’ ‘||i.sal);
      END IF;
END;

Continue reading →

Using a packaged procedure.

Using a packaged procedure:
RAISE_APPLICATION_ERROR(error number,’Error message’);

RAISE APPLICATION ERROR:

• This Built-in procedure is used to create your own error message, which can be more descriptive than named exceptions.
• It is used to communicate a predefined exception interactively by returning a non standard error code and error message.
• Using this procedure we can report error to application and avoid returning unhandled exception.

Note:

• Error number must exists between -20,000 and -20,999
• Error_message is the text associate with this error, and keep_errors is Boolean value.
• The error_message parameter must be less than 512 characters.

SQLCODE FUNCTION:

• It returns the current error code.
• For a user defined exception it returns 1, +100 NO_DATA_FOUND exception.

SQLERRM:

• It returns the current error message text.
• SQLERRM returns the message associated with the error number.
• The maximum length of a message returned by the SQLERRM functions is 512 bytes.

Continue reading →

User-defined Exception.

User-defined Exception:

• A user-defined exception is an error that is defined by the program.
• The developers to handle the business situations define user-defined exceptions during the execution of the PL/SQL block.
• User defined exceptions are defined by the following two techniques:
• Using a flow control statement RAISE:
• Raise statement transfer the control of the block from the execution part of the PL/SQL block to the exception handing part of the block.

Steps:

1. Declare Exception
2. Raise in Executable section explicitly  using RAISE<Exception_handler_name>;
3. Handle the raised exception.

Continue reading →

Pre defined Exception.

Pre defined (System defined) Exception:

• Predefined exception are raised automatically by the system during run time.
• Predefined exception are already available in the program it is not necessary to declare them in the declarative section like user_defined exception.

Predefined Exception List:

Exception Name	Error No	Description
ORA-0001	DUP_VAL_ON_INDEX	Unique constraint violated.
ORA-1001	INVALID_CURSOR	Illegal cursor operation.
ORA-1403	NO_DATA_FOUND	No data found.
ORA-1422	TOO_MANY_ROWS	A SELECT INTO statement Matches more than one row.
ORA-1722	INVALID_NUMBER	Conversion to a number Failed for example, ‘netlojava street 1’  not valid.
ORA-6502	VALUE_ERROR	Truncation, arithmetic, or Conversion error.
ORA-01476	ZERO_DIVIDE	Divisor is equal to zero.
ORA-06511	CURSOR_ALREADY_OPEN	This exception raised when we try to open a cursor   which is already opened.
ORA-01017	LOGIN_DENIED	This exception is raised When we try to enter oracle using invalid username/password.

 
Examples:
SQL>DECLARE
       v_empno emp.empno%TYPE:=&empno;
       v_ename emp.ename%TYPE;
       v_job emp.job%TYPE;
    BEGIN
       SELECT empno=v_empno;
       DBMS_OUTPUT.PUT_LINE(‘The empno detail are ‘||v_ename||’ ‘||v_job);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE(‘The empno is not found.’);
    END;
SQL>DECLARE
       v_accno kcb_acc_tab.accno%TYPE:=&accno;
       v_name kcb_acc_tab.name%TYPE:=&name;
       v_bal kcb_acc_tab.bal%TYPE:=&bal;
    BEGIN
       INSERT INTO kcb_acc_tab(accno,name,bal)
       VALUES(v_accno,v_name,v_bal);
       DBMS_OUTPUT.PUT_LINE(‘Account detailes are inserted successfully’);
    EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
       DBMS_OUTPUT.PUT_LINE(‘accno already exists’);
    END;
SQL>DECLARE
       v_empno emp.empno%TYPE;
       v_ename emp.ename%TYPE;
       v_deptno emp.deptno%TYPE;
    BEGIN
       SELECT empno,ename,deptno INTO v_empno,v_ename,v_deptno
       FROM emp
       WHERE empno=7788 AND ename=’SCOTT’;
       DBMS_OUTPUT.PUT_LINE(‘The scott works in department number:’||v_deptno);
       Select empno,ename,deptno into v_empno,v_ename,v_deptno
       FROM emp
       Where deptno=10;
       DBMS_OUTPUT.PUT_LINE(‘The Employee number:’||v_empno);
       DBMS_OUTPUT.PUT_LINE(‘The Employee name:’||v_ename);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE(‘Error:There is no such empno or ename or deptno’);
       WHEN TOO_MANY_ROWS THEN
       DBMS_OUTPUT.PUT_LINE(‘Error:More than one Employee works in department number 10’);
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(‘Error occurred while processing the program’);    
    END;

Continue reading →