%TYPE and %ROWTYPE Anchored Declaration

Contact Us or call 1-877-932-8228
%TYPE and %ROWTYPE Anchored Declaration

%TYPE and %ROWTYPE Anchored Declaration

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.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into_value_error.sql
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;

Code Explanation

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

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into_value_error_resolved.sql
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;

Code Explanation

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.

Next