Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Working with Database Objects in PL/SQL

By: FYIcenter.com

Part:   1  2   3  4  5  6 

(Continued from previous part...)

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);
Table created.

DECLARE
  id NUMBER;
  first_name CHAR(10);
BEGIN
  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
END;   
/

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.

How To Resolve Name Conflicts between Variables and Columns?

The best way to resolve name conflicts is to avoid using column names for variables.

How To Assign Query Results to Variables?

If you want to assign results from SELECT statements to variables, you can use the INTO clause, which an extension of SELECT statements for PL/SQL. The sample code below shows some good example on INTO clause:

DECLARE
  total NUMBER;
  now DATE;
  fname VARCHAR2(10);
  lname VARCHAR2(10);
BEGIN
  SELECT COUNT(*) INTO total FROM employees;
  DBMS_OUTPUT.PUT_LINE('Count = ' || TO_CHAR(total));
  SELECT SYSDATE INTO now FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('Now = ' || TO_CHAR(now, 'SSSSS'));
  SELECT first_name, last_name INTO fname, lname
    FROM employees
    WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('Name = ' || fname || ' ' || lname);
END;   
/
Count = 107
Now = 82375
Name = Steven King

Can You Assign Multiple Query Result Rows To a Variable?

You can use "SELECT ... INTO variable" to assign query results to variables. But what happens if the SELECT statements return multiple rows? The answer is that you will get a run time error. The following tutorial exercise shows this error condition:

DECLARE
  fname VARCHAR2(10);
  lname VARCHAR2(10);
BEGIN
  SELECT first_name, last_name INTO fname, lname 
    FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('Name = ' || fname || ' ' || lname);
  SELECT first_name, last_name INTO fname, lname 
    FROM employees WHERE employee_id > 100;
  DBMS_OUTPUT.PUT_LINE('Name = ' || fname || ' ' || lname);
END;
/
ORA-01422: exact fetch returns more than requested number 
  of rows
ORA-06512: at line 8
Name = Steven King

(Continued on next part...)

Part:   1  2   3  4  5  6