1Z0-148 Online Practice Questions and Answers

Examine the following error:

SQL> DECLARE

v_runid NUMBER;

BEGIN

v_runid := DBMS_HPROF.ANALYZE (LOCATION => 'PROFILE_DATA',

FILENAME => 'pd_cc_pkg.txt');

DBMS_OUTPUT.PUT_LINE('Run ID: ' || v_runid);

END;

DECLARE

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_HPROF", line 299

ORA-06512: at line 4

What would you do to execute the above block successfully?

A. Start the PL/SQL profiler before executing the block.

B. Run the tracetab.sql script located at ORACLE_HOME\RDBMS\ADMIN.

C. Run the dbmshptab.sql script located at ORACLE_HOME\RDBMS\ADMIN.

D. Grant READ and WRITE privileges to the current user on the PROFILE_DATA directory object.

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.)

A. Explicitly validate the identifier length limit.

B. Add AUTHID DEFINER to the definition of the procedure.

C. Use PRAGMA RESTRICT_REFERENCES in the procedure.

D. Filter out control characters in user-supplied identifier names.

E. Use the object ID of the table from the data dictionary to build the trigger name.

View Exhibit1 and examine the structure of the EMPLOYEES table.

View the Exhibit2 and examine the PL/SQL block that you execute for displaying the last name and hire date of the employees in department ID 60.

Which statement is true about the outcome?

A. It generates an error because RECORD type cannot be used with varrays.

B. It generates an error because BULK COLLECT cannot be used with varrays.

C. It executes successfully only if department ID 60 has five or less than five employees.

D. It executes successfully even if department ID 60 has more than five employees by dynamically extending the varray.

The database instance was started up with the following initialization parameter values:

MEMORY_TARGET = 500M

RESULT_CACHE_MODE = FORCE

RESULT_CACHE_MAX_SIZE = 0

After the database startup, to enable the result cache, you issued the following command:

SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY;

Which is the effect of this command?

A. The query result cache is enabled and 2 MB of the memory target is allocated to the result cache.

B. The query result cache is enabled and 0.25% of the memory target is allocated to the result cache.

C. The command produces an error because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.

D. The query result cache is not enabled because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.

View the Exhibit and examine the code in the PL/SQL block.

The PL/SQL block generates an error on execution. What is the reason?

A. The DELETE(n) method cannot be used with varrays.

B. The DELETE(n) method cannot be used with nested tables.

C. The NEXT method cannot be used with an associative array with VARCHAR2 key values.

D. The NEXT method cannot be used with a nested table from which an element has been deleted.

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

Examine the PL/SQL block that you execute to find the average salary for employees in the 'Sales'

department:

DECLARE

TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20);

v_emp_sal emp_sal;

PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS

BEGIN

SELECT AVG(salary) INTO p_arr(p_dept_name)

FROM employees WHERE department_id=

(SELECT department_id FROM departments

WHERE department_name=p_dept_name);

END get_sal;

BEGIN

get_sal('Sales',v_emp_sal);

DBMS_OUTPUT.PUT_LINE( v_emp_sal('Sales'));

END;

/

What is the outcome?

A. It executes successfully and gives the correct output.

B. It generates an error because the associative array definition is not valid.

C. It generates an error because an associative array cannot be passed to a procedure in OUT mode.

D. It generates an error because an associative array cannot be used with the SELECT INTO statement.

You have an OE_ACCESS_ORDERS_POLICY security policy implemented on the ORDERS table in the OE schema. The user sessions are able to access only the desired rows. The database administrator (DBA) uses the following command:

SQL> EXECUTE DBMS_RLS.ENABLE_POLICY('OE','ORDERS','OE_ORDERS_ACCESS_POLICY',FALSE );

Which statement is true about user sessions that are connected currently?

A. The security policy remains in effect till the end of the current session.

B. The subsequent queries on the ORDERS table produce an ORA-01031: insufficient privileges error.

C. The subsequent queries on the ORDERS table within the current session are not controlled by the security policy.

D. The subsequent queries on the ORDERS table produce an ORA-28112: failed to execute policy function error.

Which two are major approaches that can be used to reduce the SQL injection by limiting user input? (Choose two.)

