Examine the following snippet of code from the DECLARE section of PL/SQL
DECLARE
Cut_name VERCHAR2 (20) NOT NULL := ‘tom jones’:
Same_name cust_nameTYPE:
Which statement is correct about the above snippets of code?
A.
The variable inherits only the data type from the CUST_NAME variable.
B.
The sake name variable inherits only the data type and default value from the CUST_NAME variable.
C.
The 3ake_nake variable inherits the data type, constraint, and default value from the CUST_NAME variable.
D.
The 3ake_nake variable inherits only the data type and constraint from the CUT_NAME variable resulting in an error
answer a,b
sorry, just ignore the above comment, D is correct answer
D is the correct answer.
thnx for ur help Howard , Suni 🙂
the variable surname inherits the data type, size, and NOT NULL constraint of the variable name. Because surname does not inherit the initial value of name, its declaration needs an initial value (which cannot exceed 25 characters).
Answer is A.
A NOT NULL constraint does not apply to the variable that are declared using %TYPE. (as mentioned in PLSQL_fundamentals.pdf )
So in this case you can assign NULL to same_name variable
ankur,
check the following code:
DECLARE
cust_name VaRCHAR2 (20) NOT NULL := ‘tom jones’;
Same_name cust_name%TYPE;
begin
null;
end;
/
ankur,
check the following code:
DECLARE
cust_name VaRCHAR2 (20) NOT NULL := ‘tom jones’;
Same_name cust_name%TYPE;
begin
null;
end;
/
alex,
check this code as ora*:
SET serveroutput ON
SET verify OFF
DECLARE
Same_name employees.last_name%TYPE;
BEGIN
dbms_output.put_line(Same_name);
END;
/
it is a difference between sql and pl/sql data types 😉
for people dose not have the db: this is the sql-dump:…
“LAST_NAME” VARCHAR2(25 BYTE) CONSTRAINT “EMP_LAST_NAME_NN” NOT NULL ENABLE,…
…and this is pl/sql – D is the right
Yes, its true.. the answer is D … a,b are from the test in visualcert .. that’s not correct..
I thought the answer should be A, because of this explanation on the oracle site:
Note that column constraints, such as the NOT NULL and check constraint, or default values are not inherited by items declared using %TYPE.
see http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/type_attribute.htm
However, when the referenced item is a PL/SQL variable, it works differently, this is the explanation on the oracle site:
An item declared with %TYPE (the referencing item) always inherits the data type of the referenced item. The referencing item inherits the constraints only if the referenced item is not a database column. The referencing item inherits the default value only if the referencing item is not a database column and does not have the NOT NULL constraint.
see http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/type_attribute.htm
So the answer is D
That last line (not applicable to this question) is a bit dodgy by the way, I couldn’t reproduce what they are saying…
The referencing item inherits the default value only if the referencing item is not a database column and does not have the NOT NULL constraint.
Did you mean – if the “referenced item” is not a database column?
“The referencing item inherits the default value only if the referencing item is not a database column and does not have the NOT NULL constraint.”
This seems not to be correct. Test:
set serveroutput on;
DECLARE
cust_name VaRCHAR2 (20) default ‘tom jones’;
Same_name cust_name%TYPE;
begin
dbms_output.put_line(‘a’ || Same_name);
end;
When we use %type for copying one variable to another, default value never gets copied.Only its datatype and constraints are copied.
Here in above code cut_name has not null constraint which is copied to same_name variable but the default value is not copied so it gives an error bcs same_name must be
initialized with some value.
Error is -‘a variable declared NOT NULL must have an initialization assignment’
When we use %type for copying one variable to another, default value never gets copied.Only its datatype and constraints are copied.
Here in above code cut_name has not null constraint which is copied to same_name variable but the default value is not copied so it gives an error bcs same_name must be
initialized with some value.
Error is -‘a variable declared NOT NULL must have an initialization assignment’
So Ans D is correct.
D
D
do not confuse %TYPE versus variable and %TYPE versus TABLE.COLUMN.
1) %TYPE versus variable
x number not null default 3;
y x%TYPE; — > inherits “not null”, doesn’t inherit “default 3” and results in exception
—-
2) %TYPE versus TABLE.COLUMN
create table test (x number default 3 not null);
y x%TYPE; — > doesn’t inherit “not null” and doesn’t inherit “default(3)”.
Answer is D.
Thanks for your clarification, previously I was confused the %TYPE versus column and %TYPE versus variable.
supplement one point:
If x number default 3;
y x%TYPE; –> inherits default value
please refer to guru says.