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);
CREATE OR REPLACE PACKAGE BODY BB_PACK
PROCEDURE UPD_PLAYER_STAT (V_ID IN NUMBER,
V_AB IN NUMBER DEFAULT 4 ,
V_HITS IN NUMBER)
SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS
WHERE PLAYER_ID = V_ID;
PROCEDURE ADD_PLAYER (V_ID IN NUMBER,
INSERT INTO PLAYER (ID, LAST_NAME, SALARY)
VALUES (V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT (V_ID, 0, 0);
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.
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)
Answer: D, E
Given a function CALCTAX:
CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN
(sal * 0.05);
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);
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.
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.
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.
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
Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2,
v_email_name VARCHAR2 (19);
SUBSTR (p_first_name, 1, 1)
|| SUBSTR (p_last_name, 1, 7)
SET email = v_email_name
WHERE employee_id = p_id;
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?
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
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'
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.
As you can see there is nothing called BEFORE LOGON