Oracle 11g: Advanced PL/SQL
Question No: 51 – (Topic 0)
Examine the structure of the EMPLOYEES table that exists in your schema.
Name Null? Type
————- ——– ———– EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2)
You successfully create a GET_MAX procedure to find the maximum salary in the department of a specified employee.
You then code a PL/SQL block to display the maximum salary in the departments of the first five employees in the EMPLOYEES table.
View the Exhibit. Examine the procedure and the block of PL/SQL code. What is the outcome of executing the block of PL/SQL code?
It executes successfully and gives the required output.
It gives an error because ROWNUM cannot be used in cursor definitions.
It gives an error because usage of the %ROWCOUNT attribute is not valid.
It executes successfully, but does not give the required output because the procedure call resets the %ROWCOUNT value.
Question No: 52 – (Topic 0)
Identify the two types of PL/SQL programs for which you consider setting the compilation method to native mode. (Choose two.)
PL/SQL programs that are still in the debugging phase of development
PL/SQL programs that have computation-intensive procedural operations
A PL/SQL program, which is called with the same parameters by multiple sessions
PL/SQL programs that spend most of their execution time in executing SQL statements
Question No: 53 – (Topic 0)
Identify the method that is used by fine-grained access (FGA).
using policy functions to generate predicates dynamically
creating triggers on corresponding tables to generate dynamic predicates
modifying the existing application code to include a predicate for all SQL statements
creating views with necessary predicates, and then creating synonyms with the same name as the tables
Question No: 54 – (Topic 0)
Which statements are true about the SecureFile storage paradigm? (Choose two.)
SecureFile storage can be used for internal and external LOBs.
Automatic Segment Space Management must be enabled for a tablespace to store SecureFile LOBs.
SecureFile options enabled for a LOB column can be overridden on a per-LOB basis within the column.
SecureFile is the default storage paradigm for all LOBs that are stored in locally managed tablespaces if the DB_SECUREFILE parameter is set to ALWAYS.
Question No: 55 – (Topic 0)
You created an application context successfully. The user OE was granted the EXECUTE privilege on the DBMS_SESSION package. The user receives this error while setting the value for an attribute within the context:
SQLgt; EXECUTE DBMS_SESSION.SET_CONTEXT(#39;SALES_ORDERS_CTX#39;,#39;ACCOUNT_MGR#39;,#39;OE#39;);
BEGIN DBMS_SESSION.SET_CONTEXT(#39;SALES_ORDERS_CTX#39;,#39;ACCOUNT_MGR#39;,#39;OE#39;); END;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at quot;SYS.DBMS_SESSIONquot;, line 94
ORA-06512: at line 1
What is the reason for this error?
The context was created with a package name in the USING clause.
The attribute can be set only in the package associated with the context.
The package associated with the context did not exist at the time of creation of the context.
The value for an attribute of a user-defined context can be set only by the ALTER SESSION command.
Question No: 56 – (Topic 0)
To examine the dependencies between all PL/SQL objects and the tables and views they reference, you executed the following query as the user OE:
SQLgt; SELECT owner || #39;.#39; || NAME refs_table
, referenced_owner || #39;.#39; || referenced_name AS table_referenced FROM all_dependencies
WHERE owner = USER
AND TYPE IN (#39;PACKAGE#39;, #39;PACKAGE BODY#39;,#39;PROCEDURE#39;, #39;FUNCTION#39;)
AND referenced_type IN (#39;TABLE#39;, #39;VIEW#39;)
AND referenced_owner NOT IN (#39;SYS#39;, #39;SYSTEM#39;)
ORDER BY owner, NAME, referenced_owner, referenced_name; Which statement is true about the output of the query?
It displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM..
It displays no rows because this query needs to be executed as the user SYS for required results.
It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.
It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.
Question No: 57 – (Topic 0)
You set RESULT_CACHE_MAX_SIZE to a nonzero value to enable result caching. You executed the following command to check the status for the result cache:,
SQLgt; select dbms_result_cache.status() from dual; You receive the following output: DBMS_RESULT_CACHE.STATUS()
Identify the reason for the output.
The RESULT_CACHE_MODE parameter is set to FORCE.
The RESULT_CACHE_MODE parameter is set to MANUAL.
The database instance is not able to allocate memory for the result cache.
The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.
Question No: 58 – (Topic 0)
You execute the following command in the user session:
SQLgt; ALTER SESSION SET PLSQL_DEBUG=true;
Which statement is true about the effect of the command?
All PL/SQL blocks that are executed subsequently in the session are traced.
It enables all PL/SQL blocks that are compiled subsequently in the session for tracing.
Only anonymous PL/SQL blocks that are executed subsequently in the session are traced.
It enables only named PL/SQL blocks that are executed subsequently in the session for tracing.
Question No: 59 – (Topic 0)
Identify two strategies against SQL injection. (Choose two.)
Using parameterized queries with bind arguments.
Use subprograms that are run with the definer#39;s right.
Use RESTRICT_REFERENCE clauses in functions that use dynamic SQLs.
Validate user inputs to functions that use dynamic SQLs built with concatenated values.
Question No: 60 – (Topic 0)
A procedure is created in the SYS schema to allow users to change the password as follows:
CREATE OR REPLACE
PROCEDURE change_password(p_username VARCHAR2 DEFAULT NULL, p_new_password VARCHAR2 DEFAULT NULL)
v_sql_stmt VARCHAR2(500); BEGIN
v_sql_stmt := #39;ALTER USER #39;||p_username ||#39; IDENTIFIED BY #39;
|| p_new_password; EXECUTE IMMEDIATE v_sql_stmt; END change_password;
The SYS user has granted EXECUTE privilege on the procedure to the OE user. But OE is able to change the password for SYS by using this procedure. How would you protect this?
by using the procedure as part of a PL/SQL package
by using a bind argument with dynamic SQL in the procedure
by using AUTHID DEFINER in the procedure to implement the definer#39;s right
by using AUTHID CURRENT_USER in the procedure to implement the invoker#39;s right
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|