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.
SELECT TO_DATE ( PARM_VALUE , ‘DD-MON’ )
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.