Tutorial: Changing Declarations to Use the %TYPE Attribute

This tutorial shows how to use the SQL Developer tool Edit to change the declarations of the variables, constants, and formal parameters of the EMP_EVAL.calculate_score function (shown in "Tutorial: Declaring Variables and Constants in a Subprogram") to declarations that use the %TYPE attribute.

To change the declarations in calculate_score to use %TYPE:

1. On the Connections tab, expand the hr_conn information by clicking the plus sign (+) to the left of the hr_conn icon.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Expand EMP_EVAL.

   A list appears.

4. Right-click EMP_EVAL Body.

   A list of choices appears.

5. Click Edit.

   The EMP_EVAL Body pane appears, showing the code for the package body.

6. In the code for the function, make the changes shown in bold font:

     FUNCTION calculate_score ( evaluation_id IN SCORES.EVALUATION_ID%TYPE
                               , performance_id IN SCORES.PERFORMANCE_ID%TYPE)
                               RETURN NUMBER AS
     n_score       SCORES.SCORE%TYPE;
     n_weight      PERFORMANCE_PARTS.WEIGHT%TYPE;
     max_score     CONSTANT SCORES.SCORE%TYPE := 9;
     max_weight    CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
   

7. Right-click EMP_EVAL.

   A list of choices appears.

8. Click Edit.

   The EMP_EVAL pane opens, showing the CREATE PACKAGE statement that created the package.

9. In the code for the function, make the changes shown in bold font:

   FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                           , performance_id IN scores.performance_id%TYPE)
   

10. Right-click EMP_EVAL.

    A list of choices appears.

11. Click Compile.