Using% TYPE on a record field in PL / SQL
This drove me a little crazy:
DECLARE
TYPE AttrValueRec IS RECORD (
attr VARCHAR2(40),
val VARCHAR2(2000),
inst NUMBER(4)
);
FUNCTION create_attrval(attr AttrValueRec.attr%TYPE,
val AttrValueRec.val%TYPE,
inst AttrValueRec.inst%TYPE := 1)
RETURN AttrValueRec IS
attr_value AttrValueRec;
BEGIN
attr_value.attr := attr;
attr_value.val := val;
attr_value.inst := inst;
RETURN attr_value;
END;
BEGIN
NULL;
END;
Using %TYPE
in a record field doesn't work. It throws the following error:
ORA-06550: line 8, column 36:
PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "ATTRVALUEREC.ATTR"
ORA-06550: line 8, column 5:
PL/SQL: Item ignored
With an explicit type definition, it works again:
DECLARE
TYPE AttrValueRec IS RECORD (
attr VARCHAR2(40),
val VARCHAR2(2000),
inst NUMBER(4)
);
FUNCTION create_attrval(attr VARCHAR2,
val VARCHAR2,
inst NUMBER := 1)
RETURN AttrValueRec IS
attr_value AttrValueRec;
BEGIN
attr_value.attr := attr;
attr_value.val := val;
attr_value.inst := inst;
RETURN attr_value;
END;
BEGIN
NULL;
END;
Can someone explain to me why this is not working? Is there a way to refer to the type declared in the entry definition instead of explicitly defining it again in the function?
Thanks.
source to share
look at the documentation . % TYPE and% ROWTYPE - Use only column references . but you are trying to make a user reference type.
the solution defines your pl / sql type with% TYPE-referer on the database column and then creates a function with the parameters referring to one database column.
UPDATE
its not entirely true because the comment by the lead commentator is helpful. summary% TYPE and% ROWTYPE can refer not only to table columns. referencing "real" objects like variables and cursors are fine too.
source to share
You need to create a variable of your type to reference the attributes.
Add this after the type declaration and before the function.
attrib_value AttribValueRec;
Then, in the function header, you can refer to the attribute type in your function like this:
attr attrib_value.attr%TYPE;
source to share