Oracle Database Programming MCQs with Answers
Practice important Oracle Database Programming MCQs with answers and explanations.
Chapter: 18
Q261:
Which of the following PL/SQL code blocks will output the name of employees whose names contain 'AK'?
A. CREATE OR REPLACE PROCEDURE name_proc AS cursor c1 is select * from emp where ename like ('%AK%'); c2 c1%rowtype; BEGIN loop fetch c1 into c2; dbms_output.put_line (c2.ename); exit when c1%notfound; end loop; END name_proc;
B. CREATE OR REPLACE PROCEDURE name_proc AS cursor c1 is select ename from emp where ename like ('%AK%'); c2 c1%rowtype; BEGIN loop fetch c1 into c2; dbms_output.put_line (c2.ename); exit when c1%notfound; end loop; END name_proc;
Correct Answer:
A
Chapter: 18
Q262:
What is the purpose of the `EXIT` statement in a PL/SQL loop?
A. To terminate the loop when a condition is met
B. To continue the loop to the next iteration
C. To exit the procedure after the loop completes
D. To stop the execution of the procedure
Correct Answer:
A
Chapter: 18
Q263:
Which PL/SQL procedure uses a `CURSOR` to fetch employee names from a department?
A. CREATE OR REPLACE PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS Emp_name VARCHAR2(10); CURSOR c1 IS SELECT Ename FROM Emp WHERE deptno = dept_num; BEGIN OPEN c1; LOOP FETCH c1 INTO Emp_name; DBMS_OUTPUT.PUT_LINE(Emp_name); EXIT WHEN C1%NOTFOUND; END LOO
B. CREATE OR REPLACE PROCEDURE adjust_salary (in_employee_id IN emp.ename%TYPE, in_percent IN NUMBER, employeename out varchar2) IS BEGIN UPDATE emp SET sal = sal + sal * in_percent / 100 WHERE empno = in_employee_id; select ename into employeename from emp
Correct Answer:
A
Chapter: 18
Q264:
To handle exceptions in a PL/SQL procedure, which section is used?
A. The BEGIN-END block
B. The EXCEPTION block
C. The DECLARE block
D. The PROCEDURE block
Correct Answer:
B
Chapter: 18
Q265:
What does the following PL/SQL block output: `DECLARE a number; b number; c number; PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGIN IF x < y THEN z:= x; ELSE z:= y; END IF; END; BEGIN a:= 23; b:= 45; findMin(a, b, c); dbms_output.put_line(' Minimum of (23, 45) : ' || c); END;`?
A. The maximum of 23 and 45
B. The minimum of 23 and 45
C. The average of 23 and 45
D. The sum of 23 and 45
Correct Answer:
B
Chapter: 18
Q266:
Which PL/SQL keyword is used to create a procedure that can be called multiple times?
A. CREATE
B. REPLACE
C. EXECUTE
D. DEFINE
Correct Answer:
A
Chapter: 18
Q267:
What is the correct way to drop a procedure in PL/SQL?
A. DROP PROCEDURE procedure_name;
B. DELETE PROCEDURE procedure_name;
C. REMOVE PROCEDURE procedure_name;
D. CLEAR PROCEDURE procedure_name;
Correct Answer:
A
Chapter: 18
Q268:
What does the `dbms_output.put_line` function do in a PL/SQL procedure?
A. Outputs a line of text to the console
B. Updates a row in a table
C. Declares a new procedure
D. Fetches data from a cursor
Correct Answer:
A
Chapter: 18
Q269:
What does the `FETCH` statement do in a PL/SQL cursor loop?
A. Reads the next row from the cursor into a record
B. Closes the cursor
C. Opens the cursor
D. Exits the loop when the cursor is exhausted
Correct Answer:
A
Chapter: 18
Q270:
Which procedure statement updates the salary of an employee by a percentage provided at run-time?
A. CREATE OR REPLACE PROCEDURE adjust_salary (in_employee_id IN emp.ename%TYPE, in_percent IN NUMBER, employeename out varchar2) IS BEGIN UPDATE emp SET sal = sal + sal * in_percent / 100 WHERE empno = in_employee_id; select ename into employeename from emp
B. CREATE OR REPLACE PROCEDURE update_salary (in_employee_id IN emp.ename%TYPE, in_percent IN NUMBER, employeename out varchar2) IS BEGIN UPDATE emp SET sal = sal * in_percent / 100 WHERE empno = in_employee_id; select ename into employeename from emp where
Correct Answer:
A
Chapter: 18
Q271:
What is a primary purpose of using a PL/SQL procedure?
A. To perform specific tasks efficiently
B. To create database tables
C. To define new database structures
D. To manage database users
Correct Answer:
A
Chapter: 18
Q272:
What type of parameter does not allow modification of its value inside a PL/SQL procedure?
A. IN
B. OUT
C. IN OUT
D. TEMP
Correct Answer:
A
Chapter: 19
Q273:
What is the major difference between a function and a procedure in PL/SQL?
A. A function must return a value, whereas a procedure may or may not return a value
B. A function can only take IN parameters, while a procedure can take IN and OUT parameters
C. A function can perform multiple tasks, while a procedure performs a specific task
D. A function cannot be called multiple times, whereas a procedure can
Correct Answer:
A
Chapter: 19
Q274:
Which keyword is used to create a function in PL/SQL?
A. CREATE FUNCTION
B. CREATE PROCEDURE
C. CREATE OR REPLACE FUNCTION
D. CREATE OR REPLACE PROCEDURE
Correct Answer:
C
Chapter: 19
Q275:
What is the purpose of the `RETURN` statement in a PL/SQL function?
A. It terminates the function execution
B. It specifies the datatype of the function result
C. It returns a value to the calling environment
D. It defines the function’s parameters
Correct Answer:
C
Chapter: 19
Q276:
In PL/SQL, what type of parameter can be both input and output?
A. IN parameter
B. OUT parameter
C. INOUT parameter
D. INOUT parameter cannot be used
Correct Answer:
B
Chapter: 19
Q277:
Which of the following is true about a PL/SQL procedure?
A. It can return multiple values
B. It must always return a value
C. It can be created to perform a specific task
D. It is used primarily for computation
Correct Answer:
C
Chapter: 19
Q278:
What does the `CREATE OR REPLACE FUNCTION` statement do?
A. It creates a new function or replaces an existing function
B. It deletes an existing function
C. It creates a new procedure or replaces an existing procedure
D. It alters the structure of an existing function
Correct Answer:
A
Chapter: 19
Q279:
In the syntax `CREATE OR REPLACE FUNCTION function_name RETURN return_datatype`, what does `return_datatype` specify?
A. The name of the function
B. The datatype of the value the function returns
C. The parameters the function accepts
D. The body of the function
Correct Answer:
B
Chapter: 19
Q280:
What is the result of the following PL/SQL block if the function `totalemployees` is defined incorrectly?
A. An error message will be displayed
B. The function will execute successfully
C. The function will return zero employees
D. The function will return an incorrect number of employees
Correct Answer:
A