Oracle Database Programming MCQs with Answers
Practice important Oracle Database Programming MCQs with answers and explanations.
Multiple Choice Questions
Q281: To debug a PL/SQL function, which of the following can be used?
- A: Data Dictionary
- B: PL/SQL Block
- C: Database Schema
- D: Function Output
View Answer
A
Q282: What will the following PL/SQL function return? `CREATE OR REPLACE FUNCTION Ask_salary(emp_no IN NUMBER) RETURN NUMBER IS emp_sal NUMBER(11,2); BEGIN SELECT sal INTO emp_sal FROM emp WHERE empno=emp_no; RETURN(emp_sal); END;`
- A: The salary of the employee with the given `emp_no`
- B: The name of the employee with the given `emp_no`
- C: An error message
- D: The total number of employees
View Answer
A
Q283: In a function with IN and OUT parameters, which type of parameter can be modified within the function and return a value?
- A: IN parameter
- B: OUT parameter
- C: INOUT parameter
- D: INOUT parameter cannot be used
View Answer
C
Q284: Which PL/SQL block will call the function `inout_fn` and show the updated value of `ioval`?
- A: `DECLARE retval VARCHAR2(20); ioval VARCHAR2(20) := 'Going in'; BEGIN retval := inout_fn(ioval); DBMS_OUTPUT.PUT_LINE('In: ' || ioval); DBMS_OUTPUT.PUT_LINE('Return: ' || retval); END;`
- B: `DECLARE retval VARCHAR2(20); ioval VARCHAR2(20) := 'Going in'; BEGIN DBMS_OUTPUT.PUT_LINE('In: ' || ioval); retval := inout_fn(ioval); DBMS_OUTPUT.PUT_LINE('Return: ' || retval); END;`
- C: `DECLARE retval VARCHAR2(20); ioval VARCHAR2(20) := 'Going in'; BEGIN retval := inout_fn(ioval); DBMS_OUTPUT.PUT_LINE('Return: ' || retval); DBMS_OUTPUT.PUT_LINE('In: ' || ioval); END;`
- D: `DECLARE retval VARCHAR2(20); ioval VARCHAR2(20) := 'Going in'; BEGIN DBMS_OUTPUT.PUT_LINE('In: ' || ioval); retval := inout_fn(ioval); DBMS_OUTPUT.PUT_LINE('Return: ' || retval); END;`
View Answer
A
Q285: When creating a function, what must always be included in the function definition?
- A: The function name
- B: The function body
- C: The return type
- D: A function name and the return type
View Answer
A
Q286: What will the following PL/SQL procedure do? `CREATE OR REPLACE PROCEDURE hire_employee (name VARCHAR2, salary NUMBER) AS id NUMBER(10) := 0; BEGIN SELECT max(empno) INTO id FROM emp; INSERT INTO emp(empno, ename, sal, hiredate) VALUES (id+1, name, salary, SYSDATE); END hire_employee;`
- A: It will insert a new employee record into the database
- B: It will update an existing employee record
- C: It will delete an employee record from the database
- D: It will retrieve an employee record from the database
View Answer
A
Q287: In the scenario where you need to find the minimum of two values, which type of parameter would be used to return the minimum value from a function?
- A: IN parameter
- B: OUT parameter
- C: INOUT parameter
- D: INOUT parameter cannot be used
View Answer
B
Q288: What does the `CREATE OR REPLACE FUNCTION` statement do if a function already exists?
- A: It replaces the existing function with the new definition
- B: It deletes the existing function
- C: It creates a new function without replacing the existing one
- D: It compiles the existing function without changing it
View Answer
A
Q289: What is a trigger in PL/SQL?
- A: A database object that stores user data
- B: A PL/SQL unit fired automatically by DML operations
- C: A stored procedure for handling errors
- D: A database view
View Answer
B
Q290: Which statement is true about a row-level trigger?
- A: It is fired once per triggering statement
- B: It is fired once for each row affected by the triggering statement
- C: It is only fired if no rows are affected
- D: It cannot be defined on views
View Answer
B