Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 71:

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

  A. Include the AUTHID DEFINER clause in stored program units.

  B. Do not concatenate unchecked user input into dynamically constructed SQL statements.

  C. Switch from using DBMS_SQL to EXECUTE IMMEDIATE.

  D. Include the AUTHID CURRENT_USER clause in stored program units.

  E. Increase the amount of code that is accessible to users by default.

  Correct Answer: BD

• Question 72:

  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;

  Correct Answer: DE

• Question 73:

  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?

  A. Using bind variables instead of directly concatenating parameters into dynamic SQL statements

  B. Using automated tools to generate code

  C. Not validating parameters which are concatenated into dynamic SQL statements

  D. Validating parameters before concatenating them into dynamic SQL statements

  E. Having excess database privileges

  Correct Answer: D

• Question 74:

  In your schema, the DEPARTMENTS table contains the columns DEPARTMENT_ID and DEPARTMENT_NAME.

  You want to display the department name for existing department id 10.

  With SERVEROUTPUT enabled, which two blocks of code will give the required output?

  A. DECLARE TYPE dept_cur IS REF CURSOR; cv1 dept_cur; v_dept_name departments. department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; IF cv1 IS NOT NULL THEN FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); END IF CLOSE cv1; END;

  B. DECLARE TYPE dept_cur IS REF CURSOR RETURN departments%ROWTYPE; cv1 dept_cur; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT * FROM departments WHERE department_id=10; FETCH cv1. department_name INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;

  C. DECLARE TYPE names_t IS TABLE OF SYS_REFCURSOR INDEX BY PLS_INTEGER; cv1 names_t; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;

  D. DECLARE cv1 SYS_REFCURSOR; v_dept_name departments.department_name%TYPE; BEGIN EXECUTE IMMEDIATE ‘BEGIN OPEN: cv1 FOR SELECT department_name FROM departmnets WHERE department_id=10: END;’ USING IN cv1; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name);

  CLOSE cv1;

  END;

  Correct Answer: AD

• Question 75:

  Which PRAGMA statement may enable associated PL/SQL functions to run more efficiently when called from SQL?

  A. PRAGMA SERIALLY_REUSABLE;

  B. PRAGMA UDF;

  C. PRAGMA INLINE (`', `YES');

  D. PRAGMA AUTONOMOUS_TRANSACTION;

  Correct Answer: B

  Reference: https://mwidlake.wordpress.com/2015/11/04/pragma-udf-speeding-up-your-plsql-functionscalled-from-sql/

• Question 76:

  Examine this code:

  

  Examine this DML statement executed in the SCOTT schema:

  UPDATE emp SET comm = 1000 WHERE deptno= 20;

  What is the outcome after executing this statement?

  A. COMM is set to 1000 for all records in the EMP table where DEPTNO = 30.

  B. The statement executes successfully but no rows are updated.

  C. COMM is set to 1000 for all records in the EMP table where DEPTNO=20.

  D. The statement fails with error ORA-28115: policy with check option violation.

  Correct Answer: D

• Question 77:

  Which two statements are correct about the collections before v3. DELETE is executed?

  

  A. The values of v3(31) (2) and v3 (33) (2) are identical.

  B. The value of v3 (31) (3) is 6.

  C. The value of v3 (31) (1) and v3 (33) (3) are identical,

  D. The value of v3 (31) (1) is "hello".

  E. The values of v3 (32) (2) and v1 (2) are identical.

  Correct Answer: CE

• Question 78:

  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.

  Correct Answer: BF

• Question 79:

  The STUDENTS table with column LAST_NAME of data type VARCHAR2 exists in your database schema.

  Examine this PL/SQL block: Which two actions must you perform for this PL/SQL block to execute successfully?

  

  A. Replace the FOR loop with FOR name_rec IN names_varray.FIRST .. names_varray.LAST LOOP.

  B. Replace the L_NAME_TYPE declaration with TYPE 1_name_type IS VARRAY (25) OF SYS_REFCURSOR;

  C. Add name_rec name_cur%ROWTYPE; at the end of the DECLARE section.

  D. Replace the NAMES_VARRAY declaration with names_varray 1_name_type := 1_name_type ();

  E. Replace the NAMES_VARRAY declaration with names_varray 1_name_type := null;

  F. Add names_varray.EXTEND after the FOR ...LOOP statement.

  Correct Answer: EF

• Question 80:

  Which must be true in order to add RESULT_CACHE to a function header and have it compile successfully?

  A. The IN parameters must not include BLOB, CLOB, collection or record data types.

  B. The function must be created with invoker's rights or in an anonymous block.

  C. The function must be declared as a pipelined table function.

  D. The function must have an OUT or an IN OUT parameter.

  Correct Answer: A

  Reference: https://docs.oracle.com/cd/E18283_01/appdev.112/e17126/subprograms.htm#insertedID11