Here are all 40 full revision questions from Unit 4, followed by their brief answers:
🔹 Unit 4 – Oracle and PL/SQL – Full Revision Questions and Answers
1. What is a View? Explain the limitations of Views.
A view is a virtual table based on a SQL SELECT query.
Limitations: Cannot do DML on views with GROUP BY, JOINs, or subqueries. No indexing
directly on views.
2. What do you understand by transactions in PL/SQL?
A transaction is a sequence of SQL statements treated as a unit.
Use COMMIT, ROLLBACK, and SAVEPOINT to manage them.
3. What is a Snapshot? Explain with an example.
A snapshot (materialized view) stores results of a query physically for performance.
CREATE MATERIALIZED VIEW emp_snapshot AS SELECT * FROM employees;
4. What is Privilege? How are GRANT and REVOKE used?
Privilege: Authorization to perform actions (e.g., SELECT, INSERT).
GRANT SELECT ON emp TO user1;
REVOKE SELECT ON emp FROM user1;
5. What are database triggers in Oracle?
Triggers are procedures that run automatically when specific events occur (e.g., INSERT,
UPDATE).
Types: BEFORE/AFTER, ROW/STATEMENT level.
6. What is the package state in Oracle?
The package state holds global variables’ values across calls in a session, improving
performance.
7. Explain Simple, Composite, and Unique Indexes.
Simple: One column
Composite: Multiple columns
Unique: No duplicate values allowed
8. What is a function-based index?
An index built on an expression.
CREATE INDEX idx_upper_name ON emp(UPPER(name));
9. What is a Sequence in Oracle?
A sequence auto-generates numeric values.
CREATE SEQUENCE s START WITH 1 INCREMENT BY 1;
10. Explain different parameters used in a Sequence.
START WITH: initial number
INCREMENT BY: step
MAXVALUE, MINVALUE: limits
CYCLE, NOCYCLE: repeat or not
CACHE, NOCACHE: preallocation
11. How is user management done in Oracle?
CREATE USER u IDENTIFIED BY p;
ALTER USER u ...
DROP USER u;
Also assign privileges via GRANT.
12. What are LOBs in Oracle?
Large Objects used for big data like images or text:
CLOB, BLOB, NCLOB, BFILE
13. What are the benefits of Object Views?
Support complex data
Map relational to object models
Allow encapsulation and reuse
14. Describe the structure of a PL/SQL block.
DECLARE: variable definitions
BEGIN: main logic
EXCEPTION: error handling
END: close block
15. What are parameterized cursors?
Cursors that take input parameters for dynamic queries.
CURSOR c1(deptno NUMBER) IS SELECT * FROM emp WHERE dept = deptno;
16. Write the syntax to create and use a sequence.
CREATE SEQUENCE s START WITH 1;
SELECT s.NEXTVAL INTO v FROM dual;
17. How can you alter or drop a sequence?
ALTER SEQUENCE s INCREMENT BY 5;
DROP SEQUENCE s;
18. What do you understand by Object-Oriented Programming in Oracle?
Oracle supports OOP features:
Object types
Inheritance
Encapsulation
Methods inside objects
19. What are Nested Tables and VARRAYs in Oracle?
Nested Table: Unbounded collection
VARRAY: Fixed-size collection
Used in tables or PL/SQL code.
20. What are different types of cursors in PL/SQL?
Implicit: Automatic
Explicit: User-defined
Cursor FOR loop, Parameterized Cursor
21. What is ROWID and ROWNUM in Oracle?
ROWID: Physical address of a row
ROWNUM: Temporary row number in result
22. Explain Object-Oriented Programming in Oracle.
(Repeated) Covered in Q18.
23. Explain GRANT and REVOKE statements with examples.
GRANT INSERT ON emp TO user1;
REVOKE INSERT ON emp FROM user1;
24. What is exception handling in PL/SQL?
Handled using EXCEPTION block.
BEGIN ... EXCEPTION WHEN OTHERS THEN ... END;
25. What do you understand by OOP in Oracle?
(Repeated again) Covered in Q18.
26. What are database triggers?
(Repeated) Covered in Q5.
27. What are control structures in PL/SQL?
Conditional: IF-THEN-ELSE
Iterative: LOOP, WHILE
Sequential: Normal execution
28. What are object-oriented features in Oracle?
Object Types
Inheritance
Methods
Encapsulation
29. Explain Indexing in Oracle.
Improves query speed.
Types:
B-tree
Bitmap
Function-based
Composite
30. What are Object Types in Oracle?
User-defined data types.
CREATE TYPE emp_obj AS OBJECT (...);
31. What is an Object View?
(Repeated) Same as Q13. A view that uses object types.
32. What is PL/SQL? List its advantages.
PL/SQL = Procedural Language + SQL
Advantages:
Block structure
Error handling
Reusable code (procedures/functions)
Supports OOP
33. How do you manage users in Oracle?
(Repeated) Covered in Q11.
34. Explain Function-Based Index with example.
(Repeated) Covered in Q8.
35. What are cursors in PL/SQL?
(Repeated) See Q20. Cursors are used to fetch multiple rows one-by-one.
36. What is a cluster in Oracle?
Clusters group related tables physically for better access speed.
37. What are triggers in Oracle?
(Repeated) See Q5.
38. What do you mean by package state in Oracle?
(Repeated) See Q6. Holds global data across sessions.
39. What is a transaction in PL/SQL?
(Repeated) See Q2.
40. What is an Object View in Oracle?
(Repeated) See Q13.
Let me know if you'd like me to compile this into a PDF or convert it into MCQ/Flashcards for
fast revision.
