Total Pageviews

June 24, 2015

6/24/2015 09:04:00 PM
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