PLSQL Interview Questions

HOME

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 WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100 DECLARE


--WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100
DECLARE
N NUMBER(3) :=0;
BEGIN
WHILE N<=100
LOOP
N :=N+2;
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;


9.Which privilege can be granted only on a DIRECTORY?

 READ,Write,Execute

10. Viewing Constraints

 --view all constraint definitions and names

- SELECT constraint_name, constraint_type, search condition

FROM user_constraints

WHERE table_name = 'EMP'