Using %TYPE on a record field in PL/SQL

Question

This has been driving me crazy for a while:

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 on a record field does not seem to work. It produces 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

While explicitly defining the type again works:

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 it doesn't work? Is there a way to refer to the type declared in the record definition instead of explicitly defining it again in the function?

Thanks.

Answer 1

look at documentation. %TYPE and %ROWTYPE - only use to refer database columns. but you try to make referer to user type.

solution is define your pl/sql type with %TYPE-referer on a database column, and then create function with parameters that refer to the same database column.

UPDATE

its not full truth because lead commentator post usefull idea. summary %TYPE and %ROWTYPE can refer not only to table columns. refer ot "real" objects like variables and cursors are good too.

Answer 2

You need to actually create a variable of your type to refer to the attributes.

Add this after your type declaration and before the function.

  attrib_value  AttribValueRec;

Then in your function header you can reference the type of the attributes in your function like this:

  attr  attrib_value.attr%TYPE;