Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Which two tasks should be created as functions instead of as procedures? (Choose two.)
A. Reference host or bind variables in a PL7SQL block of code
B. Tasks that compute and return multiple values to the calling environment
C. Tasks that compute a value that must be returned to the calling environment
D. Tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application
Answer: A,C
Explanation: Explanation/Reference:
Functions are used to return a value. Functions must return only a single value.
Procedure are used to perform an action.
Both functions and procedures are using to do a special task or action. In functions it is
must to return a single value, where as in procedures it’s not compulsory
Q2. 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
Q3. View the Exhibit and examine the structure of the EMP table.
Which stages are performed when the above block is executed? (Choose all that apply)
A. Bind
B. Parse
C. Fetch
D. Execute
Answer: B,C,D
Q4. 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
Q5. 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
Q6. View the Exhibit to examine the PL/SQL code.
The record for the employee with employee__id 100 in the employees table is as follows;
Identify the correct output for the code.
A. King 17-JUN-87 1500
B. King 17-JUN-87 24000
C. King current sysdate 1500
D. King current sysdate 24000
Answer: A
Q7. View the Exhibit and examine the structure of the AUDIR_CUST table.
Exhibit Missing
CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.
Examine the following trigger code:
Which statement is true about the above trigger?
A. It gives an error on compilation because it should be a statement-level trigger.
B. It compiles and fires successfully when the credit limit is updated in the customer table.
C. It gives an error on compilation because of the commit command in the trigger code
D. It compiles successfully, but gives an error when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.
Answer: D
Q8. Identify the scenario in which you would use the current of clause for an update or delete statement to rows fetched from a cursor.
A. when you want to lock the rows fetched by the cursor
B. when you want to update or delete the result set without affecting the rows in the table C. when you want the database not to wait if the requested rows are locked by another user
D. when you want to ensure that the current rows fetched by the cursor are updated or deleted
Answer: B
Q9. 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
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
Q11. Which statements are true about PL/SQL procedures? (Choose all that apply.)
A. Users with definer's rights who are granted access to a procedure that updates a table must be granted access to the table itself.
B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time.
C. Depending on the number of calls, multiple copies of the procedure are loaded into memory for execution by multiple users to speed up performance.
D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java.
Answer: B,D
Q12. You execute the following block of code:
Which statement is true about the outcome?
A. Both Output statements show different values.
B. Both output statements show exactly the same values.
C. It gives an error because the nested blocks are not labeled.
D. It gives an error because the V_CUSTOMER variable have different types in the nested blocks
Answer: A
Q13. 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
Q14. 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
Q15. View the exhibit and examine the structure of the products table.
Examine the following code
Which statement is true when the procedure DELETE_DETAILS is invoked?
A. It executes successfully but no error messages get recorded in the DEBUG_OUTPUT table
B. It executes successfully and any error messages get recorded in the DEBUG_OUTPUT table.
C. It gives an error because PRAGMA AUTONOMOUS_TRANSACTION can be used only in packaged procedures.
D. It gives an error because procedures containing PRAGMA AUTONOMOUS_TRANSACTION cannot be called from the exception section.
Answer: A
Explanation: In this case, the debug output will only occur if there is an exception.