Oracle PL/SQL
interview questions
QUESTION 1 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, 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;
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;
You make a change to the body of the BB_PACK
package. The BB_PACK body is recompiled. What happens if the stand alone
procedure VALIDATE_PLAYER_STAT references this package?
A. VALIDATE_PLAYER_STAT cannot recompile and
must be recreated.
B. VALIDATE_PLAYER_STAT is not invalidated.
C. VALDIATE_PLAYER_STAT is invalidated.
D. VALIDATE_PLAYER_STAT and BB_PACK are
invalidated.
Answer: B
You can greatly simplify dependency management with packages when referencing a
package procedure or function from a stand-alone procedure or function. • If
the package body changes and the package specification does not change, the
stand-alone procedure referencing a package construct remains valid. • If the
package specification changes, the outside procedure referencing a package
construct is invalidated, as is the package body.
QUESTION 2
You need to create a trigger on the
EMP table that monitors every row that is changed and places this information
into the AUDIT_TABLE. What type of trigger do you create?
A. FOR EACH ROW trigger on the EMP table.
B. Statement-level trigger on the EMP
table.
C. FOR EACH ROW trigger on the AUDIT_TABLE
table.
D. Statement-level trigger on the AUDIT_TABLE
table.
E. FOR EACH ROW statement-level trigger on the
EMPtable.
Answer: A
FOR EACH ROW trigger on the updated table(emp) should be create to record each
update row in the AUDIT_TABLE.
QUESTION 3 Which statements are true? (Choose
all that apply)
A. If errors occur during the compilation of a
trigger, the trigger is still created.
B. If errors occur during the compilation
of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data
dictionary view to see the compilation errors.
C. If errors occur during the compilation
of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the
compilation errors.
D. If
errors occur during the compilation of a trigger you can go into SQL *Plus and
query the USER_ERRORS data dictionary view to see compilation errors.
Answer:
A, C, D
QUESTION 4 Which two dictionary views track
dependencies? (Choose two)
A. USER_SOURCE
B. UTL_DEPTREE
B. UTL_DEPTREE
C. USER_OBJECTS
D. DEPTREE_TEMPTAB
E. USER_DEPENDENCIES
F.
DBA_DEPENDENT_OBJECTS
Answer:
D, E
QUESTION 5
Given a function CALCTAX:
CREATE OR REPLACE FUNCTION calctax (sal NUMBER)
RETURN NUMBER IS BEGIN RETURN
(sal * 0.05);
END
;
If you want to run the above function from
the SQL *Plus prompt, which statement is true?
A. You need to execute the command
CALCTAX(1000);.
B. You need to execute the command EXECUTE
FUNCTION calctax;
C. You need to create a SQL *Plus environment
variable X and issue the command :X := CALCTAX(1000);.
D. You need to create a SQL *Plus environment
variable X and issue the command EXECUTE :X := CALCTAX;
E. You need to create a SQL *Plus environment
variable X and issue the command EXECUTE :X := CALCTAX(1000);
Answer: E
When you
call a function from SQL*PLUS you need to assign the returned value a bind
variable, and you need the EXECUTE command to execute the function.
QUESTION 6
What happens during the execute phase with
dynamic SQL for INSERT, UPDATE, and DELETE operations?
A. The rows are selected and ordered.
B. The validity of the SQL statement is
established.
C. An area of memory is established to process
the SQL statement.
D. The SQL statement is run and the number of
rows processed is returned.
E. The area of memory established to process the
SQL statement is released.
Answer: D
All SQL
statements have to go through various stages. Some stages may be skipped.
1. Parse
Every SQL statement must be parsed. Parsing the statement includes checking the
statement's syntax and validating the statement, ensuring that all references
to objects are correct, and ensuring that the relevant privileges to those
objects exist.
2. Bind
After parsing, the Oracle server knows the meaning of the Oracle statement but
still may not have enough information to execute the statement. The Oracle
server may need values for any bind variable in the statement. The process of
obtaining these values is called binding variables.
3.
Execute At this point, the Oracle server has all necessary information and
resources, and the statement is executed.
4. Fetch
In the fetch stage, rows are selected and ordered (if requested by the query),
and each successive fetch retrieves another row of the result, until the last
row has been fetched. You can fetch queries, but not the DML statements.
QUESTION 7
What part of a database trigger determines the
number of times the trigger body executes?
A. Trigger type
B. Trigger body
C. Trigger event
D. Trigger timing
Answer:
A
QUESTION 8
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_home :=
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;
You run this SELECT statement: SELECT
first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM
employees; What occurs?
A. Employee 108 has his email name updated based
on the return result of the function.
B. The statement fails because functions called
from SQL expressions cannot perform DML.
C. The statement fails because the functions
does not contain code to end the transaction.
D. The SQL statement executes successfully,
because UPDATE and DELETE statements are ignoring in stored functions called
from SQL expressions.
E. The SQL statement executes successfully and
control is passed to the calling environment.
Answer: B
• When called from a SELECT statement or a parallelized UPDATE or DELETE
statement, the function cannot modify any database tables
• When
called from an UPDATE, or DELETE statement, the function cannot query or modify
any database tables modified by that statement.
• When
called from a SELECT, INSERT, UPDATE, or DELETE statement, the function
cannot execute SQL transaction control statements (such as COMMIT), session
control statements (such as SET ROLE), or system control statements (such as
ALTER SYSTEM). Also, it cannot execute DDL statements (such as CREATE) because
they are followed by an automatic commit. • The function cannot call another
subprogram that breaks one of the above restrictions.
QUESTION 9 Which table should you query to
determine when your procedure was last compiled?
A. USER_PROCEDURES
B. USER_PROCS
C. USER_OBJECTS
D. USER_PLSQL_UNITS
Answer: C In the USER_OBJECTS
there is Incorrect Answers
A.
USER_PROCEDURES lists all functions and procedures, along with associated
properties. For example, ALL_PROCEDURES indicates whether or not a function is
pipelined, parallel enabled or an aggregate function. If a function is
pipelined or an aggregate function, the associated implementation type (if any)
is also identified. It doesn't have when the object was last complied.
B. There
is nothing called USER_PROCS.
D. There
is nothing called USER_PLSQL_UNITS
QUESTION 10
Examine
this code:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE LOGON ON employees BEGIN IF (
TO_CHAR(SYSDATE,
'DY') IN ('SAT', 'SUN')) OR (TO_CHAR(SYSDATE, 'HH24:MI'
) NOT BETWEEN
'08:00'
AND '18:00')
THEN
RAISE_APPLICATION_ERROR (-20500,
'You may insert
into the EMPLOYEES table only during END IF'); 1z0-147 END;
/
What type of trigger is it?
A. DML trigger
B. INSTEAD OF trigger
C. Application trigger
D. System event trigger
E. This is an invalid trigger.
Answer:
E
As you
can see there is nothing called BEFORE LOGON
