Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Which two guidelines are recommended by Oracle to reduce invalidation of dependent objects? (Choose two.)
A. Reference tables indirectly by using views.
B. Reference tables directly avoid using views.
C. When adding new items to a package, add them to the end of the package.
D. When adding new items to a package, add them to the beginning of the package.
Answer: A,C
Q2. Identify two situations where the DBMS_SQL package should be used. (Choose two.)
A. The SELECT list is not known until run time.
B. The dynamic SQL statement retrieves rows into records.
C. You do not know how many columns a select statement will return, or what their data types will.
D. You must use the %found SQL cursor attribute after issuing a dynamic SQL statement that is an insert or update statement.
Answer: A,C
Q3. View Exhibit1 and examine the structure of the EMP table.
View Exhibit2 and examine the PIVSQL block of code.
What is the outcome?
A. It gives an error because the return type is not valid.
B. It gives an error because the record type is not defined within the function
C. It gives an error because the function call in DBMS_OUTPUT. PUT__LINE is not valid
D. It executes successfully and displays the names and salaries of all employees who earn the highest salary.
E. It executes successfully but does not display the names and salaries of all employees who earn the highest salary.
Answer: D
Q4. View Exhibit1 and examine the structure of the employees table.
View Exhibit2 and examine the code.
What would be the outcome when the code is executed?
A. It executes successfully.
B. It gives an error because the SAL variable is not visible in the increase function.
C. It gives an error because the increase function cannot be called from the RAISE_SALARY procedure.
D. It gives an error because the increase function and the RAISE_SALARY procedure should be declared at the beginning of the declare section before all the other declarations.
Answer: A
Q5. Which two statements are true about triggers? (Choose two.)
A. All the triggers that are created on a table cannot be disabled simultaneously.
B. Any user who has the alter privilege on a table can create a trigger using that table.
C. Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database.
D. Triggers become invalid if a dependent object, such as 3 stored subprogram that is invoked from the trigger body is modified, and have to be manually recompiled before the next invocation.
Answer: C,D
Q6. Which type of exceptions is qualified as no predefined Oracle server errors?
A. the exceptions that are explicitly raised by the program and can be caught by the exception handler
B. the exceptions that are raised implicitly by the Oracle server and can be caught by the exception handler
C. an exception that the developer determines as abnormal, are in the declarative section and raised explicitly
D. an exception that is raised automatically when the PL/SQL program violates a database rule or exceeds a system-dependent limit
Answer: C
Q7. View Exhibit1 and examine the structure of the EMP table.
View Exhibit2 and examine the code.
EKPNOS 7845 and 7900 exist in the EMP table.
Which two calls to the RAISE_SALABY procedure in the anonymous block execute successfully? (Choose two.)
A. call in line 6
B. call in line 7
C. call in line 8
D. call in line 9
Answer: C,D
Q8. Which three statements are true about anonymous blocks and subprograms? (Choose three.)
A. Only subprograms can be parameterized.
B. Only subprograms are persistent database objects.
C. Both anonymous blocks and subprograms can be parameterized.
D. Both anonymous blocks and subprograms are persistent database objects
E. Only subprograms can return values that persist after the execution of the subprogram.
F. Both anonymous blocks and subprograms can return values that persist In SQL*Plus variables after their execution.
Answer: B,E,F
Q9. View the Exhibit to examine the PL/SQL code.
SERVEROUTPUT is on for the session.
Which statement is true about the execution of the code?
A. The execution fails because of the misplaced else clause.
B. The execution is successful even if there is no employee with EMPLOYEE_ID 115.
C. The execution falls and throws exceptions if no employee with EMPLOYEE_ID us is found.
D. The execution is successful, but it displays an incorrect output if no employee with EMPLOYEE_ID 115 is found.
Answer: C
Q10. Examine the following package specification.
SQL>CREATE OR REPLACE PACKAGE emp_pkf IS
PROCEDURE search_emp (empdet NUMBER);
PROCEDURE search_emp (empdet DATE);
PROCEDURE search_emp (empdet NUMBER); RETURN VERCHAR2
PROCEDURE search_emp (empdet NUMBER); RETURN DATE
END emp_pkg
/
The package is compiled successfully
Why would it generate an error at run tune?
A. Because function cannot be overload
B. Because function cannot differ only in return type.
C. Because all the functions and procedures In the package cannot have the same number of parameters with the same parameter name
D. Because the search EMP (EMPDET NUMBER) procedure and the SEARCH_DEPT (EMPDET NUMBER) cannot have identical parameter names and data types
Answer: B
Q11. View the Exhibit and examine the structure of the SALGRADE table.
Examine the following code:
What is the outcome?
A. It is created successfully.
B. It gives an error because the return clause condition is invalid.
C. It gives an error because the usage of the host variables is invalid.
D. It gives an error because the data type of the return clause is invalid.
Answer: B
Q12. Examine the following code:
Which two statements are true about the above function? (Choose two.)
A. It can be used only in a parallelized query.
B. It can be used in both a parallelized query and a parallelized DML statement.
C. It can be used only in a parallelized data manipulation language (DML) statement.
D. It can have a separate copy run in each of the multiple processes when called from a SQL statement that is run in parallel.
E. It requires a PRAGMA RESTRICT_REFERENCES declaration with RNDS, WNDS, RNPS, and WNPS specified in order to use parallel optimization.
Answer: B,E
Q13. You want to store values of different data types in a PL/SQL block and store one record at a time for processing the information.
Which type of composite data type would you choose to fulfill the requirement?
A. VARRAYS
B. Nested table
C. PL/SQL records
D. Associative arrays
Answer: C
Q14. View the exhibit and examine the structure of the EMPLOYEE table.
EMPLOYEE_SEQ is an existing sequence.
Examine the following block of code:
Which statement is true about the above block of code?
A. It consists of two transactions
B. It consists of a single transaction,
C. The data is automatically committed after the block execution ends,
D. It gives an error on execution because sequences cannot be used in anonymous blocks.
Answer: A
Q15. Which two statements are true about the %ROWTYPE attribute? (Choose two.)
A. It is used to declare a record that can hold multiple rows of a table.
B. The attributes of fields in the record with the %ROWTYPE attribute can be modified manually.
C. The attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variable.
D. It ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altered.
Answer: C,D