Oracle PL SQL interview questions
1. Q: What is PL/SQL?
A: PL/SQL is Oracle's procedural extension to SQL that supports variables, conditions, loops, and exception handling.
2. Q: What are the benefits of PL/SQL?
A: Improved performance, better error handling, modular code, and tight SQL integration.
3. Q: What is a PL/SQL block?
A: A PL/SQL block is the basic unit of PL/SQL, consisting of DECLARE, BEGIN, EXCEPTION (optional), and END.
4. Q: Write a basic PL/SQL block.
A:
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
5. Q: What is %TYPE in PL/SQL?
A: It defines a variable with the same datatype as a table column. Incase data type or length change in base table no need to update same in PL SQL procedure.
It will update automatically and process re compile by database
6. Q: What is %ROWTYPE?
A: A record that holds an entire row of a table or cursor. We can refer individual columns when we need. By using rowtypevariable.columnname
7. Q: Difference between %TYPE and %ROWTYPE?
A: %TYPE is for one column; %ROWTYPE includes all columns in a row.
8. Q: What are triggers?
A: Stored procedures that execute automatically on specific events like INSERT, UPDATE, DELETE.
Normally trigger apply in tables and views for all rows or statement level.
It also have BEFORE AND AFTER events the values cann be refer or check by using :old.column_name or :new column_name.
The triggers use for audit purpose or update values in other tables
9. Q: Types of triggers in PL/SQL?
A: BEFORE, AFTER, INSTEAD OF, DDL, Compound triggers.
10. Q: What is a stored procedure?
A: A named PL/SQL block that performs a task and can be called multiple times.
It provides modularity and reusability. The execution is faster
11. Q: Difference between a function and a procedure?
A: Functions return values and can be used in SQL; procedures do not return values directly.
12. Q: How to return a value from a function?
A: Use RETURN. Example:
FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS...
13. Q: What is a cursor?
A: A pointer to a result set of a query used to process multiple rows.
14. Q: Types of cursors?
A: Implicit and explicit. Implicit cursor created by oracle it self usually when we execute any SQL statement and close by it self. In the other hand explicit cursor define by developer and handle by them as well
15. Q: When to use explicit cursors?
A: When you need control over row-by-row processing. We can apply control statement like IF , Case etc and close it when required
16. Q: Write an explicit cursor example.
A:
CURSOR c1 IS SELECT name FROM students;
BEGIN
FOR r IN c1 LOOP
DBMS_OUTPUT.PUT_LINE(r.name);
END LOOP;
END;
17. Q: What is a cursor FOR loop?
A: A loop that opens, fetches, and closes an explicit cursor automatically.
18. Q: What is an exception?
A: An error condition during execution.
19. Q: Types of exceptions?
A: Predefined, user-defined, and unnamed.
20. Q: How to raise a user-defined exception?
A: Declare and use RAISE:
DECLARE
ex EXCEPTION;
BEGIN
RAISE ex;
END;
21. Q: What is RAISE_APPLICATION_ERROR?
A: Raises user-defined error messages with custom error codes. The error code should be -20000 or above
22. Q: What is DBMS_OUTPUT.PUT_LINE?
A: A built-in procedure that displays output to console. It use to check or debug the values.
23. Q: What is a package?
A: A group of related procedures, functions, variables. Package provide modularity, reusability. We can achieve function overloading via package procedure or functions
24. Q: Components of a package?
A: Specification and Body.
25. Q: Can a function have OUT parameters?
A: No, functions return values using RETURN, not OUT.
26. Q: What is dynamic SQL?
A: SQL executed dynamically at runtime.
27. Q: Example of dynamic SQL?
A:
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE department_id = 10';
28. Q: What is a collection?
A: A single-dimensional data structure (like arrays).
29. Q: Types of collections?
A: Associative Arrays, Nested Tables, VARRAYs.
30. Q: What is BULK COLLECT?
A: Retrieves multiple rows into a collection in one query. The data store in memory and different options can be performed there
31. Q: Example of BULK COLLECT?
A:
SELECT name BULK COLLECT INTO v_names FROM students;
32. Q: What is FORALL?
A: Executes DML statements in bulk for collections.
33. Q: Example of FORALL?
A:
FORALL i IN indices.FIRST .. indices.LAST
INSERT INTO logs VALUES (ids(i), names(i));
34. Q: Can we commit inside a trigger?
A: No, DML triggers do not allow COMMIT.
35. Q: What is SYSDATE?
A: A function that returns the current date and time.
36. Q: What is NVL()?
A: Replaces NULL with a specified value. There are other related functions like nvl2 and collases
37. Q: What is TO_CHAR() used for?
A: Converts a date or number to a string. We can control date formats and number format via to char
38. Q: How do you write a loop?
A:
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
39. Q: Difference between LOOP, WHILE, FOR?
A: LOOP - unconditional, it continues until we write exit statement
WHILE - precondition, it execute until the condition meet
FOR - iteration range. A range provides for execution
40. Q: What are %FOUND, %NOTFOUND, %ROWCOUNT?
A: Cursor attributes to check operation results.
41. Q: Can a procedure be called inside a trigger?
A: Yes, if the procedure does not use COMMIT/ROLLBACK.
42. Q: What is a view?
A: A virtual table created using a SELECT statement.
43. Q: Can views be updated?
A: Yes, under certain conditions like single-table and no group functions.
44. Q: Difference between IN, OUT, IN OUT parameters?
A: IN: read-only, OUT: write-only, IN OUT: read-write.
45. Q: Can SELECT be used in a function?
A: Yes. Certain condition applied on that
46. Q: What is a pragma?
A: A compiler directive like PRAGMA EXCEPTION_INIT. It also called copiler instructions. Famous one is pragma autonomous transaction, use to save data even commit not passed
47. Q: What is a mutating table error?
A: Occurs when a trigger tries to query or change the table that fired it.
48. Q: How to avoid mutating table error?
A: Use compound triggers or temporary storage via packages.
49. Q: How to handle NULL values?
A: Use NVL, COALESCE, CASE expressions.
50. Q: What is a compound trigger?
A: A trigger that handles multiple timing points in one body, useful to avoid mutating table errors.
0 Comments