Oracle PL/SQL Interview Questions
QUESTION 1
Examine this code:
CREATE OR REPLACE PACKAGE comm_package
IS
g_comm NUMBER := 10;
PROCEDURE reset_comm (p_comm IN NUMBER);
END comm_package;
/
User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?
A. g_comm has a value of 15 at 9:06am for Smith.
B. g_comm has a value of 15 at 9:06am for Jones.
C. g_comm has a value of 20 at 9:06am for both Jones and Smith.
D. g_comm has a value of 15 at 9:03 am for both Jones and Smith.
E. g_comm has a value of 10 at 9:06am for both Jones and Smith.
F. g_comm has a value of 10 at 9:03am for both Jones and Smith
Answer: B
QUESTION 2 Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name
VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS
v_email_name VARCHAR2(19)
BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name,1
,7) || '@Oracle.com';
UPDATE employees
SET email = v_email_name
WHERE employee_id = p_id; RETURN v_email_name;
END;
/
Which statement removes the function?
A. DROP FUNCTION gen_email_name;
B. REMOVE gen_email_name;
C. DELETE gen_email_name;
D.Truncate gen_email _name;
E .DROP FUNCTION gen_email_name;
F.ALTER FUNCTION gen_email_name;REMOVE
Answer: E
QUESTION 3 Examine this procedure:
CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10 ,
V_AB IN NUMBER DEFAULT 4 )
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB
WHERE PLAYER_ID = V_ID;
COMMIT;
END;
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)
A. EXECUTE UPD_BAT_STAT;
B. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);
C. EXECUTE UPD_BAT_STAT(31, 'FOUR','TWO');
D. UPD_BAT_STAT(V_AB=>10, V_ID=>31);
E. RUN UPD_BAT_STAT;
Answer: A,B
QUESTION 4 Examine this code:
CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGININSERT INTO
audit(schema_user)
VALUES(p_who);
END audit_action;; /
CREATE OR REPLACE TRIGGER watch_it AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) / What does this trigger do?
A. The trigger records an audit trail when a user makes changes to the database.
B. The trigger marks the user as logged on to the database before an audit statement is issued. C. The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table.
D. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table.
Answer: D
QUESTION 5
Which view displays indirect dependencies, indenting each dependency?
A. DEPTREE
B. IDEPTREE
C. INDENT_TREE
D. I_DEPT_TREE
Answer: B
QUESTION 6
The OLD and NEW qualifiers can be used in which type of trigger?
A. Row level DML trigger
B. Row level system trigger
C. Statement level DML trigger
D. Row level application trigger
E. Statement level system trigger
F. Statement level application trigger
Answer: A
QUESTION 7
Which statement is true?
A. Stored functions can be called from the SELECT and WHERE clauses only.
B. Stored functions do not permit calculations that involve database links in a distributed environment.
C. Stored functions cannot manipulate new types of data, such as longitude and latitude.
D. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.
Answer: D
Oracle PL/SQL Interview Questions
QUESTION 8
Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW
DELCARE n INTEGER;
BEGIN
SELECT COUNT ( * ) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE (' There are now ' || a || ' employees,');
END;
This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error?
A. Change the trigger type to a BEFORE DELETE.
B. Take out the COUNT function because it is not allowed in a trigger.
C. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
D. Change the trigger to a statement-level trigger by removing FOR EACH ROW.
Answer: D
QUESTION 9
What is true about stored procedures?
A. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.
B. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.
C. A stored procedure must have at least one executable statement in the procedure body.
D. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.
Answer: C
QUESTION 10
Examine this code:
CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id
NUMBER(4);
BEGIN
INSERT
INTO departments
VALUES (5, 'Education', 150, p_location_id)
SELECT department_id
INTO v_dept_id
FROM employees
WHERE employee_id=99999;
END insert_dept;
/
You just created the departments, the locations, and the employees table. You did
- The Power of Knowing
not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command: EXECUTE insert_location (19, 'San Francisco') What is the result in thisEXECUTE command?
A. The locations, departments, and employees tables are empty.
B. The departments table has one row. The locations and the employees tables are empty.
C. The location table has one row. The departments and the employees tables are empty.
D. The locations table and the departments table both have one row. The employees table is empty.
Answer: A
QUESTION 1
Examine this code:
CREATE OR REPLACE PACKAGE comm_package
IS
g_comm NUMBER := 10;
PROCEDURE reset_comm (p_comm IN NUMBER);
END comm_package;
/
User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?
A. g_comm has a value of 15 at 9:06am for Smith.
B. g_comm has a value of 15 at 9:06am for Jones.
C. g_comm has a value of 20 at 9:06am for both Jones and Smith.
D. g_comm has a value of 15 at 9:03 am for both Jones and Smith.
E. g_comm has a value of 10 at 9:06am for both Jones and Smith.
F. g_comm has a value of 10 at 9:03am for both Jones and Smith
Answer: B
QUESTION 2 Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name
VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS
v_email_name VARCHAR2(19)
BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name,1
,7) || '@Oracle.com';
UPDATE employees
SET email = v_email_name
WHERE employee_id = p_id; RETURN v_email_name;
END;
/
Which statement removes the function?
A. DROP FUNCTION gen_email_name;
B. REMOVE gen_email_name;
C. DELETE gen_email_name;
D.Truncate gen_email _name;
E .DROP FUNCTION gen_email_name;
F.ALTER FUNCTION gen_email_name;REMOVE
Answer: E
QUESTION 3 Examine this procedure:
CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10 ,
V_AB IN NUMBER DEFAULT 4 )
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB
WHERE PLAYER_ID = V_ID;
COMMIT;
END;
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)
A. EXECUTE UPD_BAT_STAT;
B. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);
C. EXECUTE UPD_BAT_STAT(31, 'FOUR','TWO');
D. UPD_BAT_STAT(V_AB=>10, V_ID=>31);
E. RUN UPD_BAT_STAT;
Answer: A,B
QUESTION 4 Examine this code:
CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGININSERT INTO
audit(schema_user)
VALUES(p_who);
END audit_action;; /
CREATE OR REPLACE TRIGGER watch_it AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) / What does this trigger do?
A. The trigger records an audit trail when a user makes changes to the database.
B. The trigger marks the user as logged on to the database before an audit statement is issued. C. The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table.
D. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table.
Answer: D
QUESTION 5
Which view displays indirect dependencies, indenting each dependency?
A. DEPTREE
B. IDEPTREE
C. INDENT_TREE
D. I_DEPT_TREE
Answer: B
QUESTION 6
The OLD and NEW qualifiers can be used in which type of trigger?
A. Row level DML trigger
B. Row level system trigger
C. Statement level DML trigger
D. Row level application trigger
E. Statement level system trigger
F. Statement level application trigger
Answer: A
QUESTION 7
Which statement is true?
A. Stored functions can be called from the SELECT and WHERE clauses only.
B. Stored functions do not permit calculations that involve database links in a distributed environment.
C. Stored functions cannot manipulate new types of data, such as longitude and latitude.
D. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.
Answer: D
Oracle PL/SQL Interview Questions
QUESTION 8
Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW
DELCARE n INTEGER;
BEGIN
SELECT COUNT ( * ) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE (' There are now ' || a || ' employees,');
END;
This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error?
A. Change the trigger type to a BEFORE DELETE.
B. Take out the COUNT function because it is not allowed in a trigger.
C. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
D. Change the trigger to a statement-level trigger by removing FOR EACH ROW.
Answer: D
QUESTION 9
What is true about stored procedures?
A. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.
B. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.
C. A stored procedure must have at least one executable statement in the procedure body.
D. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.
Answer: C
QUESTION 10
Examine this code:
CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id
NUMBER(4);
BEGIN
INSERT
INTO departments
VALUES (5, 'Education', 150, p_location_id)
SELECT department_id
INTO v_dept_id
FROM employees
WHERE employee_id=99999;
END insert_dept;
/
You just created the departments, the locations, and the employees table. You did
- The Power of Knowing
not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command: EXECUTE insert_location (19, 'San Francisco') What is the result in thisEXECUTE command?
A. The locations, departments, and employees tables are empty.
B. The departments table has one row. The locations and the employees tables are empty.
C. The location table has one row. The departments and the employees tables are empty.
D. The locations table and the departments table both have one row. The employees table is empty.
Answer: A
