Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Beginner
May 4, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Declaring Data Variables in Oracle PL/SQL Blocks

In a PL/SQL block, if you are querying a database table, you need to declare data variables to hold column values returned from the query. The variables can be declared of the same explicit datatype as the column in the table.

Let's say we create a table:

 create table dept ( deptno number(2), deptname varchar2(20));
Now we have a procedure Proc1, which queries the "dept" table to get the department name for a given deptno. The code is as follows:
 create or replace procedure proc1 
	v_deptname	varchar2(20);
	select deptname into v_deptname
	from   dept 
where deptno = 10;
	when no_data_found then
         raise_application_error(-20001,'Dept 10 does not exist');
This procedure will work fine until the table definition gets altered, such that the column "deptname" reaches varchar2(30) . If the department name for deptno =10 is longer than 20 characters in the table, then the procedure will raise an unhandled exception of "Value Error" because the variable v_deptname cannot hold more than 20 characters. If you have lots of procedures with v_deptname declared as varchar2(20), you will need to modify all the procedures.

To get around this painful process, you should declare the variables as the column type rather than the explicit datatype of the column. See the declaration below:

 v_deptname	 dept.deptname%TYPE;
Now if the column size is altered, you are not required to change any of the procedures—only recompile them. The %TYPE will automatically assign the datatype of the column to v_deptname.
Jayanta Sengupta
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date