Which statements are true about the above PL/SQL block?

Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block.
CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,
deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),
salary Number(8),
deptname VARCHAR2(30) DEFAULT ‘Sales’)
/
DECLARE
SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;
v_emprec v_emprec_subtype;
BEGIN
v_emprec.empid := NULL; v_emprec.salary := 10000.002;
v_emprec.deptid := 50;
DBMS_OUTPUT.PUT_LINE(‘v_emprec.deptname: ‘ || v_emprec.deptname);
END;
/
Which statements are true about the above PL/SQL block? (Choose two.)

Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block.
CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,
deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),
salary Number(8),
deptname VARCHAR2(30) DEFAULT ‘Sales’)
/
DECLARE
SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;
v_emprec v_emprec_subtype;
BEGIN
v_emprec.empid := NULL; v_emprec.salary := 10000.002;
v_emprec.deptid := 50;
DBMS_OUTPUT.PUT_LINE(‘v_emprec.deptname: ‘ || v_emprec.deptname);
END;
/
Which statements are true about the above PL/SQL block? (Choose two.)

A.
V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.

B.
Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is
inherited.

C.
Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of
the decimal.

D.
Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is
not inherited.



Leave a Reply 5

Your email address will not be published. Required fields are marked *


ja

ja

CREATE TABLE employees_temp (
empid NUMBER(6) NOT NULL,
deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),
salary Number(8),
deptname VARCHAR2(30) DEFAULT ‘Sales’
);

DECLARE
SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;
v_emprec v_emprec_subtype;
BEGIN
v_emprec.empid := NULL;
v_emprec.salary := 10000.002;
v_emprec.deptid := 50;
DBMS_OUTPUT.PUT_LINE(‘v_emprec.deptname: ‘ || v_emprec.deptname);
DBMS_OUTPUT.PUT_LINE(‘v_emprec.empid: ‘ || v_emprec.empid);
DBMS_OUTPUT.PUT_LINE(‘v_emprec.salary: ‘ || v_emprec.salary);
DBMS_OUTPUT.PUT_LINE(‘v_emprec.deptid: ‘ || v_emprec.deptid);
END;

PIERO

PIERO

P.S i didn’t know you could subtype also a %rowtipe
why subtype it ?

Leo Yu

Leo Yu

just for the exam purpose :-), to test if you know the subType only inherit the size constraint from %ROWTYPE or %TYPE. and subType not inherit the other constraints like check between, not null and default. One possible exam item could be the var NUMBER(3) in %TYPE, and one subType inherit the size 3, then you assign the var=7777 exceed the size, then error occured,