Oracle Database Programming MCQs with Answers
Practice important Oracle Database Programming MCQs with answers and explanations.
Chapter: 20
Q301:
What does the `DBMS_OUTPUT.PUT_LINE` procedure do in a trigger?
A. It outputs the value of a variable to the database
B. It logs the result to a file
C. It prints messages to the console or log
D. It updates the database table
Correct Answer:
C
Chapter: 20
Q302:
When a trigger is disabled, what happens?
A. It is executed only when manually invoked
B. It is ignored and does not fire during DML operations
C. It logs an error message
D. It still runs but does not perform any actions
Correct Answer:
B
Chapter: 20
Q303:
How can you enable a trigger that has been disabled?
A. Use the `DROP TRIGGER` statement
B. Use the `CREATE TRIGGER` statement
C. Use the `ALTER TRIGGER` statement with `ENABLE`
D. Use the `ALTER TABLE` statement with `ENABLE`
Correct Answer:
C
Chapter: 20
Q304:
Which PL/SQL block creates a log entry every time the `emp` table is updated?
A. CREATE OR REPLACE TRIGGER Log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO Emp_log (Log_date, Action) VALUES (SYSDATE, 'Employee Table Updated'); END;
B. CREATE OR REPLACE TRIGGER Log_emp_update BEFORE UPDATE ON emp BEGIN INSERT INTO Emp_log (Log_date, Action) VALUES (SYSDATE, 'Employee Table Updated'); END;
C. CREATE OR REPLACE TRIGGER Log_emp_update AFTER INSERT OR UPDATE ON emp BEGIN INSERT INTO Emp_log (Log_date, Action) VALUES (SYSDATE, 'Employee Table Updated'); END;
D. CREATE OR REPLACE TRIGGER Log_emp_update BEFORE INSERT OR UPDATE ON emp BEGIN INSERT INTO Emp_log (Log_date, Action) VALUES (SYSDATE, 'Employee Table Updated'); END;
Correct Answer:
A
Chapter: 21
Q305:
What is a PL/SQL package?
A. A stored procedure
B. A database object that groups related database objects
C. A type of trigger
D. A view
Correct Answer:
B
Chapter: 21
Q306:
What is included in the Package Specification?
A. Implementation details of subprograms
B. Private declarations only
C. Definitions and public declarations
D. Exception handling routines
Correct Answer:
C
Chapter: 21
Q307:
Which part of a package contains the implementation details?
A. Package Specification
B. Package Body
C. Package Header
D. Package Interface
Correct Answer:
B
Chapter: 21
Q308:
What is the primary advantage of using a PL/SQL package?
A. Reducing database size
B. Increasing database performance by grouping logically related objects
C. Ensuring database security
D. Improving network connectivity
Correct Answer:
B
Chapter: 21
Q309:
Which part of the package declares types, variables, constants, exceptions, cursors, and subprograms available for use?
A. Package Specification
B. Package Body
C. Package Header
D. Package Implementation
Correct Answer:
A
Chapter: 21
Q310:
What is the correct syntax to create a package specification?
A. CREATE PACKAGE package_name AS [specification]; CREATE PACKAGE BODY package_name AS [implementation]; END package_name;
B. CREATE PACKAGE package_name AS [specification]; END package_name; CREATE PACKAGE BODY package_name AS [implementation]; END package_name;
C. CREATE PACKAGE package_name AS [specification]; CREATE PACKAGE BODY package_name AS [implementation]; END package_name;
D. CREATE PACKAGE BODY package_name AS [implementation]; END package_name; CREATE PACKAGE package_name AS [specification]; END package_name;
Correct Answer:
C
Chapter: 21
Q311:
Which statement accurately describes the Package Body?
A. It defines the types, variables, and constants available to the application
B. It declares the public interface to the package
C. It implements the subprograms and defines private details of the package
D. It creates the database objects necessary for the package
Correct Answer:
D
Chapter: 21
Q312:
What is the primary purpose of encapsulating related types, items, and subprograms into a PL/SQL package?
A. To hide implementation details and provide a clear interface
B. To improve the database schema
C. To create multiple copies of database objects
D. To optimize query performance
Correct Answer:
A
Chapter: 21
Q313:
Which PL/SQL code snippet correctly creates a package specification for a function?
A. CREATE PACKAGE my_package AS FUNCTION my_function(p1 NUMBER) RETURN NUMBER; END my_package;
B. CREATE PACKAGE my_package AS FUNCTION my_function(p1 NUMBER) RETURN NUMBER; CREATE PACKAGE BODY my_package AS FUNCTION my_function(p1 NUMBER) RETURN NUMBER IS BEGIN RETURN p1; END; END my_package;
C. CREATE PACKAGE my_package AS PROCEDURE my_procedure; END my_package;
D. CREATE PACKAGE BODY my_package AS FUNCTION my_function(p1 NUMBER) RETURN NUMBER; END my_package;
Correct Answer:
A
Chapter: 21
Q314:
In which part of the package would you define a function's implementation?
A. Package Specification
B. Package Body
C. Package Header
D. Package Interface
Correct Answer:
B
Chapter: 21
Q315:
What is the purpose of the `CREATE PACKAGE BODY` statement in PL/SQL?
A. It creates the package specification
B. It defines the implementation details for the subprograms declared in the package specification
C. It deletes the package
D. It creates a new database table
Correct Answer:
B
Chapter: 21
Q316:
Fill in the blank: The Package Specification provides the _______ to the application, while the Package Body provides the _______.
A. public interface; implementation details
B. implementation details; public interface
C. private declarations; public interface
D. public interface; private declarations
Correct Answer:
A
Chapter: 21
Q317:
What are the two main parts of a PL/SQL package?
A. Package Specification and Package Body
B. Package Header and Package Body
C. Package Interface and Package Implementation
D. Package Definition and Package Schema
Correct Answer:
A
Chapter: 21
Q318:
Which part of the package is responsible for defining subprograms and cursors?
A. Package Specification
B. Package Body
C. Package Header
D. Package Interface
Correct Answer:
B
Chapter: 21
Q319:
Fill in the blank: To create a package in PL/SQL, you must first define the package _______ and then define the package _______.
A. body; specification
B. specification; body
C. interface; implementation
D. header; content
Correct Answer:
B