Oracle PL/SQL Interview Questions
QUESTION 1
When creating a function in SQL *Plus, you receive this message: "Warning: Function created with compilation errors." Which command can you issue to see the actual error message?
A. SHOW FUNCTION_ERROR
B. SHOW USER_ERRORS
C. SHOW ERRORS
D. SHOW ALL_ERRORS
Answer: C
QUESTION 2 Which four triggering events can cause a trigger to fire? (Choose four)
A. A specific error or any errors occurs.
B. A database is shut down or started up.
C. A specific user or any user logs on or off.
D. A user executes a CREATE or an ALTER table statement.
E. A user executes a SELECT statement with an ORDER BY clause.
F. A user executes a JOIN statement that uses four or more tables.
Answer: A,B,C,D
QUESTION 3
Examine this procedure:
CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2)
IS
BEGIN
INSERT INTO PLAYER (ID, LAST_NAME)
VALUES (V_ID, V_LAST_NAME);
COMMIT;
END;
This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?
A. EXECUTE UPD_BAT_STAT(V_ID);
B. UPD_BAT_STAT(V_ID);
C. RUN UPD_BAT_STAT(V_ID);
D. START UPD_BAT_STAT(V_ID);
Answer: B
Oracle PL/SQL interview Questions
QUESTION 4
Which statement about triggers is true?
A. You use an application trigger to fire when a DELETE statement occurs.
B. You use a database trigger to fire when an INSERT statement occurs.
C. You use a system event trigger to fire when an UPDATE statement occurs.
D. You use INSTEAD OF trigger to fire when a SELECT statement occurs.
Answer: B
QUESTION 5 You want to create a PL/SQL block of code that calculates discounts on customer orders. -This code will be invoked from several places, but only within the program unit ORDERTOTAL. What is the most appropriate location to store the code that calculates the discounts?
A. A stored procedure on the server.
B. A block of code in a PL/SQL library.
C. A standalone procedure on the client machine.
D. A block of code in the body of the program unit ORDERTOTAL.
E. A local subprogram defined within the program unit ORDEgRTOTAL.
Answer: E
QUESTION 6
Which type of argument passes a value from a procedure to the calling environment?
A. VARCHAR2
B. BOOLEAN
C. OUT
D. IN
Answer: C
QUESTION 7
You create a DML trigger. For the timing information, which is valid with a DML trigger?
A. DURING
B. INSTEAD
C. ON SHUTDOWN
D. BEFORE
E. ON STATEMENT EXECUTION
Answer: D
QUESTION 8
You are about to change the arguments of the CALC_TEAM_AVG function. Which dictionary view can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function?
A. USER_PROC_DEPENDS
B. USER_DEPENDENCIES
C. USER_REFERENCES
D. USER_SOURCE
Answer: B
QUESTION 9
A CALL statement inside the trigger body enables you to call ______.
A. A package.
B. A stored function.
C. A stored procedure.
D. Another database trigger.
Answer: C
Incorrect Answers:
A. Package can't be called, we call a procedure inside the package.
B. We can't call a function use CALL statement because function must return a value.
D. Trigger can't be called, they are execute automatically when the trigger event occure.
QUESTION 10 You need to remove the database triggerBUSINESS_HOUR. Which command do you use to remove the trigger in the SQL *Plus environment?
A. DROP TRIGGER business_hour;
B. DELETE TRIGGER business_hour;
C. REMOVE TRIGGER business_hour;
D. ALTER TRIGGER business_hour REMOVE;
E. DELETE FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'BUSINESS_HOUR';
Answer: A
QUESTION 1
When creating a function in SQL *Plus, you receive this message: "Warning: Function created with compilation errors." Which command can you issue to see the actual error message?
A. SHOW FUNCTION_ERROR
B. SHOW USER_ERRORS
C. SHOW ERRORS
D. SHOW ALL_ERRORS
Answer: C
QUESTION 2 Which four triggering events can cause a trigger to fire? (Choose four)
A. A specific error or any errors occurs.
B. A database is shut down or started up.
C. A specific user or any user logs on or off.
D. A user executes a CREATE or an ALTER table statement.
E. A user executes a SELECT statement with an ORDER BY clause.
F. A user executes a JOIN statement that uses four or more tables.
Answer: A,B,C,D
QUESTION 3
Examine this procedure:
CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2)
IS
BEGIN
INSERT INTO PLAYER (ID, LAST_NAME)
VALUES (V_ID, V_LAST_NAME);
COMMIT;
END;
This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?
A. EXECUTE UPD_BAT_STAT(V_ID);
B. UPD_BAT_STAT(V_ID);
C. RUN UPD_BAT_STAT(V_ID);
D. START UPD_BAT_STAT(V_ID);
Answer: B
Oracle PL/SQL interview Questions
QUESTION 4
Which statement about triggers is true?
A. You use an application trigger to fire when a DELETE statement occurs.
B. You use a database trigger to fire when an INSERT statement occurs.
C. You use a system event trigger to fire when an UPDATE statement occurs.
D. You use INSTEAD OF trigger to fire when a SELECT statement occurs.
Answer: B
QUESTION 5 You want to create a PL/SQL block of code that calculates discounts on customer orders. -This code will be invoked from several places, but only within the program unit ORDERTOTAL. What is the most appropriate location to store the code that calculates the discounts?
A. A stored procedure on the server.
B. A block of code in a PL/SQL library.
C. A standalone procedure on the client machine.
D. A block of code in the body of the program unit ORDERTOTAL.
E. A local subprogram defined within the program unit ORDEgRTOTAL.
Answer: E
QUESTION 6
Which type of argument passes a value from a procedure to the calling environment?
A. VARCHAR2
B. BOOLEAN
C. OUT
D. IN
Answer: C
QUESTION 7
You create a DML trigger. For the timing information, which is valid with a DML trigger?
A. DURING
B. INSTEAD
C. ON SHUTDOWN
D. BEFORE
E. ON STATEMENT EXECUTION
Answer: D
QUESTION 8
You are about to change the arguments of the CALC_TEAM_AVG function. Which dictionary view can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function?
A. USER_PROC_DEPENDS
B. USER_DEPENDENCIES
C. USER_REFERENCES
D. USER_SOURCE
Answer: B
QUESTION 9
A CALL statement inside the trigger body enables you to call ______.
A. A package.
B. A stored function.
C. A stored procedure.
D. Another database trigger.
Answer: C
Incorrect Answers:
A. Package can't be called, we call a procedure inside the package.
B. We can't call a function use CALL statement because function must return a value.
D. Trigger can't be called, they are execute automatically when the trigger event occure.
QUESTION 10 You need to remove the database triggerBUSINESS_HOUR. Which command do you use to remove the trigger in the SQL *Plus environment?
A. DROP TRIGGER business_hour;
B. DELETE TRIGGER business_hour;
C. REMOVE TRIGGER business_hour;
D. ALTER TRIGGER business_hour REMOVE;
E. DELETE FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'BUSINESS_HOUR';
Answer: A
