1z0-148 practice test

1. Question 1 · 1

   Which codes executes successfully?

   Choose one answer.

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

2. Question 2 · 1

   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 all answers that apply.

   

   

   • Line 2
   • Line 3
   • Line 5
   • Line 6
   • Line 7

3. Question 3 · 1

   Which two statements describe actions developers can take to make their application less vulnerable to security attacks?

   Choose all answers that apply.

   • Include the AUTHID DEFINER clause in stored program units.
   • Do not concatenate unchecked user input into dynamically constructed SQL statements.
   • Switch from using DBMS_SQL to EXECUTE IMMEDIATE.
   • Include the AUTHID CURRENT_USER clause in stored program units.
   • Increase the amount of code that is accessible to users by default.

4. Question 4 · 1

   Examine the code: Which two subprograms will be created successfully?

   Choose all answers that apply.

   

   • CREATE FUNCTION p4 (y pkg.tab_typ) RETURN pkg.tab_typ IS BEGIN EXECUTE IMMEDIATE SELECT pdt_id, pdt_name FROM TABLE (:b) BULT COLLECT INTO pkg.x USING y; RETURN pkg.x; END p4;
   • CREATE PROCEDURE p1 (y IN OUT pkg.tab_typ) IS BEGIN EXECUTE IMMEDIATE SELECT f (:b) FROM DUAL INTO y USING pkg.x; END p1;
   • CREATE PROCEDURE p2 (v IN OUT VARCHAR2) IS BEGIN EXECUTE IMMEDIATE SELECT f (:b) FROM DUAL INTO v USING pkg.x; END p2;
   • CREATE FUNCTION p3 RETURN pkg. tab_typ IS BEGIN EXECUTE IMMEDIATE SELECT f (:b) FROM DUAL INTO pkg.x; END p3;
   • CREATE PROCEDURE p5 (y pkg. rec_typ) IS BEGIN EXECUTE IMMEDIATE SELECT pdt_name FROM TABLE (:b) BULK COLLECT INTO y USING pkg.x;

5. Question 5 · 1

   Which two statements about the PL/SQL hierarchical profiler are true?

   Choose all answers that apply.

   • Access it using the DBMS_PROFILER package.
   • Access it using the DBMS_HPROF package.
   • Profiler data is recorded in tables and published in HTML reports.
   • It is only accessible after a grant of the CREATE PROFILE privilege.
   • It helps you identify subprograms that are causing bottlenecks in application performance.

6. Question 6 · 1

   Examine this function: Execute the query: SELECT remap_schema FROM dual; Which is the correct output from the query?

   Choose one answer.

   

   • 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 2417483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CHACHE 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"
   • 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)
   • 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)
   • 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 2417483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CHACHE 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)

7. Question 7 · 1

   Consider a function totalEmp () which takes a number as an input parameter and returns the total number of employees who have a salary higher than that parameter. Examine this PL/SQL package AS - Which two definitions of totalEmp () result in an implicit conversion by Oracle Database on executing this PL/SQL block?

   Choose all answers that apply.

   

   • CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IS total NUMBER :=0; BEGIN RETUNRN total; END; /
   • CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IS total NUMBER :=0; BEGIN RETUNRN total; END; /
   • CREATE FUNCTION totalEmp (sal IN PLS_INTEGER) RETURN NUMBER IS total NUMBER :=0; BEGIN RETUNRN total; END; /
   • CREATE FUNCTION totalEmp (sal IN BINARY_FLOAT) RETURN NUMBER IS total NUMBER :=0; BEGIN RETUNRN total; END; /
   • CREATE FUNCTION totalEmp (sal IN POSITIVEN) RETURN NUMBER IS total NUMBER :=0; BEGIN RETUNRN total; END;

8. Question 8 · 1

   Which two statements are true about PL/SQL AOIs for SecureFiles?

   Choose all answers that apply.

   • DBMS_LOB can be used to compress SecureFiles columns.
   • When using DBMS_DATAPUMP, if SecureFiles LOB data needs to be encrypted then ENCRYPTION=ALL must be specified.
   • If a BasicFiles LOB locator is passed to DBMS_LOB.ISSECUREFILE, an exception will be raised.
   • An online redefinition of SecureFiles by DBMS_REDEFINIITON can be performed with PDML (Parallel DML).

9. Question 9 · 1

   Examine this external function declaration: Which C function does it publish?

   Choose one answer.

   

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

10. Question 10 · 1

    Examine the structure and data in the CUSTOMERS table: Examine this code: What is the outcome on execution with SERVEROUTPUT enabled?

    Choose one answer.

    

    

    

    • It throws a NO_DATA_FOUND exception.
    • It displays 3 for both the row count and the total.
    • It throws a PL/SQL: numeric or value error: NULL index table key value exception.
    • It displays 3 for the row count and 2 for the total.

11. Question 11 · 1

    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?

    Choose one answer.

    • You can use different security policies for SELECT, INSERT, UPDATE, INDEX and DELETE statements.
    • You can use only one security policy per database object.
    • You implement security policies through database procedures.
    • Column-masking policies can be applied to SELECT, INSERT, UPDATE and DELETE statements.

12. Question 12 · 1

    You are designing and developing a complex database application built using many dynamic SQL statements. Which option could expose your code to SQL injection attacks?

    Choose one answer.

    • Using bind variables instead of directly concatenating parameters into dynamic SQL statements
    • Using automated tools to generate code
    • Not validating parameters which are concatenated into dynamic SQL statements
    • Validating parameters before concatenating them into dynamic SQL statements
    • Having excess database privileges

13. Question 13 · 1

    Examine this code executed as SYS: Examine this code executed as SPIDER and the error message received upon execution: What is the reason for this error?

    Choose one answer.

    

    

    • The procedure needs to be granted the DYNAMIC_TABLE_ROLE role.
    • The EXECUTE IMMEDIATE clause is not supported with roles.
    • Privileges granted through roles are never in effect when running definer’s rights procedures.
    • The user SPIDER needs to be granted the CREATE TABLE privilege and the procedure needs to be granted the DYNAMIC_TABLE_ROLE.

Paid access to all 13 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.