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;
CREATE OR REPLACE 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 OR REPLACE PACKAGE 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
rec pkg.rec_typ;
BEGIN
rec.price := 100;
rec.inc_pct := 50;
execute immediate 'BEGIN pkg.calc_price(:rec); END;' using in out rec;
DBMS_OUTPUT.PUT_LINE('rec.price: ' || rec.price);
rec.price := 1000;
rec.inc_pct := 50;
begin
pkg.calc_price(rec);
end;
DBMS_OUTPUT.PUT_LINE('rec.price: ' || rec.price);
--execute immediate 'BEGIN pkg.calc_price(:rec); END;' using in out rec(100,50);
-- PLS-00308: this construct is not allowed as the origin of an assignment
--execute immediate 'BEGIN pkg.calc_price(:rec); END;';
-- ORA-01008: not all variables bound
END;
/
A is the correct answer:
B- You cant use PL/SQL data types and record type into the USING clause;
C- Would be correct if you use in the EXECUTE IMMEDIATE statement a bind variable for the function call and if you use clause USING IN OUT; in this situation function calc_price needs an IN OUT parameter, so using EXECUTE IMMEDIATE with a call to this function without a bind variable the compiler will give an error because it cant return the result into the IN OUT variable.
D- Here the USING clause of EXECUTE IMMEDIATE its using a RECORD type wich is forbidden (as an explanation for the A answer, wich is right, is good to use a record variable wich is create under a package, because it becomes an SQL type and can be calle from an SQL enviroment)
I verified in PL/SQL and ALL the sentences executes but all have error, the only who has less error is the B answer but the anonymous block has pragma error
No A is not a correct answer
Look at this :
DECLARE 1_rec pkg. rec_typ; BEGIN 1_rec_price :=100; 1_rec.inc_pct :=50;
Warning : 1_rec_price don't exist. The correct code is : 1_rec.price := 100;
So the correct answer is B
Next time take time to check Your opinion. There is no posibility to use l_rec(100,50) as IN OUT param)
upvoted 1 times
...
...
...
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Rakeshpro
2 years, 4 months agoRakeshpro
2 years, 4 months agoRakeshpro
2 years, 4 months agoRakeshpro
2 years, 4 months agoJosephgreenson
3 years, 1 month agoCosminCof
4 years, 2 months agoTheOracleWasTaken
1 year, 5 months agojcamt
4 years, 3 months agopeguynya
4 years, 6 months agokahabe59
4 years, 7 months agoorakell
5 years, 2 months agoGuyFabrice
4 years, 8 months agoJosephgreenson
3 years, 1 month agoprotonik2020
4 years, 5 months ago