1Z0-148 Online Practice Questions and Answers

Which statements are true about the SecureFile storage paradigm? (Choose two.)

A. SecureFile storage can be used for internal and external LOBs.

B. Automatic Segment Space Management must be enabled for a tablespace to store SecureFile LOBs.

C. SecureFile options enabled for a LOB column can be overridden on a per-LOB basis within the column.

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

Which two statements are true about the initialization of internal LOBs? (Choose two.)

A. The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize only null internal LOBs.

B. The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize only non- NULL internal LOBs.

C. The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize both null and non-NULL internal LOBs.

D. The CLOB and BLOB columns can be initialized only by using the EMPTY_CLOB() and EMPTY_BLOB () functions, respectively.

E. The CLOB and BLOB columns can be initialized with a character or raw string, respectively, provided they are less than 4000 bytes in size.

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.

The following command is executed to start tracing in a user session:

SQL> EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ENABLED_EXCEPTIONS) ;

Which statement is true about the effect of the command?

A. It allows tracing of exceptions raised by all subprograms executed in the session.

B. It allows tracing of user-defined exceptions raised by all subprograms executed in the session.

C. It allows tracing of system-defined exceptions raised by all subprograms executed in the session.

D. It allows tracing of exceptions raised by subprograms compiled with the DEBUG option and executed in the session.

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

This result cache is enabled for the database instance. Examine this code for a PL/SQL function:

Which two actions would ensure that the same result will be consistently returned for any session when the same input value is passed to the function?

A. Add a parameter, fmt, and change the RETURN statement to: RETURN TO_CHAR (date_hired, fmt);

B. Set the RESULT_CACHE_MODE parameter to FORCE.

C. Increase the value for the RESULT_CACHE_MAX_SIZE parameter.

D. Change the return type of GET_HIRE_DATE to DATE and have each session invoke the TO_CHAR function.

E. Set the RESULT_CACHE_MAX_RESULT parameter to 0.

Examine this Java method in class Employee, loaded into the Oracle database:

Public static int updateSalary (String name, float salary) {...}

Which PL/SQL specification can be used to publish this method?

A. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA LIBRARY “Employee” NAME “updateSalary” PARAMETERS (p_nm java.lang. String, p_sal float, RETURN int);

B. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA

NAME “Employee.updateSalary”

PARAMETERS (p_nm java.lang.String, p_sal float, RETURN int);

C. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA NAME “Employee.updateSalary” PARAMETERS (“name” java.lang.String, “salary” float, RETURN int);

D. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA NAME ‘Employee.updateSalary (java.lang.String, float) return int’;

E. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA NAME ‘int Employee.updateSalary (java.lang.String, float)’;

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”

Your company decided to update its employees' contact information as a one-time event.

With thousands of employees' records to be updated, the project manager hoped to achieve substantial

performance gains using the DBMS_PARALLEL_EXECUTE package and identified these four actions to

be executed.

1) dbms_parallel_execute.create_task

2) GRANT system privilege CREATE JOB to the user executing this task

3) dbms_parallel_execute.run_task 4) dbms_parallel_execute.create_chunks_by_rowid Select the correct order of actions to implement the parallel execution of PL/SQL blocks.

A. 2, 1, 3 and 4

B. 1, 2, 4 and 3

C. 2, 1, 4 and 3

D. 1, 2, 3 and 4

E. 2, 4, 1 and 3

Which two statements are true about inlining PL/SQL subprograms? (Choose two.)

A. PLSQL_OPTIMIZE_LEVEL must be set to 3 to inline external subroutines.

B. Programs making use of large helper subroutines are good candidates for inlining.

C. PRAGMA INLINE must be specified.

D. Only local subroutines can be inlined.

E. PLSQL_OPTIMIZE_LEVEL must be set to a value greater than or equal to 2.