Home >> FAQs/Tutorials >> Oracle Tutorials
Oracle Tutorials - Variable Names Collide with Column Names
(Continued from previous topic...)
What Happens If Variable Names Collide with Table/Column Names?
When a variable name collides with a column name, PL/SQL will use it as the variable
if it is used where variable is allowed; It will be used as the column, if it is used where
variable is not allowed but column is allowed. Here is a good example of variable names
collide with column names:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL);
id := 29;
first_name := 'Bob';
INSERT INTO student VALUES(id, first_name, 'Henry');
first_name := 'Joe';
INSERT INTO student VALUES(id+1, first_name, 'Bush');
first_name := 'Fyi';
UPDATE student SET first_name = first_name WHERE id = 29;
-- 1 row updated
-- Both 'first_name's are treated as column names
UPDATE student SET first_name = first_name
WHERE id = id+1;
-- 0 rows updated
-- Both "id"s are treated as variable names
DELETE FROM student WHERE id = id;
-- 2 rows deleted
SELECT * FROM student;
0 rows selected
Noticed that "id = id+1" in the WHERE clause will be evaluated to FALSE,
because both "id"s are treated as variables. Similarly "id = id" will also
be evaluated to TRUE in the WHERE clause. But both "first_name"s
in the SET clause will be treated as column names.
(Continued on next topic...)
- Can DML Statements Be Used in PL/SQL?
- Can DDL Statements Be Used in PL/SQL?
- Can Variables Be Used in SQL Statements?
- What Happens If Variable Names Collide with Table/Column Names?
- How To Resolve Name Conflicts between Variables and Columns?
- How To Assign Query Results to Variables?
- Can You Assign Multiple Query Result Rows To a Variable?
- How To Invoke Built-in Functions in PL/SQL?
- How To Retrieve the Count of Updated Rows?
- What Is the Implicit Cursor?
- How To Assign Data of the Deleted Row to Variables?
- What Is a RECORD in PL/SQL?
- How To Define a Specific RECORD Type?
- How To Define a Variable of a Specific RECORD Type?
- How To Assign Values to Data Fields in RECORD Variables?
- How To Retrieve Values from Data Fields in RECORD Variables?
- How To Define a Data Field as NOT NULL?
- How To Define a RECORD Variable to Store a Table Row?
- How To Assign a Table Row to a RECORD Variable?
- How To Insert a RECORD into a Table?
- How To Update a Table Row with a RECORD?
- How To Define a Variable to Match a Table Column Data Type?