Q1. Examine the following PL/SQL code:
The server output is on for the session. Which statement is true about the execution of the code?
A. The code executes successfully and gives the desired output.
B. The code generates an error because the EMP_RECORD variable is not declared.
C. The code generates an error because the cursor is not opened before the FOR loop.
D. The code generates an error because the loop does not have the exit when clause.
Answer: A
Q2. View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.
EMPLOYEES
DEPAERTMENT
EMP_BY_DEPT
Examine the following code:
What is the outcome on execution of the above code?
A. It executes successfully but the output statements show different values.
B. It executes successfully and both output statements show the same values.
C. It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT.
D. It gives an error because the INSERT SELECT construct cannot be used with the FORALL
Answer: A
Q3. Which tasks must be performed during the installation of the UTL_MAIL package? (Choose all that apply.)
A. setting the UTL_FILE_DIR initialization parameter
B. running the UTLMAIL.SQL and prvtmail.plb scripts
C. setting the SMTP_OUT_SERVER initialization parameter
D. using the CREATE DIRECTORY statement to associate an alias with an operating system directory
E. granting read and WRITE privileges to control the type of access to files in the operating system
Answer: B,C
Q4. You want to maintain an audit of the date and time when each user of the database logs off.
Examine the following code:
Which two clauses should be used to fill in the blanks and complete the above code? (Choose two.)
A. ON SCHEMA
B. ON QRXABASE
C. AFTER LOGOFF
D. BEFORE LOGOFF
Answer: A,D
Q5. Examine the following partial declare section from a block of PL/SQL code
Which line(s) in the above code are NOT valid? (Choose all that apply.)
A. line 2
B. line 3
C. line 4
D. line 5
Answer: B,D
Q6. Which two statements are correct about PL/SQL package components? (Choose two)
A. A package must have both specification and body.
B. A package body can exist without the package specification.
C. A package specification can exist without the package body.
D. When a packaged public variable is called for the first time in a session, the entire package is loaded into memory.
Answer: C,D
Q7. Examine the following code:
What is the outcome?
A. The procedure is created successfully and displays the values 20 and 30 when it is called.
B. The procedure gives errors because the parameters should be in out mode.
C. The procedure gives errors because the host variables cannot be referenced anywhere in the definition of a PL/SQL stored procedure.
D. The procedure is created successfully but does not display any values when it is called because the host variables cannot be displayed inside the procedure.
Answer: C
Q8. View the Exhibit and examine the structure of the EMP table.
You want to create two procedures using the overloading feature to search for employee details based on either the employee name or employee number.
Which two rules should you apply to ensure that the overloading feature is used successfully? (Choose two.)
A. The procedures can be either stand-alone or packaged.
B. The procedures should be created only as packaged subprograms
C. The procedures should be created only as stand-alone subprograms
D. Each subprogram's formal parameters should differ in both name and data type.
E. The formal parameters of each subprogram should differ in data type but can use the same names.
Answer: B,E
Q9. View the Exhibit and examine the code and its outcome on execution:
What would be the effect on the two procedures if the value of debug is set to false? (Choose two.)
A. MY_PROC2 is not recompiled.
B. MY_PROC1 is recompiled but remains unchanged.
C. MY_PROC2 is recompiled but remains unchanged.
D. MY_PROC1 is recompiled without the debugging code.
Answer: A,D
Q10. View Exhibit 1 and examine the structure of the EMP and dept tables.
View Exhibit2 and examine the trigger code that is defined on the dept table to enforce the update and delete restrict referential actions on the primary key of the dept table.
What is the outcome on compilation?
A. It compiles and executes successfully.
B. It gives an error on compilation because it is not a row-level trigger.
C. It gives an error on compilation because the exception section Is used in the trigger.
D. It compiles successfully but gives an error on execution because it is not a row-level trigger.
Answer: B