HomeOracle Page 3 - Database Interaction with PL/SQL, part 1
%TYPE attribute - Oracle
This first article in a series focusing on database interactions with Oracle PL/SQL serves as a good introduction to the topic. It mainly focuses on the basics of retrieving information from the Oracle database using the SELECT..INTO statement, as well as two attributes, namely "%TYPE" and "%ROWTYPE." Readers should have some knowledge of RDBMS, Oracle SQL, and some very fundamental grounding in PL/SQL.
In the above program, we declared a variable "v_ename" as being of type "varchar(10)." What if we don't know the data type (or data length) of the field "ename"? In general, we will switch back to the SQL prompt and issue a DESCRIBE statement to show all of the columns and datatypes (and of course the lengths, too) of a particular table. What if DBA increases the width of "ename" column without informing you (which happens very often)? Your program may not work correctly.
So, we need to have a special mechanism with which a variable should find the data type and width dynamically and automatically at runtime. This is where %TYPE comes in. Let us take a closer look at it, with a simple example.
Declare v_ename emp.ename%Type; v_empno emp.empno%type := &empno; begin select ename into v_ename from emp where empno=v_empno; dbms_output.put_line('Name: '||v_ename); end;
This is very similar to the first example, except for the declaration section. "v_ename" is declared as being of type "emp.ename%type." What does it mean? It means that the data type of "v_ename" would be the same as the data type of the "ename" column in the "emp" table. Similarly, the other variable, "v_empno," is also declared.
We can declare multiple variables and fetch accordingly, as shown in the following example:
Declare v_empno emp.empno%type := &empno; v_ename emp.ename%type; v_sal emp.sal%type; v_job emp.job%type; begin select ename,sal,job into v_ename,v_sal,v_job from emp Where empno=v_empno; dbms_output.put_line('Name: '|| v_ename); dbms_output.put_line('Salary: '|| v_sal); dbms_output.put_line('Job: '|| v_job); end;