Oracle Database Programming MCQs with Answers
Practice important Oracle Database Programming MCQs with answers and explanations.
Chapter: 19
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
Correct Answer:
A
Chapter: 19
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
Correct Answer:
A
Chapter: 19
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
Correct Answer:
C
Chapter: 19
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;`
Correct Answer:
A
Chapter: 19
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
Correct Answer:
A
Chapter: 19
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
Correct Answer:
A
Chapter: 19
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
Correct Answer:
B
Chapter: 19
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
Correct Answer:
A
Chapter: 20
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
Correct Answer:
B
Chapter: 20
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
Correct Answer:
B
Chapter: 20
Q291:
Which type of trigger is executed before the triggering statement?
A. AFTER trigger
B. INSTEAD OF trigger
C. BEFORE trigger
D. ROW trigger
Correct Answer:
C
Chapter: 20
Q292:
What is the primary difference between a function and a procedure in PL/SQL?
A. A function does not return a value, but a procedure does
B. A function must return a value, but a procedure may or may not return a value
C. A function can only be used in SQL statements
D. A procedure cannot have parameters
Correct Answer:
B
Chapter: 20
Q293:
What type of trigger would you use to log events in a database table?
A. BEFORE trigger
B. AFTER trigger
C. DML trigger
D. DDL trigger
Correct Answer:
C
Chapter: 20
Q294:
Which of the following is the correct syntax to create a trigger in PL/SQL?
A. CREATE TRIGGER trigger_name ON table_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
B. CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON table_name
C. CREATE TRIGGER trigger_name {INSERT | UPDATE | DELETE} ON table_name [FOR EACH ROW]
D. CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} ON table_name {INSERT | UPDATE | DELETE}
Correct Answer:
B
Chapter: 20
Q295:
A trigger created on a view will be fired when a DML statement is executed on the associated:
A. Table
B. Schema
C. Database
D. Trigger
Correct Answer:
D
Chapter: 20
Q296:
What does the `WHEN` clause in a trigger do?
A. It specifies the trigger action
B. It determines the trigger timing
C. It defines the condition under which the trigger is executed
D. It disables the trigger
Correct Answer:
B
Chapter: 20
Q297:
In the following syntax, which keyword specifies that the trigger should be fired before an UPDATE statement?
A. BEFORE
B. AFTER
C. INSTEAD OF
D. WHEN
Correct Answer:
A
Chapter: 20
Q298:
What type of trigger is used to enforce referential integrity constraints in a table?
A. Row-level trigger
B. Statement-level trigger
C. BEFORE trigger
D. AFTER trigger
Correct Answer:
C
Chapter: 20
Q299:
The following PL/SQL block is used to create a trigger that stores old product prices:
A. CREATE OR REPLACE TRIGGER price_history_trigger BEFORE UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price); END; /
B. CREATE OR REPLACE TRIGGER price_history_trigger AFTER UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price); END; /
C. CREATE OR REPLACE TRIGGER price_history_trigger BEFORE UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:new.product_id, :new.product_name, :new.supplier_name, :new.unit_price); END; /
D. CREATE OR REPLACE TRIGGER price_history_trigger AFTER UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:new.product_id, :new.product_name, :new.supplier_name, :new.unit_price); END; /
Correct Answer:
B
Chapter: 20
Q300:
Which PL/SQL block creates a trigger to prevent insertion if the department number does not exist in the `dept` table?
A. CREATE OR REPLACE TRIGGER Emp_dept_check BEFORE INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NOT NULL) DECLARE Dummy INTEGER; BEGIN SELECT Deptno FROM Dept WHERE Deptno = :new.Deptno; END;
B. CREATE OR REPLACE TRIGGER Emp_dept_check BEFORE INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NOT NULL) DECLARE Dummy INTEGER; CURSOR Dummy_cursor IS SELECT Deptno FROM Dept WHERE Deptno = :new.Deptno FOR UPDATE OF Deptno; BEGIN OPEN
C. CREATE OR REPLACE TRIGGER Emp_dept_check AFTER INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NOT NULL) BEGIN IF NOT EXISTS (SELECT 1 FROM Dept WHERE Deptno = :new.Deptno) THEN RAISE_APPLICATION_ERROR(-20000, 'Invalid Department Number
D. CREATE OR REPLACE TRIGGER Emp_dept_check AFTER INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NULL) BEGIN RAISE_APPLICATION_ERROR(-20000, 'Deptno cannot be NULL'); END;
Correct Answer:
B