|
|
 |
| Purpose |
|
To inform migration specialists of the difference between T_SQL and PL/SQL singleton SELECT INTO statements.
|
| Scope & Application |
|
The audience for this document is Oracle customers, Oracle partners and Oracle staff.
|
| Singleton Select Statements (Select Into) |
SELECT INTO statements have a logical difference between T-SQL and PL/SQL.
SQL Server continues execution no matter what the outcome.
For certain situations Oracle will throw exceptions.
To avoid this problem a cursor for loop should be used.
Example:
SELECT col1 INTO @var FROM table1
Scenarios:
If more than 1 value is returned
- T-SQL places the last value returned into the variable
- PL/SQL throws an exception
If no values are returned
- T-SQL places no value in the variable
- PL/SQL throws an exception
If there is a chance that more than 1 or no rows are returned from the select.
The SELECT INTO statement should be changed to a cursor for loop.
Now the PL/SQL replicates T-SQL exactly and no exceptions are thrown.
|
| Before |
After |
| SELECT col1 INTO @var FROM table1
|
SELECT col1 INTO @var FROM table1
FOR rec IN ( SELECT col1 FROM Table1)LOOP
var := rec.col1;
END LOOP;
|
| Related Documents |
| <>Migration of Singleton Select Statements
|
|
|
|
|
Bookmark
