SQL Worksheet (from within cloud control) wrong results bug?



I don't know how widely used the 'sql worksheet' feature is from within cloud control but I've discovered something which i think is a bug (I've opened an SR with Oracle to confirm) which is not immediately obvious but is giving wrong (null) results.

I'll show a quick demo - it would be useful to know if anyne else sees the same behaviour as I'm sure this used to work OK in earlier versions (i'm on 12.1.0.3 OMS and 12.1.0.5 db plugin) but i don't have anything older available to test with now.

First we navigate to the sql worksheet pop out window:



Then we run a simple query - which works fine



Now we add a where clause - and get no rows returned


Now we track down what SQL it ran


It seems to have converted my literal to a bind but the bind must be null or have a wrong value

If i use bind variables in the sql worksheet screen then everything works fine - see below

So it seems any sort of literal clause in a sql worksheet screen is rewritten to binds, but the binding is not working and we are getting empty result sets.

Lets see what oracle support say.

=======UPDATE========

Ok after some digging it seems i got to the bottom of this - the problem occurs when cursor_sharing is set to 'FORCE' in the database the sql worksheet is connected to. Switching it to 'EXACT' the problem goes away.

So as with many other random issues make sure that cursor_sharing is EXACT........


0 comments:

Post a Comment