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

Larry,

this is a side effect of having query_rewrite_enabled=true as a possibility.
by allowing query rewrites, the aliases have to be unique across the query.
I experienced problems with non-unique aliases when migrating from 7.3 to 8.1.

To me, this is not a surprise.

Paul

Larry Elkins wrote:
>
> 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: Paul Drake
  INET: paled_at_home.com

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