A common problem in programming occurs when values do not fit when assigned to a variable. This can result in a runtime error or a loss of data. The use of %TYPE and %ROWTYPE anchored declarations in PL/SQL can eliminate many of these potential problems. %TYPE is used when referencing a single column in a table; %ROWTYPE is used to reference every available column in a given table. The following demonstrations show how the problem can manifest itself and how the use of %TYPE addresses the issue.
declare v_first_name varchar2(5); v_last_name varchar2(100); begin select first_name, last_name into v_first_name, v_last_name from employees where employee_id = 100; dbms_output.put_line(v_first_name || ' ' || v_last_name); end;
The v_first_name variable has been modified in this example so that it is too small to hold the value retrieved from the table. An exception is raised: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
declare v_first_name employees.first_name%type; v_last_name employees.last_name%type; begin select first_name, last_name into v_first_name, v_last_name from employees where employee_id = 100; dbms_output.put_line(v_first_name || ' ' || v_last_name); end;
Variables that will be populated with the contents from table columns should be declared in the manner shown in this example. This eliminates the need to consult the table definition and declare a variable size that matches the column. Since %TYPE uses the datatype of the database column itself, it ensures that the variable in use in a PL/SQL statement will be able to hold the contents of the variable even if the column size changes at some point in the future.