Power to Build

Home » CodeProject » PB Gotcha – Embedded (inline) SQL – SELECT..INTO

PB Gotcha – Embedded (inline) SQL – SELECT..INTO

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


2 Comments

  1. William says:

    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.

    • SamV says:

      Hi 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.

      This 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

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: