OT: SQL Cursor Parameter Behavior in 7.x vs 8.x

From: Larry Elkins <elkinsl_at_flash.net>
Date: Wed, 11 Jul 2001 17:17:22 -0700
Message-ID: <F001.003482D9.20010711172050@fatcity.com>

Listers,

Anyone have access to 7.x to test this for me? I had to head out to help a former client tune a large "batch" package that headed South on them after they went live over the weekend, upgrading from 7.3.4.3 to 8.1.7.1. In the case of one SQL statement, the CBO was making a very poor decision regarding the driving table. Easy enough to identify and fix after generating a trace and running tkprof. I would have liked to play with the statistics gathering process to see if we could fix the problem, but, since they were in a hurry, I just went with the ORDERED hint.

But, there was another "bad" SQL statement that jumped out. I will use EMP as an example. For whatever reason, the programmer had named a cursor parameter the *same* as an actual column name (I know, not very smart on the coder's part):

cursor c1 (empno number) is
select empno
from emp e
where e.empno = empno;

V8 was treating it as the column equal to itself. Hence, a full table scan and returning all rows. In their real life example, the variable was against a PK on a table joining back through 3 other tables. Oracle did FTS's on all tables and a combination of HJ's and MJ's. And each of those tables had millions of rows. And that's the behavior I would expect since there was "no criteria" to speak of.

But, this is a nightly batch process and problems had never been encountered before. So, this is making me think that V7 was treating the right side as a variable and using the value passed in. Best I remember (I don't have access to Metalink right now) various notes warned about the above being erratic -- how would Oracle know if you meant the column or the variable? And the person should never have coded it the way they did. I always prefix my parameters with "p_", p_empno for example, and type them to the column. No confusion that way. I am curious if someone could test this against V7. For all I know, maybe since the "column name" was prefixed with the alias and the variable wasn't, they were just getting *lucky* in V7. Or, maybe it is truly erratic. And maybe it is still erratic in V8 (though an example I wrote exhibited the same behavior). And I left wondering how many other cases they might have in their code where a similar coding technique was used. Ouch! I hope for their sake there aren't that many ;-)

Anyway, I'm curious if someone can check this out against V7. Here is an example script I ran against 8.1.7 here at home:

  1 declare
  2 cursor c1 (empno number) is
  3 select empno
  4 from emp e
  5 where e.empno = empno;
  6 v_empno number;
  7 begin
  8 open c1 (4567);
  9 fetch c1 into v_empno;
10 dbms_output.put_line(to_char(v_empno)); 11 close c1;
12* end;
SQL> /
7369

PL/SQL procedure successfully completed.

There is no employee "4567" and the output makes it obvious that the SQL was treated as column = column, get all rows. The obvious solution is to avoid the ambiguity in how the cursor parameter is named. But I am still interested in how the above would be handled against V7.

Regards,

Larry G. Elkins
elkinsl_at_flash.net

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Larry Elkins
INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jul 11 2001 - 19:17:22 CDT