1z0-148 Exam Training Practice Tests (Oracle Database: Advanced PL/SQL)]

Question 1

Which three statements are correct with reference to intra unit inlining?

A. Inlining will always decrease the size of a unit. B. You cannot inline local subroutines. C. You cannot inline an external subroutine. D. Setting PLSQL_OPTIMIZE_LEVEL to 2 means automatic inlining is attempted. E. Pragmas apply only to calls in the next statement following the pragma. F. Programs that make use of smaller helper subroutines are good candidates for inlining.

Correct Answer: C,E,F

Question 2

Which tablespace is used to store the data collected by PL/Scope?

A. UNDOTBS1 B. SYSAUX C. TEMP D. SYSTEM E. USERS

Correct Answer: B

Question 3

Examine this code:

Examine this query:
SELECT ename, d.dname, job, sal, comm FROM emp e, dept d WHERE d.deptno = e.deptno; Which statement is correct regarding the execution of this SQL query?

A. The query will only fetch records pertaining to department 30 with SAL and COMM data replaced by
"xxxxx". B. The query will fetch records from all departments with SAL and COMM values displayed as NULL for all records other than department 30. C. The query will only fetch records pertaining to department 30 with SAL and COMM values displayed as NULL. D. The query will only fetch records pertaining to department 30 with SAL and COMM displaying the actual data from the table.

Correct Answer: D

Question 4

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 ENABLE,CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "DEPT" ("DEPTNO") ENABLE) 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 PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL
65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2417483645PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CHACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)TABLESPACE "SYSAUX" ENABLE,CONSTRAINT
"FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "DEPT" ("DEPTNO") ENABLE)
SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255NOCOMPRESS LOGGINGSTORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)TABLESPACE "SYSAUX" 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 255STORAGE (INITIAL
65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2417483645PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CHACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ENABLE,CONSTRAINT
"FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "DEPT" ("DEPTNO") ENABLE)
SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255NOCOMPRESS LOGGINGSTORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)TABLESPACE "USERS"

Correct Answer: A

Question 5

Examine this PL/SQL function:

What happens when the function is created with PLSQL_WARNINGS set to 'ENABLE: ALL'?

A. A performance compilation warning is generated. B. It fails compilation. C. There are no compilation warnings or errors. D. An information compilation warning is generated. E. A severe compilation warning is generated.

Correct Answer: E

Question 6

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_CURSOR_NUMBER (v_cur); is added before the FETCH statement. B. It executes successfully only if the FETCH statement is replaced by DBMS_SQL.FETCH_ROWS (v_cur); C. It executes successfully only if the FETCH statement is replaced by DBMS_SQL.RETURN_RESULT (v_ref_cur); D. It executes successfully only if v_ref_cur := DBMS_SQL.TO_REFCURSOR (V_CUR); is added before the FETCH statement. E. It executes successfully.

Correct Answer: A,C

Oracle Database: Advanced PL/SQL - 1z0-148 Exam Practice Test