Oracle PL/SQL Interview Questions
QUESTION 1
How can you migrate from a LONG to a LOB data type for a column?
A. Use the DBMS_MANAGE_LOB.MIGRATE procedure.
B. Use the UTL_MANAGE_LOB.MIGRATE procedure.
C. Use the DBMS_LOB.MIGRATE procedure.
D. Use the ALTER TABLE command.
E. You cannot migrate from a LONG to a LOB date type for a column.
Answer: D
QUESTION 2
Examine this procedure:
CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2
DEFAULT 'AUSTIN', V_NAME in VARCHAR2) IS
BEGIN
INSERT
INTO TEAM (id, city, name)
VALUES (v_id, v_city, v_name);
COMMIT;
END
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)
A. EXECUTE INSERT_TEAM;
B. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', , V_CITY=>'AUSTIN');
C.EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');
D. EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
E. EXECUTE INSERT_TEAM (3, 'LONGHORNS');
Answer: B,C
Oracle PL/SQL QUESTION 3
To be callable from a SQL expression, a user-defined function must do what?
A. Be stored only in the database.
B. Have both IN and OUT parameters.
C. Use the positional notation for parameters.
D. Return a BOOLEAN or VARCHAR2 data type.
Answer: A
QUESTION 4
Which two describe a stored procedure? (Choose two)
A. A stored procedure is typically written in SQL.
B. A stored procedure is a named PL/SQL block that can accept parameters.
C. A stored procedure is a type of PL/SQL subprogram that performs an action.
D. A stored procedure has three parts: the specification, the body, and the exception handler part.
E. The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.
Answer: B,C A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has a header, a declaration section, an executable section, and an optional exception-handling section. A procedure can be compiled and stored in the database as a schema object. Procedures promote reusability and maintainability. When validated, they can be used in any number of applications. If the requirements change, only the procedure needs to be updated.
QUESTION 5 Examine this code:
CREATE OR REPLACE PROCEDURE add_dept ( p_name departments.department_name%TYPE
DEFAULT 'unknown', p_loc departments.location_id%TYPE DEFAULT 1700) IS
BEGIN
INSERT
INTO departments(department_id, department_name, loclation_id)
VALUES(dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;/
You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus. Which four are valid invocations? (Choose four)
A. EXECUTE add_dept(p_loc=>2500)
B. EXECUTE add_dept('Education', 2500)
C. EXECUTE add_dept('2500', p_loc =>2500)
D. EXECUTE add_dept(p_name=>'Education', 2500)
E. EXECUTE add_dept(p_loc=>2500, p_name=>'Education')
Answer: A,B,C,E
Oracle PL/SQL QUESTION 6
Which three are valid ways to minimize dependency failure? (Choose three)
A. Querying with the SELECT * notification.
B. Declaring variables with the %TYPE attribute.
C. Specifying schema names when referencing objects.
D. Declaring records by using the %ROWTYPE attribute.
E. Specifying package.procedure notation while executing procedures.
Answer: A,B,D
QUESTION 7
Which two dopes the INSTEAD OF clause in a trigger identify? (Choose two)
A. The view associated with the trigger.
B. The table associated with the trigger.
C. The event associated with the trigger.
D. The package associated with the trigger.
E. The statement level or for each row association to the trigger.
Answer: A,C
QUESTION 8
Examine this package:
CREATE OR REPLACE PACKAGE manage_emps
IS
tax_rate CONSTANT NUMBER (5, 2) := .28;
v_id NUMBER;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp;
FUNCTION calc_tax (p_sal NUMBER)
RETURN NUMBER;
END manage_emps;/
CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (
p_raise_amt NUMBER) IS
BEGIN
UPDATE emp
SET sal = (sal * p_raise_emt) + sal
WHERE empno = v_id;
END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS
BEGIN
INSERT
INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal);
END insert_emp; PROCEDURE delete_emp IS
BEGIN
DELETE
FROM emp
WHERE empno = v_id;
END delete_emp; PROCEDURE update_emp
IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal
FROM emp
WHERE empno = v_id;
IF v_sal < 500
THEN v_raise := .05; ELSIP v_sal < 1000
THEN v_raise := .07;
ELSE v_raise := .04;
END
IF; update_sal(v_raise);
END update_emp;
FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal *
tax_rate;
END calc_tax;
END manage_emps;; /
What is the name of the private procedure in this package?
A. CALC_TAX
B. INSERT_EMP
C. UPDATE_SAL
D. DELETE_EMP
E. UPDATE_EMP
F. MANAGE_EMPS
Answer: C
QUESTION 9 What can you do with the DBMS_LOB package?
A. Use the DBMS_LOB.WRITE procedure to write data to a BFILE.
B. Use the DBMS_LOB.BFILENAME function to locate an external BFILE.
C. Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.
D. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.
Answer: D See next page
Incorrect Answers:
A. DBMS_LOB.WRITE is used to write to Internal LOBs. The internal LOB is stored inside the Oracle server. A BLOB, NCLOB, or CLOB can be one of the following: • An attribute of a user-defined type • A column in a table • A bind or host variable • A PL/SQL variable, parameter, or result Internal LOBs can take advantage of Oracle features such as: • Concurrency mechanisms • Redo logging and recovery mechanisms • Transactions with commit or rollbacks
B. BFILENAME is a built-in function that initializes a BFILE column to point to an external file. Use the BFILENAME function as part of an INSERT statement to initialize a BFILE column by associating it with a physical file in the server file system. You can use the UPDATE statement to change the reference target of the BFILE. A BFILE can be initialized to NULL and updated later by using the BFILENAME function.
C. DBMS_LOB.FILEEXISTS function to find if the file exits on the server
QUESTION 10
Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE
ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, , V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK IS PROCEDURE UPD_PLAYER_STAT V_ID IN
NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS
WHERE PLAYER_ID = V_ID;
COMMIT; VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME
VARCHAR2, V_SALARY NUMBER) IS
BEGIN
INSERT
INTO PLAYER(ID,LAST_NAME,SALARY)
VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK /
Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure outside the package?
A. V_PLAYER_AVG := .333;
B. BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333;
C. BB_PACK.V_PLAYER_AVG := .333;
D. This variable cannot be assigned a value from outside of the package.
Answer: D
QUESTION 1
How can you migrate from a LONG to a LOB data type for a column?
A. Use the DBMS_MANAGE_LOB.MIGRATE procedure.
B. Use the UTL_MANAGE_LOB.MIGRATE procedure.
C. Use the DBMS_LOB.MIGRATE procedure.
D. Use the ALTER TABLE command.
E. You cannot migrate from a LONG to a LOB date type for a column.
Answer: D
QUESTION 2
Examine this procedure:
CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2
DEFAULT 'AUSTIN', V_NAME in VARCHAR2) IS
BEGIN
INSERT
INTO TEAM (id, city, name)
VALUES (v_id, v_city, v_name);
COMMIT;
END
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)
A. EXECUTE INSERT_TEAM;
B. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', , V_CITY=>'AUSTIN');
C.EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');
D. EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
E. EXECUTE INSERT_TEAM (3, 'LONGHORNS');
Answer: B,C
Oracle PL/SQL QUESTION 3
To be callable from a SQL expression, a user-defined function must do what?
A. Be stored only in the database.
B. Have both IN and OUT parameters.
C. Use the positional notation for parameters.
D. Return a BOOLEAN or VARCHAR2 data type.
Answer: A
QUESTION 4
Which two describe a stored procedure? (Choose two)
A. A stored procedure is typically written in SQL.
B. A stored procedure is a named PL/SQL block that can accept parameters.
C. A stored procedure is a type of PL/SQL subprogram that performs an action.
D. A stored procedure has three parts: the specification, the body, and the exception handler part.
E. The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.
Answer: B,C A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has a header, a declaration section, an executable section, and an optional exception-handling section. A procedure can be compiled and stored in the database as a schema object. Procedures promote reusability and maintainability. When validated, they can be used in any number of applications. If the requirements change, only the procedure needs to be updated.
QUESTION 5 Examine this code:
CREATE OR REPLACE PROCEDURE add_dept ( p_name departments.department_name%TYPE
DEFAULT 'unknown', p_loc departments.location_id%TYPE DEFAULT 1700) IS
BEGIN
INSERT
INTO departments(department_id, department_name, loclation_id)
VALUES(dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;/
You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus. Which four are valid invocations? (Choose four)
A. EXECUTE add_dept(p_loc=>2500)
B. EXECUTE add_dept('Education', 2500)
C. EXECUTE add_dept('2500', p_loc =>2500)
D. EXECUTE add_dept(p_name=>'Education', 2500)
E. EXECUTE add_dept(p_loc=>2500, p_name=>'Education')
Answer: A,B,C,E
Oracle PL/SQL QUESTION 6
Which three are valid ways to minimize dependency failure? (Choose three)
A. Querying with the SELECT * notification.
B. Declaring variables with the %TYPE attribute.
C. Specifying schema names when referencing objects.
D. Declaring records by using the %ROWTYPE attribute.
E. Specifying package.procedure notation while executing procedures.
Answer: A,B,D
QUESTION 7
Which two dopes the INSTEAD OF clause in a trigger identify? (Choose two)
A. The view associated with the trigger.
B. The table associated with the trigger.
C. The event associated with the trigger.
D. The package associated with the trigger.
E. The statement level or for each row association to the trigger.
Answer: A,C
QUESTION 8
Examine this package:
CREATE OR REPLACE PACKAGE manage_emps
IS
tax_rate CONSTANT NUMBER (5, 2) := .28;
v_id NUMBER;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp;
FUNCTION calc_tax (p_sal NUMBER)
RETURN NUMBER;
END manage_emps;/
CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (
p_raise_amt NUMBER) IS
BEGIN
UPDATE emp
SET sal = (sal * p_raise_emt) + sal
WHERE empno = v_id;
END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS
BEGIN
INSERT
INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal);
END insert_emp; PROCEDURE delete_emp IS
BEGIN
DELETE
FROM emp
WHERE empno = v_id;
END delete_emp; PROCEDURE update_emp
IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal
FROM emp
WHERE empno = v_id;
IF v_sal < 500
THEN v_raise := .05; ELSIP v_sal < 1000
THEN v_raise := .07;
ELSE v_raise := .04;
END
IF; update_sal(v_raise);
END update_emp;
FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal *
tax_rate;
END calc_tax;
END manage_emps;; /
What is the name of the private procedure in this package?
A. CALC_TAX
B. INSERT_EMP
C. UPDATE_SAL
D. DELETE_EMP
E. UPDATE_EMP
F. MANAGE_EMPS
Answer: C
QUESTION 9 What can you do with the DBMS_LOB package?
A. Use the DBMS_LOB.WRITE procedure to write data to a BFILE.
B. Use the DBMS_LOB.BFILENAME function to locate an external BFILE.
C. Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.
D. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.
Answer: D See next page
Incorrect Answers:
A. DBMS_LOB.WRITE is used to write to Internal LOBs. The internal LOB is stored inside the Oracle server. A BLOB, NCLOB, or CLOB can be one of the following: • An attribute of a user-defined type • A column in a table • A bind or host variable • A PL/SQL variable, parameter, or result Internal LOBs can take advantage of Oracle features such as: • Concurrency mechanisms • Redo logging and recovery mechanisms • Transactions with commit or rollbacks
B. BFILENAME is a built-in function that initializes a BFILE column to point to an external file. Use the BFILENAME function as part of an INSERT statement to initialize a BFILE column by associating it with a physical file in the server file system. You can use the UPDATE statement to change the reference target of the BFILE. A BFILE can be initialized to NULL and updated later by using the BFILENAME function.
C. DBMS_LOB.FILEEXISTS function to find if the file exits on the server
QUESTION 10
Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE
ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, , V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK IS PROCEDURE UPD_PLAYER_STAT V_ID IN
NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS
WHERE PLAYER_ID = V_ID;
COMMIT; VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME
VARCHAR2, V_SALARY NUMBER) IS
BEGIN
INSERT
INTO PLAYER(ID,LAST_NAME,SALARY)
VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK /
Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure outside the package?
A. V_PLAYER_AVG := .333;
B. BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333;
C. BB_PACK.V_PLAYER_AVG := .333;
D. This variable cannot be assigned a value from outside of the package.
Answer: D
