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>