Oracle 11g: Advanced PL/SQL
Question No: 31 – (Topic 0)
Examine the following error:
v_runid NUMBER; BEGIN
v_runid := DBMS_HPROF.ANALYZE (LOCATION =gt; #39;PROFILE_DATA#39;,
FILENAME =gt; #39;pd_cc_pkg.txt#39;); DBMS_OUTPUT.PUT_LINE(#39;Run ID: #39; || v_runid);
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at quot;SYS.DBMS_HPROFquot;, line 299
ORA-06512: at line 4
What would you do to execute the above block successfully?
Start the PL/SQL profiler before executing the block.
Run the tracetab.sql script located at ORACLE_HOME\RDBMS\ADMIN.
Run the dbmshptab.sql script located at ORACLE_HOME\RDBMS\ADMIN.
Grant READ and WRITE privileges to the current user on the PROFILE_DATA directory object.
Question No: 32 – (Topic 0)
Which two statements are true about SecureFile LOB options? (Choose two.)
The DECRYPT option can be used to remove the encryption only if the LOB column is empty.
The KEEP_DUPLICATES option removes the deduplication effect only on new data in a LOB column.
The KEEP_DUPLICATES option removes the deduplication effect on existing and new data in a LOB column.
The DECRYPT option can be used to remove the encryption from LOB columns that are empty or contain data.
Question No: 33 – (Topic 0)
View the Exhibit and examine the procedure to create a trigger name based on the table name supplied to the procedure.
Which three statements are appropriate for protecting the code in the procedure from SQL injection? (Choose three.)
Explicitly validate the identifier length limit.
Add AUTHID DEFINER to the definition of the procedure.
Use PRAGMA RESTRICT_REFERENCES in the procedure.
Filter out control characters in user-supplied identifier names.
Use the object ID of the table from the data dictionary to build the trigger name.
Question No: 34 – (Topic 0)
You created the SALES_ORDERS_CTX context to use the OE.SALES_ORDERS_PKG package.
View Exhibit1 and examine the package that is used with the context. View Exhibit2 to examine the policy defined and the logon trigger.
A user receives the following error when he or she executes a query:
ERROR at line 2:
ORA-28112: failed to execute policy function What could be the reason for the error?
The user has insufficient privileges on the DBMS_SESSION package.
The subprograms inside the package have not been created with the invoker#39;s right.
The THE_PREDICATE function has an insufficient number of parameters in the package.
The policy is created by using SALES_ORDERS_PKG.THE_PREDICATE without a parameter.
Question No: 35 – (Topic 0)
Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block.
CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,
deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),
deptname VARCHAR2(30) DEFAULT #39;Sales#39;)
SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE; v_emprec v_emprec_subtype;
v_emprec.empid := NULL; v_emprec.salary := 10000.002; v_emprec.deptid := 50;
DBMS_OUTPUT.PUT_LINE(#39;v_emprec.deptname: #39; || v_emprec.deptname); END;
Which statements are true about the above PL/SQL block? (Choose two.)
V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.
Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is inherited.
Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of the decimal.
Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.
Question No: 36 – (Topic 0)
Identify two factors that you consider for compiling a PL/SQL program unit for interpreted mode. (Choose two.)
a PL/SQL program which needs to be recompiled frequently
a PL/SQL program that spends most of the time executing SQL
a PL/SQL program, which performs computation-intensive procedural operations and is recompiled rarely
a PL/SQL program, which is called frequently with the same parameter values by multiple sessions and is recompiled rarely
Question No: 37 – (Topic 0)
Which two statements are true about REF CURSOR types? (Choose two.)
REF CURSOR types cannot be defined inside a package.
SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a user- defined record.
Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function.
Question No: 38 – (Topic 0)
The result cache is enabled for the database instance. Examine the following code for a PL/SQL function:
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR
RESULT_CACHE RELIES_ON (HR.EMPLOYEES) IS
date_hired DATE; BEGIN
SELECT hire_date INTO date_hired FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
RETURN TO_CHAR(date_hired); END;
You notice that results for the functions are not used effectively. What do you recommend for better utilization of the result cache? (Choose all that apply.)
Set the RESULT_CACHE_MODE parameter to FORCE.
Increase the value for the RESULT_CACHE_MAX_SIZE parameter.
Add a format mask parameter, such as RETURN TO_CHAR(date_hired, fmt) to GET_HIRE_DATE.
Change the return type of GET_HIRE_DATE to DATE and have each session invoke the TO_CHAR function.
Question No: 39 – (Topic 0)
You designed a CardValidation.java Java source file. You also have the corresponding CardValidation.class file. As part of invoking a Java class method, you executed this command at the command prompt:
loadjava -user oe/oe CardValidation.java Which statement is true about the command?
It loads the Java code into the database.
It publishes Java methods in CardValidation.java.
It loads the metadata related to the Java class file into the database.
It loads the Java class file into the Java pool in the database instance.
Question No: 40 – (Topic 0)
Which two statements are true about the query results stored in the query result cache? (Choose two.)
If any of the tables used to build a query is modified by an ongoing transaction in the current session, the query result is not cached.
A query result based on a read-consistent snapshot of data that is older than the latest committed version of the data is not cached.
Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.
A query result for a query that has a bind variable is stored in the cache and is reused if the query is equivalent even when the bind variable has a different value.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|