Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 91:

  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”

  Correct Answer: B

• Question 92:

  Examine this external function declaration:

  CREATE FUNCTION compare_and_sum(p1 PLS_INTEGER, p2 IN PLS_INTEGER, p3 IN OUT

  NUMBER)

  RETURN PLS_INTEGER

  AS LANGUAGE C LIBRARY mylib

  NAME "compareAndSum" WITH CONTEXT;

  Which C function does it publish?

  A. OCINumber * compareAndSum (OCIExtProcContext *ctx, OCINumber *p1, OCINumber *p2, OCINumber *p3);

  B. OCINumber compareAndSum (OCIExtProcContext *ctx, OCINumber p1, OCINumber p2, OCINumber *p3);

  C. int compareAndSum (OCIExtProcContext *ctx, int p1, int p2, OCINumber *p3);

  D. int compareAndSum (OCIExtProcContext *ctx, int p1, int p2, OCINumber p3);

  E. int compareAndSum (OCIExtProcContext *ctx, int p1, int p2, int p3);

  F. int compareAndSum (OCIExtProcContext *ctx, int p1, int p2, int* p3);

  G. OCINumber compareAndSum (OCIExtProcContext *ctx, OCINumber p1, OCINumber p2, OCINumber p3);

  Correct Answer: F

• Question 93:

  Examine this code:

  

  Users of this function may set different date formats in their sessions.

  Which two modifications must be made to allow the use of your session's date format when outputting the cached result of this function?

  A. Change the RETURN type to DATE.

  B. Change AUTHID to CURRENT_USER.

  C. Use the TO_CHAR function around SYSDATE, that is, 1_date := TO_CHAR (SYSDATE).

  D. Change the data type of 1_date to DATE.

  E. Set NLS_DATE_FORMAT to `DD-MM-YY' at the instance level.

  F. Set the RESULT_CACHE_MODE parameter to FORCE.

  Correct Answer: AB

• Question 94:

  Which statement is correct about DBMS_LOB.SETOPTIONS and DBMS_LOB.GETOPTIONS for SecureFiles?

  A. DBMS_LOB.GETOPTIONS can only be used for BLOB data types.

  B. DBMS_LOB.SETOPTIONS can perform operations on individual SecureFiles but not an entire column.

  C. DBMS_LOB. SETOPTIONS can set option types COMPRESS, DUPLICATE, and ENCRYPT.

  D. If a table was not created with compression specified in the store as securefile clause then DBMS_LOB.SETOPTIONS can be used to enable it later.

  Correct Answer: D

  Reference: https://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm

• Question 95:

  Which three commands can be used to set PL/SQL conditional compilation inquiry directive MODE?

  A. ALTER SESSION SET PLSQL_CCFLAGS = `mode: FALSE';

  B. ALTER SESSION SET PLSQL_CCFLAGS= `mode: NULL';

  C. ALTER SESSION SET PLSQL_CCFLAGS= `mode: Level 1';

  D. ALTER SESSION SET PLSQL_CCFLAGS= `mode: Level1';

  E. ALTER SESSION SET PLSQL_CCFLAGS= `mode: 1'

  Correct Answer: ACE

• Question 96:

  In which situation will cached results become invalid?

  A. When the memory allocated to the server result cache is increased using the RESULT_CACHE_MAX_SIZE initialization parameter

  B. When a session executes an insert, update, or delete statement on a table or view that is queried by the result-cached function

  C. When a session on this database instance invokes the function with the same parameter values

  D. When the RESULT_CACHE_MODE parameter is set to FORCE.

  E. When a new session is opened to invoke the function which is already cached

  Correct Answer: CD

  Reference: https://mariadb.com/kb/en/library/query-cache/

• Question 97:

  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

  Correct Answer: B

• Question 98:

  Examine this PL/SQL function:

  

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

  A. There are no compilation warnings or errors.

  B. It fails compilation.

  C. An information compilation warning is generated.

  D. A performance compilation warning is generated.

  E. A severe compilation warning is generated.

  Correct Answer: E

• Question 99:

  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.

  Correct Answer: BD

• Question 100:

  Which codes executes successfully?

  A. CREATE PACKAGE pkg AS TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); PROCEDURE calc_price (price_rec IN OUT rec_typ); END pkg; / CREATE PACAKGE BODY pkg AS PROCEDURE calc_price (price_rec IN OUT rec_typ) AS BEGIN price_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100; END calc_price; END pkg; / DECLARE 1_rec pkg. rec_typ; BEGIN 1_rec_price :=100; 1_rec.inc_pct :=50; EXECUTE IMMEDIATE ‘BEGIN pkg. calc_price (:rec); END;’ USING IN OUT 1_rec; END;

  B. CREATE PACKAGE pkg AS TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); END pkg; / CREATE PROCEDURE calc_price (price_rec IN OUT pkg. rec_typ) AS BEGIN price_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100; END / DECLARE 1_rec pkg.rec_typ; BEGIN EXECUTE IMMEDIATE ‘BEGIN calc_price (:rec); END;’ USING IN OUT 1_rec (100, 50); END;

  C. CREATE PACKAGE pkg AS TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); END pkg; / CREATE PROCEDURE calc_price (price_rec IN OUT pkg. rec_typ) AS BEGIN price_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100; END ; / DECLARE 1_rec pkg. rec_typ; BEGIN 1_rec_price :=100; 1_rec.inc_pct :=50; EXECUTE IMMEDIATE ‘BEGIN calc_price (1_rec); END;’; END;

  D. DECLARE TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); 1_rec rec-typ; PROCEDURE calc_price (price_rec IN OUT rec_typ) AS BEGIN price_rec.price := price-rec.price+ (price_rec.price * price_rec.inc_pct)/100; END; BEGIN 1_rec_price :=100; 1_rec.inc_pct :=50; EXECUTE IMMEDIATE ‘BEGIN calc_price (:rec); END;’ USING IN OUT 1_rec; END;

  Correct Answer: B