Q1. Examine the following code:
Which statement is true about the execution of the above code?
A. It executes and displays null.
B. It executes and the condition returns true.
C. It executes and control goes to the else statement.
D. It fails because no value is assigned to the v_myage variable.
Answer: C
Q2. 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
Q3. Examine the following snippet of PL/SQL code:
View the exhibit for table description of EMPLOYEES table. The EMPLOYEES table has 200 rows.
Identify open statement for opening the cursor that fetches the result as consisting of employees with JOB_ID as ‘ST_CLERK’ and salary greater than 3000.
A. OPEN c1 (NULL, 3000);
B. OPEN c1 (emp_job, 3000);
C. OPEN c1 (3000, emp_salary);
D. OPEN c1 (‘ST_CLERK’, 3000)
E. OPEN c1 (EMP_job, emp_salary);
Answer: D
Q4. In which of the following scenarios would you recommend using PL/SQL records?
A. when you want to retrieve an entire row from a table and perform calculations
B. when you know the number of elements in advance and the elements are usually accessed sequentially
C. when you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries
D. when you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked
Answer: C,D
Q5. Examine the following code that you plan to execute:
What correction should be performed in the above code?
A. The PROC2 procedure code should be defined in the package body.
B. The PROC3 procedure should be declared in the package specification.
C. The PROC3 procedure header should be declared at the beginning of the package body.
D. The variable x must be declared in the package body and removed from the specification,
Answer: A,B
Q6. View the Exhibit and examine the structure of the employees table.
Execute the following block of code:
What is the outcome?
A. It gives an error because group functions cannot be used in anonymous blocks
B. It executes successfully and correctly gives the result of the sum of salaries in
department 60.
C. It executes successfully and incorrectly gives the result of the sum of salaries in department 60.
D. It gives an error because the variable name and column name are the same in the where clause of the select statement.
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. 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
Q9. User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database.
SCOTT creates the following trigger:
CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object');
END:
SCOTT does not grant the execute privilege on this trigger to any other users.
For which user(s) would this trigger fire by default when they drop an object in the hr schema?
A. Only HR
B. SCOTT and HR
C. Only SCOTT
D. SCOTT, HR, and SYS
Answer: A
Q10. 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