Oracle Database Programming MCQs with Answers
Practice important Oracle Database Programming MCQs with answers and explanations.
Multiple Choice Questions
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;
- C:
- D:
View Answer
A
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
View Answer
A
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
- C:
- D:
View Answer
A
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
View Answer
B
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
View Answer
B
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
View Answer
A
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;
View Answer
A
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
View Answer
A
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
View Answer
A
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
- C:
- D:
View Answer
A