Home

Description

The %TYPE and %ROWTYPE should not be used in package specification as they are not allowed in the package specification. These two attributes are used to declare a variable or a column of a table. They are used to store the data type of the variable or the column. The %TYPE attribute is used to store the data type of a variable and the %ROWTYPE attribute is used to store the data type of a column of a table. Using these two attributes in the package specification will result in an error.

Key Benefits

• No Dependency: %TYPE and %ROWTYPE should not be used in package specification as they create a dependency on the table structure, which can lead to unexpected results.
• No Unnecessary Code: Using %TYPE and %ROWTYPE in package specification can lead to unnecessary code being generated, which can be difficult to debug.
• Easier Maintenance: By avoiding %TYPE and %ROWTYPE in package specification, maintenance of the code is easier as changes to the table structure will not affect the package.

Non-compliant Code Example

CREATE OR REPLACE PACKAGE PKGCUSTOMER IS

    TYPE CustomType IS RECORD (customVariable CustTable.CustColumn%TYPE); --Non compliant code (%TYPE and  %ROWTYPE is used in package specification)

    FUNCTION GetCustomerName(param1 IN CustTable.CustColumn%TYPE,param2 IN CustTable.CustColumn%ROWTYPE) RETURN VARCHAR2; --Non compliant code (%TYPE and  %ROWTYPE is used in package specification)

    FUNCTION GetCustomerAddress(param1 IN CustTable%ROWTYPE) RETURN VARCHAR2; --Non compliant code (%TYPE and  %ROWTYPE is used in package specification)

END

Compliant Code Example

CREATE OR REPLACE PACKAGE PKGCUSTOMER IS

    TYPE CustomType IS RECORD (customVariable VARCHAR2(100)); --Compliant code

    FUNCTION GetCustomerName(param1 IN  VARCHAR2,param2 IN  VARCHAR2) RETURN VARCHAR2; --Compliant code
    
    TYPE ve_rowtype IS RECORD (
    vecol1 NUMBER,
    vecol2 VARCHAR2(30)
    );

    FUNCTION GetCustomerAddress(param1 IN ve_rowtype) RETURN VARCHAR2; --Compliant code

END