Download Oracle.1z0-148.PracticeTest.2018-08-07.43q.tqb

Vendor:	Oracle
Exam Code:	1z0-148
Exam Name:	Oracle Database 12c: Advanced PL/SQL
Date:	Aug 07, 2018
File Size:	2 MB
Downloads:	1

Download Exam

Demo Questions

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?

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.

Correct answer: C

Which codes executes successfully?

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

Correct answer: B

Examine this function header:

FUNCTION calc_new_sal (emp_id NUMBER) RETURN NUMBER;

You want to ensure that whenever this PL/SQL function is invoked with the same parameter value across active sessions, the result is not recomputed.

If a DML statement is modifying a table which this function depends upon, the function result must be recomputed at that point in time for all sessions calling this function.

Which two actions should you perform?