Home arrow Oracle arrow 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.

  1. Database Interaction with PL/SQL, part 1
  2. SELECT…INTO Statement
  3. %TYPE attribute
  4. %ROWTYPE attribute
  5. Using Other DML Commands
By: Jagadish Chatarji
Rating: starstarstarstarstar / 36
May 24, 2005

print this article



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.

     v_ename emp.ename%Type;
     v_empno emp.empno%type := &empno;
     select ename into v_ename
     from emp
     where empno=v_empno;
     dbms_output.put_line('Name: '||v_ename);

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:

     v_empno emp.empno%type := &empno;
     v_ename emp.ename%type;
     v_sal emp.sal%type;
     v_job emp.job%type;
     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);

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: