1Z0-148 Online Practice Questions and Answers

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

salary Number(8),

deptname VARCHAR2(30) DEFAULT 'Sales')

/

DECLARE

SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;

v_emprec v_emprec_subtype;

BEGIN

v_emprec.empid := NULL; v_emprec.salary := 10000.002;

v_emprec.deptid := 50;

DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname);

END;

/

Which statements are true about the above PL/SQL block? (Choose two.)

A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.

B. Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is inherited.

C. Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of the decimal.

D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.

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:

SQL> EXECUTE DBMS_SESSION.SET_CONTEXT('SALES_ORDERS_CTX','ACCOUNT_MGR','OE');

BEGIN DBMS_SESSION.SET_CONTEXT('SALES_ORDERS_CTX','ACCOUNT_MGR','OE'); END;

*

ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 94 ORA-06512: at line 1 What is the reason for this error?

A. The context was created with a package name in the USING clause.

B. The attribute can be set only in the package associated with the context.

C. The package associated with the context did not exist at the time of creation of the context.

D. The value for an attribute of a user-defined context can be set only by the ALTER SESSION command.

Which two reports can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package? (Choose two.)

A. DDL report for all objects dependent on a table

B. DDL report for all the objects stored in a tablespace

C. DDL report for all the invalidated objects in a schema

D. data definition language (DDL) report for all the tables in a schema

Which two statements are true about the query results stored in the query result cache? (Choose two.)

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

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

C. Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.

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

Examine the EMPLOYEE_IDS table its data:

CREATE TABLE employee_ids(

emp_id NUMBER

emp_userid VARCHAR2(10),

emp)taxid NUMBER INVISIBLE DEFAULT -1);

EMP_ID

EMP_USERID

EMP_TAXID

--------------

----------------------

-------------------

1011

JJSONES

3789

1012

SSMITH

-1

Examine this PL/SQL block:

DECLARE CURSOR cur IS SELECT * FROM employee_ids ORDER BY emp_id;rec cur%ROWTYPE;

BEGIN OPEN cur; LOOP

FETCH cur INTO rec;

EXIT WHEN cur%NOTFOUND;

DBMS_OUTPUT PUT_LINE('Fetched'| | rec.emp_id||','||rec.emp_userid||'.'||rec.emp_taxid);END LOOP; CLOSE cur;

END;

What is the result of executing this PL/SQL block with SERVEROUTPUT enabled?

A. It executes successfully and outputs: Fetched: 1011, JJONES, 3789 Fetched: 1012, SSMITH, -1

B. Compilation fails saying EMP_TAXID must be declared.

C. An exception is thrown at runtime saying EMP_TAXID is not visible.

D. It executes successfully and outputs: Fetched: 1011, JJONES, Fetched: 1012, SSMITH,

E. It executes successfully and outputs: Fetched: 1011, JJONES, -1 Fetched: 1012, SSMITH, -1

Examine these program units: Which two blocks will execute successfully?

A. BEGIN My_proc; END;

B. BEGIN pkg2.proc3; END;

C. BEGIN pkg2.proc2; END;

D. BEGIN pkg1.proc1a; END;

E. BEGIN pkg1.proc1b; END;

Examine this function header: FUNCTION calc_new_sal (emp_id NUMBER) RETURN NUMBER;

You want to ensure that whenever this PL/SQL function is invoked with the same parameter value across active sessions, the result is not recomputed.

If a DML statement is modifying a table which this function depends upon, the function result must be recomputed at that point in time for all sessions calling this function.

Which two actions should you perform?

A. Ensure RESULT_CACHE_MAX_SIZE is greater than 0.

B. Enable the result cache by using DBMS_RESULT_CACHE.BYPASS (FALSE).

C. Add the deterministic clause to the function definition.

D. Add the RELIES_ON clause to the function definition.

E. Add the RESULT_CACHE clause to the function definition.

You are designing and developing a complex database application and implementing fine-grained access control using security policies.

Which statement is true with respect to attaching security policies to database objects?

A. You can use different security policies for SELECT, INSERT, UPDATE, INDEX and DELETE statements.

B. You can use only one security policy per database object.

C. You implement security policies through database procedures.

D. Column-masking policies can be applied to SELECT, INSERT, UPDATE and DELETE statements.