|
Problem IC74423
|
Status: Closed |
PL/SQL STATIC CURSOR DECLARATION RETURNS SQLCODE -153, SQLSTATE 42908 |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Declaring a PL/SQL static cursor may incorrectly return SQLCODE
-153, SQLSTATE 42908. This happens if the query includes an
ORDER BY column that is not in the select list and the select
list includes an expression with the same column name. For
example:
create table T ( col1 int )
DB20000I The SQL command completed successfully.
create procedure P
is
CURSOR cur1 IS
SELECT col1+1 col1 from T order by T.col1; --
begin
return 0; --
end
SQL0153N The statement does not include a required column
list. LINE NUMBER=3. SQLSTATE=42908 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2LUW *
****************************************************************
* PROBLEM DESCRIPTION: *
* Declaring a PL/SQL static cursor may incorrectly return *
* SQLCODE *
* -153, SQLSTATE 42908. This happens if the query includes an *
* ORDER BY column that is not in the select list and the *
* select *
* list includes an expression with the same column name. For *
* example: *
* *
* create table T ( col1 int ) *
* DB20000I The SQL command completed successfully. *
* *
* create procedure P *
* is *
* CURSOR cur1 IS *
* SELECT col1+1 col1 from T order by T.col1; -- *
* begin *
* return 0; -- *
* end *
* SQL0153N The statement does not include a required column *
* list. LINE NUMBER=3. SQLSTATE=42908 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 9.7 fixpack 5 *
**************************************************************** |
| Local Fix: |
Change the column name of the expression in the select list. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
Change the column name of the expression in the select list. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.02.2011
01.02.2012
01.02.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