A. Restrict users accessing specified web page.

B. Use NUMBER data type if only positive integers are needed.

C. Use dynamic SQL and construct it through concatenation of input values.

D. In PL/SQL API, expose only those routines that are intended for customer use.

DATA_FILES is a directory object that contains the DETAILS.TXT text file.

You have the required permissions to access the directory object.

You create a table using the following command:

CREATE TABLE clob_tab(col2 CLOB);

View the Exhibit and examine the PL/SQL block that you execute for loading the external text file into the

table that currently has no rows. The PL/SQL block results in an error.

What correction must be done to ensure the PL/SQL block executes successfully?

A. The L_OUT variable must be initialized to an empty locator.

B. The L_OUT variable has to be declared as a temporary LOB.

C. The A_CLOB variable has to be declared as a temporary LOB.

D. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.

Which three actions can be performed by using the DBMS_ASSERT package to prevent SQL injection? (Choose three.)

A. Detect a wrong user.

B. Check input string length.

C. Verify qualified SQL names.

D. Validate TNS connect strings.

E. Verify an existing schema name.

F. Enclose string literals within double quotation marks.

A products TABLE exists with a PROD_ID column.

Examine this PL/SQL block: Which statement is true?

A. It executes successfully only if v_ref_cur := DBMS_SQL.TO_REFCURSOR (V_CUR); is added before the FETCH statement.

B. It executes successfully.

C. It executes successfully only if v_ref_cur: = DBMS_SQL.TO_CURSOR_NUMBER (v_cur); is added before the FETCH statement.

D. It executes successfully only if the FETCH statement is replaced by DBMS_SQL.RETURN_RESULT (v_ref_cur);

E. It executes successfully only if the FETCH statement is replaced by DBMS_SQL.FETCH_ROWS (v_cur);

You are designing and developing a complex database application built using many dynamic SQL statements. Which option could expose your code to SQL injection attacks?

A. Using bind variables instead of directly concatenating parameters into dynamic SQL statements

B. Using automated tools to generate code

C. Not validating parameters which are concatenated into dynamic SQL statements

D. Validating parameters before concatenating them into dynamic SQL statements

E. Having excess database privileges

Examine the structure of the EMP table:

Name

Null?

Type

——————

————–

———————

EMPNO ENAME

NOT

NULL NUMBER (4)VARCHAR2 (10) SAL

NUMBER (7, 2)

Examine this code:

Which code should be inserted to display the collection contents?

A. 1_indx := 1_list.FIRST; WHILE (1_indx IS NOT NULL) LOOP DBMS_OUTPUT.PUT_LINE (1_indx || ‘ ’ || 1_list (1_indx)); 1_indx := 1_emp.NEXT (1_indx); END LOOP;

B. FOR indx IN 1_list. COUNT .. -1 LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx)); END LOOP;

C. FOR indx IN -1 .. 1_list.LIMIT LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx) ); END LOOP;

D. FOR indx IN 1_list.FIRST . . 1_list.LAST LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx)); END LOOP;

Examine these statements:

Which two corrections will allow this anonymous block to execute successfully?

A. Add wk# .NEXT; before the 7thline.

B. Add i PLS_INTEGER; before the 3rdline.

C. Add wk#. EXTEND (1); before the 5thline.

D. Change line #2 to wk# tp_test# := tp_test# (tp_rec# ());

E. Replace lines 5 and 6 with wk# (i) := tp_rec# (i, i);

Examine these statements:

1.

A function with invoker's rights can be result cached by using both AUTHID CURRENT_USER and RESULT_CACHE in its declaration.

2.

A function with invoker's rights cannot be result cached because it may allow a user to retrieve data cached by another user.

3.

Results in the result cache for an invoker's rights function will be cached by the value of CURRENT_SCHEMA in the invoking environment.

4.

By default, declaring a function with RESULT_CACHE makes it an invoker's rights result cached function.

5.

A function with invoker's rights can be result cached because the function result cache is created per user for invoker's rights functions.

Identify the set of correct statements with respect to invoker's rights and function result caching.

A. 1, 3, 4 and 5

B. 1 and 3

C. 1, 3 and 5

D. 2 only

E. 1, 3 and 4