Identify the component of the PL/SQL hierarchical profiler that uploads the result of profiling into database tables.

A. the trace file component

B. the analyzer component

C. the shared library component

D. the data collection component

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?

A. The user has insufficient privileges on the DBMS_SESSION package.

B. The subprograms inside the package have not been created with the invoker's right.

C. The THE_PREDICATE function has an insufficient number of parameters in the package.

D. The policy is created by using SALES_ORDERS_PKG.THE_PREDICATE without a parameter.

Which two guidelines should be considered when designing and using cursors in a PL/SQL block? (Choose two.)

A. When fetching from a cursor, fetch into a record.

B. When fetching from a cursor, fetch required values into individually declared variables.

C. Whenever possible, explicitly declare the cursor and use the OPEN, FETCH, and CLOSE statements to manipulate the cursor instead of using the cursor FOR loop.

D. Whenever possible, use the cursor FOR loop instead of explicitly declaring the cursor and using the OPEN, FETCH, and CLOSE statements to manipulate the cursor.

Examine this function: Execute the query:

SELECT remap_schema FROM dual;

Which is the correct output from the query?

A. CREATE TABLE “EMP” (“EMPNO” NUMBER (4,0), “ENAME” VARCHAR2 (10), “JOB” VARCHAR2 (9), “MGR” NUMBER (4,0), “HIREDATE” DATE, “SAL” NUMBER (7,2) , “COMM” NUMBER (7,2), “DEPTNO” NUMBER (2,0), CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “USERS” ENABLE, CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”) REFERENCES “DEPT” (“DEPTNO”) ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “USERS”

B. CREATE TABLE “EMP” (“EMPNO” NUMBER (4, 0), “ENAME” VARCHAR2 (10), “JOB” VARCHAR2 (9), “MGR” NUMBER (4, 0), “HIREDATE” DATE, “SAL” NUMBER (7, 2), “COMM” NUMBER (7, 2), “DEPTNO” NUMBER (2, 0), CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”) USING INDEX ENABLE, CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”) REFERENCES “DEPT” (“DEPTNO”) ENABLE)

C. CREATE TABLE “SCOTT”. “EMP” (“EMPNO” NUMBER (4, 0), “ENAME” VARCHAR2 (10), “JOB” VARCHAR2 (9), “MGR” NUMBER (4, 0), “HIREDATE” DATE, “SAL” NUMBER (7, 2), “COMM” NUMBER (7, 2), “DEPTNO” NUMBER (2, 0), CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)

USING INDEX ENABLE,

CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)

REFERENCES “DEPT” (“DEPTNO”) ENABLE)

D. CREATE TABLE “EMP” (“EMPNO” NUMBER (4,0), “ENAME” VARCHAR2 (10), “JOB” VARCHAR2 (9), “MGR” NUMBER (4,0), “HIREDATE” DATE, “SAL” NUMBER (7, 2) , “COMM” NUMBER (7, 2), “DEPTNO” NUMBER (2,0), CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “SYSAUX” ENABLE, CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”) REFERENCES “DEPT” (“DEPTNO”) ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “SYSAUX”

Examine this block:

Which two will be correct after line 5?

A. va. LAST and va. LIMIT will return the same value.

B. va. LAST and va. COUNT will return the same value.

C. va. LIMIT and va. COUNT will return the same value.

D. va. LIMIT and va. NEXT (199) will return the same value.

E. va. LAST will return 200.

F. va. NEXT (199) will return NULL.

Examine this declaration section:

Which two executable sections will display the message `Summary is null'?

A. BEGIN 1_rec := NULL; 1_emp := emp_typ (1_rec); IF 1_emp (1).expr_summary IS EMPTY THEN DBMS_OUTPUT.PUT_LINE (‘Summary is null’); END IF; END;

B. BEGIN 1_rec.emp_id :=1; 1_rec.expr_summary := NULL; 1_emp :=emp_typ (1_rec); IF 1_emp(1).expr_summary IS NULL THEN DBMS_OUTPUT.PUT_LINE (‘Summary is null’); END IF; END;

C. BEGIN 1_rec.emp_id :=1; 1_rec.expr_summary := EMPTY_CLOB (); 1_emp := emp_typ (1_rec); IF 1_emp(1).expr_summary IS NULL THEN DBMS_OUTPUT.PUT_LINE (‘Summary is null’); END IF; END;

D. BEGIN 1_emp := emp_typ (); IF NOT 1_emp. EXISTS (1) THEN DBMS_OUTPUT.PUT_LINE (‘Summary is null’); END IF; END;

E. BEGIN 1_emp. EXTEND; IF NOT 1_emp. EXISTS (1) THEN DBMS_OUTPUT.PUT_LINE (‘Summary is null’); END IF; END;

Examine this function body:

BEGIN SELECT hire_date INTO date_hired FROM employees WHERE employee_id=emp_id;RETURN TO_CHAR(date_hired);

END;

Which two headers will allow this function to compile successfully and take advantage of both invoker's rights and function result caching?

A. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE RELIES_ON (departments) AUTHID CURRENT_USER IS date_hired DATE;

B. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE AUTHID CURRENT_USER IS date_hired DATE;

C. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE AUTHID DEFINER IS date_hired DATE;

D. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE RELIES_ON (employees) AUTHID CURRENT_USER IS date_hired DATE;

E. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 AUTHID DEFINER IS date_hired DATE;

Which two statements are true regarding edition-based redefinition (EBR)?

A. There is no default edition defined in the database.

B. EBR does not let you upgrade the database components of an application while in use.

C. You never use EBR to copy the database objects and redefine the copied objects in isolation.

D. Editions are non-schema objects.

E. When you change an editioned object, all of its dependents remain valid.

F. Tables are not editionable objects.

Examine this code:

CREATE TYPE list_typ IS TABLE OF NUMBER;

/

DECLARE

l_list list_typ := list_typ ();

Which two executable sections will display the message TRUE?

A. BEGIN IF l_list.LIMIT IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

B. BEGIN l_list.EXTEND; IF l_list.PRIOR (1_list.FIRST) IS NULL THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

C. BEGIN l_list.EXTEND; IF l_list IS EMPTY THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

D. BEGIN IF l_list.FIRST IS NULL THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

E. BEGIN IF l_list.FIRST =1 THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

With SERVEROUTPUT enabled, you successfully create the package YEARLY_LIST:

Examine this code:

You want to display the contents of CREATE_LIST.

Which two lines need to be corrected in the PL/SQL block? (Choose two.)

A. Line 6

B. Line 5

C. Line 7

D. Line 2

E. Line 3