PLSQL INTERVIEW QUESTIONS

1.How Many triggers can be applied to a table?

There are maximum 12 triggers can be applied to a table.

2. Difference between Syntax error & runtime error in PLSQL?

A Syntax error is easily identified by a PLSQL compiler.For example Incorrect spelling.

but runtime error can be handled with the help of Exception Section block in PLSQL.

3.How to disabled multiple triggers  of a table at a time ?

Alter table Table_name disable   all triggers.

4. Where is the pre defined procedures stored?

In oracle standard package,procedures,functions.

5.What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor,fetches rows of a table values from active set into record variable

& it closes when all the records have been carried out process.

6.What is the difference between Procedure and function?

Function must return value and procedure does not.

Function can be used in sql with some restrictions. where as procedure can not be called directly from sql.

7.Explain Boolean Datatype with Example In Oracle

DECLARE

  V_SAL NUMBER;

  V_FLAG BOOLEAN;

BEGIN

  SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;

  V_FLAG := (V_SAL>=10000);

  IF V_FLAG=TRUE THEN

     DBMS_OUTPUT.PUT_LINE('HIGH GRADE');

  ELSE

     DBMS_OUTPUT.PUT_LINE('LOWER GRADE');

  END IF;

END;

8.Find out the First_name of the employee and the name of the departent for the employee who is managing for worker employee_id is 124.

declare

    fname     employees.first_name%type;

    dname departments.department_name%type;

begin

    select  first_name , department_name into

fname, dname

    from  employees join departments using

(department_id)

    where employee_id = ( select manager_id from

employees    where employee_id= 124);

    dbms_output.put_line(fname);

    dbms_output.put_line(dname);

end;

/

9.Write a PL Sql Block that display Top 10 employees salaries from Employees Table.

DECLARE

  CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;

  EMPREC EMPLOYEES%ROWTYPE;

BEGIN

  OPEN EMPCUR;

  LOOP

     FETCH EMPCUR INTO EMPREC;

     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);

     EXIT WHEN EMPCUR%ROWCOUNT >= 10 OR EMPCUR%NOTFOUND;

  END LOOP;

  CLOSE EMPCUR;

END;

10.What is transaction?

A  transaction is a set of sql statements like data manipulation that works with logical unit .

You can use commit,Rollback Savepoint  command to control the transaction.

At the end of the transaction that makes database changes,oracle makes all the changes permanent save or may be undone.

if your program fails in the middle of a transaction,oracle detect the error and rollback the transaction and restoring the database.

1.Commit : Commit command to make changes permanent save to a database during the current transaction.

2. Rollback : Rollback command executes at the end of current transaction and undo any changes made since the begin transaction.

3.Savepoint : Savepoint command save the current point with the unique name in the processing of a transaction.

4.Autocommit : Set Autocommit on to execute commit statement automatically.

5.Set transaction : PL/SQL set transaction command set the transaction properties such as write/read access.

Savepoint  Savepoint_names is marks the current point in the processing of a transaction.Savepoint let you rollback  part of a transaction instead of the whole transaction.

SQL> create table temp (id number,fname varchar2(30));

Table created.

SQL> insert into temp values(1,'Konal');

1 row created.

SQL> savepoint a;

Savepoint created.

SQL> insert into temp values(2,'Santosh');

1 row created.

SQL> insert into temp values(3,'Nikita');

1 row created.

SQL> insert into temp values(4,'Shalini');

1 row created.

SQL> savepoint b;

Savepoint created.

SQL> select * from temp;

        ID FNAME

---------- ------------------------------

         1 Konal

         2 Santosh

         3 Nikita

         4 Shalini

SQL> insert into temp values(5,'Ahmad');

1 row created.

SQL> rollback to b;

Rollback complete.

SQL> select * from temp;

        ID FNAME

---------- ------------------------------

         1 Konal

         2 Santosh

         3 Nikita

         4 Shalini

SQL>

12:- Write a Plsql block that update the salary of employees from department_number 40 and 90
will have a 10% raise while employees from department_number 90 will have 15% raise.

DECLARE

  CURSOR cur_emp
  IS
    SELECT * FROM employees WHERE department_id = 40 OR department_id = 90;
  rec_emp cur_emp%rowtype;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO rec_emp;

    EXIT WHEN cur_emp%notfound; -- **** leave the loop right here, BEFORE doing the update *****

    IF rec_emp.department_id = 40 THEN
      UPDATE employees
      SET salary                = salary + (salary * 0.1)
      WHERE employee_id         = rec_emp.employee_id;
    elsif rec_emp.department_id = 90 THEN
      UPDATE employees
      SET salary        = salary + (salary * 0.15)
      WHERE employee_id = rec_emp.employee_id;
    END IF;

  END LOOP;
  CLOSE cur_emp;
END;
/