Examine the following snippet of code from the DECLARE section of PL/SQL
DECLARE
Cut_name VERCHAR2 (20) NOT NULL := ‘tom jones’:
Same_name cut_name\TYPE:
Which statement is correct about the above snippets of code?
A.
The variable inherits only the data type from the CUT_NAME variable.
B.
The same_name variable inherits only the data type and default value from the CUT_NAME
variable.
C.
The 3ake_nake variable inherits the data type, constraint, and default value from the
CUT_NAME variable.
D.
The 3ake_nake variable inherits only the data type and constraint from the CUT_NAME
variable resulting in an error
Explanation:
executed below block and got the error:
Error report:
ORA-06550: line 3, column 11:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
06550. 00000 – “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:
DECLARE
Cut_name varchar(20) not null:= ‘tom jones’;
Same_name cut_name%TYPE;
begin
dbms_output.put_line(‘Cut_name: ‘||Cut_name);
dbms_output.put_line(‘Same_name: ‘||Same_name);
end;
thus only the datatype and constraint is inherited.
D is the answer
hi,
glad for your answer,
i think tha the mistake is generated by the second variale
so, i’ve put a value also to that and i’ve seen tha also the defalut value is inherited
i think it’s C
Piero
D
c
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/type_attribute.htm#LNPLS01352
The %TYPE attribute lets you declare a constant, variable, field,
or parameter to be of the same data type a previously declared variable,
field, record, nested table, or database column. If the referenced item changes,
your declaration is automatically updated.
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.
DECLARE
OUT_NAME VARCHAR2(20) NOT NULL := ‘tom jones’;
same_name out_name%type ;
begin
DBMS_OUTPUT.PUT_LINE( out_name || ‘ ‘ || same_name);
end;
ORA-06550: line 3, column 15:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
DECLARE
OUT_NAME VARCHAR2(20) NOT NULL := ‘tom jones’;
same_name out_name%type := ‘abc jones’;
begin
DBMS_OUTPUT.PUT_LINE( out_name || ‘ ‘ || same_name);
end;
tom jones abc jones
SORRY, IT’S D
I THOUGHT C BUT IT SEEMS THAT NEVER AND NEVER
ALSO WITHOUT NOT NULL CONSTRAINT
INHERITS THE DEFAULT VALUES
EVEN IF DOCUMENTATION WRITE THE EXACT CONTRARY,
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/type_attribute.htm#LNPLS01352
The %TYPE attribute lets you declare a constant, variable, field,
or parameter to be of the same data type a previously declared variable,
field, record, nested table, or database column. If the referenced item changes,
your declaration is automatically updated.
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.
DECLARE
TYPE PIPPO IS RECORD (
empid number(6) not null := 1,
deptmame varchar2(30) NOT NULL default ‘sales’);
emprec PIPPO;
pluto_deptMame emprec.deptMame%type;
BEGIN
emprec.empid := 10;
DBMS_OUTPUT.PUT_LINE(’emprec.deptname: ‘ || emprec.deptMame );
DBMS_OUTPUT.PUT_LINE(‘pluto.deptname: ‘ || pluto_deptMame );
END;
ORA-06550: line 11, column 21:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
DECLARE
TYPE PIPPO IS RECORD (
empid number(6) not null := 1,
deptmame varchar2(30) –NOT NULL
default ‘sales’);
emprec PIPPO;
pluto_deptMame emprec.deptMame%type;
BEGIN
emprec.empid := 10;
DBMS_OUTPUT.PUT_LINE(’emprec.deptname: ‘ || emprec.deptMame );
DBMS_OUTPUT.PUT_LINE(‘pluto.deptname: ‘ || pluto_deptMame );
END;
— emprec.deptname: sales
— pluto.deptname:
quindi non eredito il valore di default ma solo il tipo e il contraint
che manda inerrore l’assenza del dato
se non ci est il not null, non va in errore ma comunque non viene ereditato il valore di default
quindi sembra che non venga mai ereditato il valore di default.’
DECLARE
TYPE PIPPO IS RECORD (
empid number(6) not null := 1,
deptmame varchar2(30) := ‘sales’);
emprec PIPPO;
pluto_deptMame emprec.deptMame%type;
BEGIN
emprec.empid := 10;
DBMS_OUTPUT.PUT_LINE(’emprec.deptname: ‘ || emprec.deptMame );
DBMS_OUTPUT.PUT_LINE(‘pluto.deptname: ‘ || pluto_deptMame );
END;
emprec.deptname: sales
pluto.deptname:
Documentation is right, read it carefully, it says NOT DATABASE COLUMN
Piero is correct! It never inherits the default value, please see the example below:
declare
cut_name varchar2(20) := ‘tom jones’;
same_name cut_name%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE (‘name: ‘ || same_name);
END;
PL/SQL procedure successfully completed.
name:
And the NOT DATABASE COLUMN clause should’ve been applied even for the NOT NULL constraint in the initial example, but somehow – given the fact that is DATABASE COLUMN – it inherits the not null contraint, but not the default value (the default value clearly is not supposed to be inherited because of the not null constraint).
So if someone could explain this mistery.. it would be very much appreciated.
Thanks!
P.S. The answer is D.
D
D
D
D
snippet of code gives error ORA-06550
declare
cut_name varchar2(20) not null:=’tom jones’;
same_name cut_name%type;
begin
dbms_output.put_line(same_name);
end;
d