Systems Engineering and RDBMS

Back to the basics: %TYPE and %ROWTYPE declaration

Posted by decipherinfosys on September 10, 2008

Normally in PL/SQL or in any programming language, when we declare a variable, we have to declare its data type as well whether variable is varchar or number or date. Oracle provides special qualifiers %TYPE and %ROWTYPE to declare the variables, which are going to hold values from the database columns or rows specifically. As usual we will start with our example of creating table.

CREATE TABLE TEST
(
ID NUMBER(9),
OBJ_TYPE VARCHAR(5),
OBJ_NAME VARCHAR(30),
NUM_ROWS NUMBER(9),
CREATE_DATE DATE
);

%TYPE: We can use this qualifier to declare the variables, which are going to hold column values of the table. It can also be used to assign the data type to a variable, which is not going to hold the database value but can have same data type as of the column. Assume that we have following table. It always corresponds to specific column of the table. What is the benefit of using %TYPE?

• We don’t have to worry about exact data type of the column for which we are declaring variable.
• Also if later on, data type changes or column length gets increased or decreased, we don’t need to change the program. Oracle takes care of it automatically at run time.

%ROWTYPE: Unlike %TYPE, this qualifier or PL/SQL data type is used for entire row or group of columns, which are specified in cursor. Essentially it represents the record, which corresponds to the row in the database table. Again advantage of using %ROWTYPE is to avoid the declaration of individual variable for each column. One thing to remember is that we have to use dot (.) notation to reference the fields. Also database column and corresponding variable will have the same name and data type.

Following code snippet shows both %TYPE and %ROWTYPE declaration.

DECLARE
/* %TYPE variable declaration */
v_date DATE;
v_obj_name TEST.obj_name%TYPE;
v_numRows TEST.num_rows%TYPE;
/*Cursor*/
CURSOR C1 IS
SELECT ID, obj_type, obj_name, create_date
FROM TEST
WHERE ROWNUM < 2;

/*%ROWTYPE declaration*/
test_Rec c1%ROWTYPE;

BEGIN
OPEN C1;
FETCH C1 INTO test_rec;
Dbms_output.put_line(‘Object Name = ‘ || test_rec.obj_name);
Dbms_output.put_line(‘Object Type = ‘ || test_rec.obj_type);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END;
/

Apart from obvious advantages shown above, it also makes code concise and readable. Another major advantage which people are not aware is that one can insert the record using PL/SQL record type and can update the row as well using record type rather than stating the individual column list. We have covered this in detail in this blog entry.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: