Just a quick gotcha I stumbled on in PB code. This is about SELECT..INTO. As you know, SELECT..INTO expects one row and it works well as long as there is only one row.While researching another issue, I saw the below SQL in one of the objects in our application. of_get_cutoffdate .... // Date ld_CutOff.... SELECT TO_DATE ( PARM_VALUE , 'DD-MON' ) INTO :ld_Cutoff FROM SYSPARM WHERE TRIM ( PARM_NAME ) ='CUTOFF_DATE'; .... There are 4 rows for this parameter in the SYSPARM table. I originally anticipated ld_Cutoff to be NULL (or 01/01/1900 - default for date) as I thought this SQL will error out because of too many rows. To my surprise, the variable had the value from the first row. The gotcha here is that PB does not throw any exception when a DB error occurs. Even though the above SQL resulted in an error, PB silently fetches the first row's value into the variable and sets SQLCODE to -1 and error message in SQLErrText. (See attachment). Even though the host variable got value from 1st row, this is not guaranteed and is not a documented feature in PB or Oracle. If we anticipate more than 1 row for the SELECT, then try to add conditions (rownum, cursors) to filter to 1 row. (Oracle SQL[PL/SQL] behavior: In Oracle the same SQL would generate a TOO_MANY_ROWS exception . There also the SQL seems to be setting the host variable with the value from the 1st row, but Oracle does not recommend relying on this. See usage notes in reference 1 below) Irrespective of the # of rows fetched, we always need to add proper error checks around the SQL. In PB, check the SQLCA.SQLCode, and print messages using SQLCA.SQLErrText. (In Oracle, handle TOO_MANY_ROWS exception). Note: A lot of our SQLs getting SYSPARM are done this way. PR4297 was created to add corrections to these. Reference: 1. Oracle PL/SQL User Guide |
Friday, December 9, 2011
PB Gotcha - Embedded (inline) SQL - SELECT..INTO
Subscribe to:
Post Comments
(
Atom
)
The reason why PB does not throw an error because, PB never treat the sql as an implicit cursor like a DB. It treats it like an regular sql and populates the first value in the bind variable.
ReplyDeleteHi William, Thanks a lot for pointing that out. I turned on DBTrace and noticed PB actually opened an explicit cursor and populated the variable. Of course, we are expecting only 1 row in a SELECT..INTO. When the select returns more than 1, SQLcode is set to -1 (and SQLErrm = "Select returned more than 1 row), and the variable is set to the first value in the result set. So, unless we check the SQLCODE, there is no way to tell there was an error.
ReplyDeleteThis was the gotcha for me in the application I maintain. The developer simply got SYSPARM values in this manner and there was no error checks. So, when there was a scenario where multiple rows got returned, the first row's value was picked which turned out to be incorrect (the user was expecting the value from latest row added)! I added the error checks and also qualified the SQL (with from and to date range), so the SELECT never returns multiple rows in the 1st place.
FYI, Here is my question and some answers at stackoverflow.com.
Thanks
Sam