Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: Declaring and Initializing Variables
Oracle Tips by Burleson

SPL supports all of the data types that I covered in Chapter 2.  It also supports some unique typing when declaring variables.

A variable is declared by giving it a name and following it with the data type. Some variables can be declared CONSTANT and some variables can be optionally assigned a value in the declaration.  The declaration ends with a semi-colon.

To declare a variable named v_first_name of type VARCHAR2 and length 30, I would use:

v_first_name VARCHAR2(30);

I could declare the same variable and assign it the value "Lewis".

v_first_name VARCHAR2(30) := 'Lewis';

A CONSTANT is a variable that does not change its value during the course of a program.  As a matter of a fact, by using the CONSTANT keyword, you prevent the program from being able to change the value.  The constant variable’s value MUST be assigned when it is created:

v_first_name CONSTANT VARCHAR2(30) := 'Lewis';

Numbers and Booleans are just as easy to declare and assign as a Varchar2.

v_number_1 NUMBER;
v_number_2 NUMBER(5,3) := 42.5;
v_number_3 CONSTANT NUMBER(5,3) := 42.5;
v_bool_1 BOOLEAN;
v_bool_2 BOOLEAN := TRUE;

Dates in SPL are implemented as timestamp(0), which is a DATE and TIME with no partial seconds; in other words just like an Oracle date data type.

Dates and timestamps are declared just as easily as character or numeric data but they get a little more complicated when you assign them a value:

v_date_1 DATE;
v_date_2 DATE := TO_DATE('01-JAN-2006 01:24:55', 'DD-MON-YYYY HH24:MI:SS');
v_date_3 CONSTANT DATE :=
             TO_DATE('01-JAN-2006 01:24:55', 'DD-MON-YYYY HH24:MI:SS');

v_timestamp_2 TIMESTAMP(6) :=
            TO_TIMESTAMP('01-JAN-2006 01:24:55.123456',
v_timestamp_3 CONSTANT TIMESTAMP(6) :=
            TO_TIMESTAMP('01-JAN-2006 01:24:55.123456',

When assigning a literal value to a date or timestamp, always use the appropriate function to ensure that it is formatted as you expect it to be.  As we discovered in the last chapter, TO_DATE converts a character string to a date and TO_TIMESTAMP converts a character string to a timestamp.

As you can see from these examples, := is the assignment operator.  This is also the assignment operator when you are writing code:


  v_first_name VARCHAR2(30);
  v_age NUMBER;
  v_first_name := 'Lewis';
  v_age := 21;  -- yeah, I wish!

There is a special way to declare variables in an SPL program called an "anchored declaration".  An anchored declaration is identified by the percent sign (%) followed by either the keyword TYPE or the keyword ROWTYPE.  I will cover the ROWTYPE keyword in greater detail in the section on records below but it is declared just like the TYPE.

An anchored declaration is called that because it is anchored to the database.  Unlike a boat anchor that holds you in place, this is an anchor in the sense of a relationship.  When you use an anchored declaration, you relate the data type in your program to a data structure in the database.  For %TYPE, you refer to the declared structure of a column in the database.  The %ROWTYPE declaration refers to the entire table structure (all columns) of a database table.

The greatest benefit of using %TYPE or %ROWTYPE is that since it is related to a database object, you are ensured that should the database object change, your code will automatically pick up that change when you recompile.  If you alter the data type of a column for %TYPE, or add or remove a column for %ROWTYPE, your SPL will be aware of that.  If you drop a column referred to by a %TYPE, your code will no longer compile.  If you drop a table or view referred to by a %TYPE or a %ROWTYPE, your code will no longer compile.

The format of the TYPE anchor is:

<variable name > <table name>.<column name>%TYPE ;

The format for ROWTYPE is:

<variable name> <table name>%ROWTYPE; 

You can see this in the example below:


  v_name emp.ename%TYPE;
  v_emp_record emp%ROWTYPE;


  v_name := 'Lewis';
  v_emp_record.ename := 'Lewis';

To initialize the variable declared with %TYPE, you access it as you would any other variable.  It can be a constant and it can be initialized as part of the declaration.

The ROWTYPE variable is accessed differently.  When you use ROWTYPE, you are creating an SPL "record".  An SPL record is different than a database record but is very similar in concept.


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter