Ignored Errors?

[townsendEbmud]

One of my users on the latest version runs a SQL that returns no rows. However, my tech guy runs the same SQL on his machine and gets a correct error message that percolates out of a function call that a "single row query returns multiple rows". SQL*Plus returns this error message also. The result on my tech guy's machine is the correct one. Can anyone suggest why on the user's machine the result shows no rows returned when it should show an error message? This wrongness happens in both the command window and the SQL window. TIA.

 

[Milan]

I have similar problem with latest version on Windows Vista. It seems that PL/SQL Developer is picking up OCI DLL libraries from wrong Oracle Home directory (not the one that I specified in preferences), which are not working properly with the database version.

I reported it as a bug, but got no reply so far.

I had another problem with 7.1.3. My advice is not to upgrade.

 

[Marco Kalter]

However, my tech guy runs the same SQL on his machine and gets a correct error message that percolates out of a function call that a "single row query returns multiple rows".

This is indeed a known problem. Somehow this situation is handled as "no more rows".

It seems that PL/SQL Developer is picking up OCI DLL libraries from wrong Oracle Home directory (not the one that I specified in preferences)

If all is well you received the following e-mail:

--------------------

Hello Milan,

We should have this issue fixed in this version:

http://www.allroundautomations.com/download/plsqldev713.1381.exe

Let me know how this turns out.

 

[townsendEbmud]

Wow, this almost took my breath away. Almost. Sadly it did not solve my problem. I downloaded and installed 7.1.3.1381 and the problem continues. My tech guy put together this script to demonstrate the problem:
-------------
create or replace function test_fc
return varchar2 as
v_dummy varchar2(30);
begin
select table_name into v_dummy from all_tables;
return v_dummy;
end;
/

select test_fc from dual;
-------------
The "select ... into..." statement returns too many rows, so the function should fail and the select from dual should return an error message. It does so for a 10g client install, but not for a 9i client. Here is some environment info for 10g where the error is returned correctly:
-------------
DLLs
D:\oracle\product\10.2.0\db_1\bin\oci.dll
Using
Home: OraDb10g_home1
DLL: D:\oracle\product\10.2.0\db_1\bin\oci.dll
OCI: version 9.2
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
-------------
And here is the same info for the 9i client where the error is not returned and the response is "0 rows selected in 0.344 seconds"
-------------
DLLs
C:\Oracle\Ora92\bin\oci.dll
Using
Home: OraHome92
DLL: C:\Oracle\Ora92\bin\oci.dll
OCI: version 9.2
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
-------------
I consider this to be an extremely dangerous bug because the invalid response (0 rows returned) looks so reasonable.

Please help ASAP. Thanks!

 

[Stew Stryker]

I see the same thing on oracle 10gR1, running PSD 7.1.3.

 

[townsendEbmud]

On a further note, one thing we observed trying to chase down this problem was that PSD used the oci.dll that it found in my PATH regardless of the oci.dll specified in the settings. Mine was the only PC around that had this directory in its path, so my PC behaved differently from other PCs we were testing until I removed the directory from my PATH. It seems like PSD should ignore the PATH if I specify an oci.dll in the Preferences.

 

[Marco Kalter]

This is indeed a bug in 7.1.3.1380. The current 7.1.3.1381 build fixes this.

 

[townsendEbmud]

I assume you mean that 7.1.3.1381 fixes the oci.dll vs. PATH problem, not the "ignored errors" problem, which still exists in .1381.

 

[Marco Kalter]

That is indeed correct. We do not yet have a fix for the "ignored errors" problem.