Oracle Database: Advanced PL/SQL (1z1-148) Free Practice Test

Question 1

Examine the structure of the EMP table:

Examine this code and statements:

Which statement is true?

A. The result cache gets invalidated only after the ALTER FUNCTION statement.

B. The result cache gets invalidated only after each UPDATE statement.

C. The result cache does not get invalidated at all since the RELIES_ON clause is not used.

D. The result cache gets invalidated after the ALTER FUNCTION and again after each UPDATE statement.

E. The result cache gets invalidated after the ALTER FUNCTION and again after the second UPDATE statement.

F. The result cache gets invalidated only after the second UPDATE statement.

Correct Answer: C

Question 2

Which codes executes successfully?

A. CREATE PACKAGE pkg ASTYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER);END pkg;/CREATE PROCEDURE calc_price (price_rec IN OUT pkg. rec_typ) ASBEGINprice_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100;END/DECLARE1_rec pkg.rec_typ;BEGINEXECUTE IMMEDIATE 'BEGIN calc_price (:rec); END;' USING IN OUT 1_rec (100, 50);END;

B. CREATE PACKAGE pkg ASTYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER);END pkg;/CREATE PROCEDURE calc_price (price_rec IN OUT pkg. rec_typ) ASBEGINprice_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100;END ;/DECLARE1_rec pkg.
rec_typ;BEGIN1_rec_price :=100;1_rec.inc_pct :=50;EXECUTE IMMEDIATE 'BEGIN calc_price (1_rec); END;';END;

C. CREATE PACKAGE pkg ASTYPE rec_typ IS RECORD (price NUMBER, inc_pct
NUMBER);PROCEDURE calc_price (price_rec IN OUT rec_typ);END pkg;/CREATE PACAKGE BODY pkg ASPROCEDURE calc_price (price_rec IN OUT rec_typ) ASBEGINprice_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100;END calc_price;END pkg;/DECLARE1_rec pkg. rec_typ;BEGIN1_rec_price :=100;1_rec.inc_pct :=50;EXECUTE IMMEDIATE 'BEGIN pkg.
calc_price (:rec); END;' USING IN OUT 1_rec;END;

D. DECLARETYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER);1_rec
rec-typ;PROCEDURE calc_price (price_rec IN OUT rec_typ) ASBEGINprice_rec.price := price-rec.price+ (price_rec.price * price_rec.inc_pct)/100;END;BEGIN1_rec_price :=100;1_rec.inc_pct
:=50;EXECUTE IMMEDIATE 'BEGIN calc_price (:rec); END;' USING IN OUT 1_rec;END;

Correct Answer: C

Question 3

Examine this code:

Assuming the default edition is ORA$BASE, what is the result of the last execution of hello?

A. Hello, edition 1.
Hello, edition 2.

B. Hello, edition 2.
Hello, edition 1.

C. Hello, edition 2.

D. Hello, edition 1.

E. An exception is raised.

Correct Answer: D

Question 4

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.RETURN_RESULT (v_ref_cur);

C. It executes successfully.

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 only if the FETCH statement is replaced by DBMS_SQL.FETCH_ROWS (v_cur);

Correct Answer: A,B

Question 5

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. DECLARETYPE names_t IS TABLE OF SYS_REFCURSOR INDEX BY PLS_INTEGER;cv1 names_t;v_dept_name departments.department_name%TYPE;BEGINOPEN 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;

B. DECLARETYPE dept_cur IS REF CURSOR RETURN departments%ROWTYPE;cv1
dept_cur;v_dept_name departments.department_name%TYPE;BEGINOPEN 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. DECLAREcv1 SYS_REFCURSOR;v_dept_name
departments.department_name%TYPE;BEGINEXECUTE IMMEDIATE 'BEGIN OPEN: cv1 FORSELECT 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;

D. DECLARETYPE dept_cur IS REF CURSOR;cv1 dept_cur;v_dept_name departments.
department_name%TYPE;BEGINOPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10;IF cv1 IS NOT NULL THENFETCH cv1 INTO v_dept_name;DBMS_OUTPUT.PUT_LINE (v_dept_name);END IFCLOSE cv1;END;

Correct Answer: C,D

Question 6

You are logged on to the SCOTT schema and the schema has EMP and DEPT tables already created:
Examine this PL/SQL procedure:

Which PL/SQL block will raise an exception?

A. EXEC get_tab_row_count ('DEPT');

B. EXEC get_tab_row_count ('DEPT, EMP')

C. EXEC get_tab_row_count (' "EMP" ');

D. EXEC get_tab_row_count ('emp');

E. EXEC get_tab_row_count ('SCOTT.EMP');

Correct Answer: B

Question 7

Which two statements are correct for collecting data about identifiers in PL/SQL source code?

A. ALTER SYSTEM SET PLSCOPE_SETTINGS = 'IDENTIFIERS: NONE'

B. ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS: ALL'

C. ALTER <function/Procedure> COMPILE PLSCOPE_SETTINGS = 'IDENTIFIERS: ALL'

D. ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS: NONE'

E. CREATE < function/Procedure> PLSCOPE_SETTINGS = 'IDENTIFIERS: ALL' AS ...

Correct Answer: B,C

Question 8

Examine this code:

What is the correct statement to get the value of attribute ACCOUNT_MGR after the procedure has been executed?

A. SELECT SYS_CONTEXT ('USERENV', 'ACCOUNT_MGR') FROM dual;

B. SELECT SYS_CONTEXT ('ACCOUNT_MGR', 'ORDER_CTX') FROM dual;

C. SELECT USERENV ('ACCOUNT_MGR') FROM dual;

D. SELECT SYS_CONTEXT ('ORDER_CTX', 'ACCOUNT_MGR') FROM dual;

E. SELECT USERENV ('ORDER_CTX') FROM dual;

Correct Answer: A

Question 9

Which statement is true in regard to minimizing attack surface during application design?

A. Private functions and procedures should be declared in package specifications.

B. All the code should run anonymously.

C. Reusable components should be developed and used extensively.

D. Only one user account should be used.

Correct Answer: C

Welcome to TestSimulate

Oracle Database: Advanced PL/SQL (1z1-148) Free Practice Test