Re: Why no implicit cursors ? (was Re: Problem with PL/SQL / nextval)

This is one of those good ideas which has been surrounded by ignorance and taken on more significance than it really deserves.

It is generally a good idea to be in control of what you are doing, and it is possible to run into unexpected problems if you use lots of implicit cursors, and there is (in some cases) a performance overhead.

But my opinion is that the issue is only a problem if you are using PL/SQL in an unsuitable way anyway.

To address the issues:
a) Performance:

        Historically an implicit cursor did TWO fetches from the database,
        so that it could report 'too many rows' if the second fetch returns
        a row.  This required a second round-trip across the network.

        But this was only a 'performance issue' when compared to an explicit
        cursor that does NOT do a second fetch - and thus fails to trap a
        possible error.

        However, various changes  in how SQL is executed in later versions
        of Oracle were introduced to handle such network costs, and if you
        trace the action of an implicit cursor (under 8.0.4 at least) it
manages
        to return 'too many rows' whilst executing a single FETCH at the
        database end.

        Scratch one old wives' tale about performance on implicit cursors.


b)    Cursors closing;
        Examine the following code fragment with its output:
        SQL> l
          1  declare
          2   m_junk number;
          3   m_text varchar2(60);
          4  begin
          5   select 1 into m_junk from dual;
          8   select sid into m_junk from v$session where audsid =
          9    (select userenv('sessionid') from dual);
         10   for l_text in (select sql_text from v$open_cursor where sid =
m_junk) loop
         11    dbms_output.put_line(l_text.sql_text);
         12   end loop;
         13* end;
        SQL> /
        SELECT SQL_TEXT   FROM V$OPEN_CURSOR  WHERE SID = :b1
        SELECT SID   FROM V$SESSION  WHERE AUDSID =  (SELECT
        USERENV
        SELECT 1   FROM DUAL
        declare  m_junk number;  m_text varchar2(60); begin  select

        PL/SQL procedure successfully completed.

        SQL> spool off

        When the cursor loop opens, the cursor 'select 1 from dual'
        should have been closed - but it isn't.  The theory is that if
        you rewrote this as an explicit cursor it would:  unfortunately
        on my copy of 8.0.4 of you change
            select 1 into m_junk from dual;
        into
         declare
             cursor c1 is select1 from dual;
          begin
              open c1;
              fetch c1 into m_junk;
              close c1;
         end;

        You still see the same result !!
        Scratch another theory of implicit vs. explicit cursors.  (Again,
        this may have been true in the past, and may become true in
        the future).

        The main benefit of explicit cursors is that they can
        have parameters, so you can have just one open, but
        use it for lots of different calls.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Marc wrote in message <7pipov$6h8$1_at_vkhdsu24.hda.hydro.com>...
>
>I've already heard different times that an implicit cursor should not be
>used in PL/SQL, but noone can give me a good reason.
>I heard that implicit cursors are slower : I tested it, and it appeared
that
>explicit cursors are slower, even if only one row is returned !
>Someone else told that implicit cursors are never closed : I tested it, and
>that is not true either (IMO explicit cursors have a higher risk of not
>being closed)...
>
>So, why should I avoid 'select ... into ..' in PL/SQL ?
>
>(I performed the tests on Oracle 7.3.3)
>
>Marc
>
>Ron Reidy wrote in message <37BBF555.3DBF1A41_at_uswest.net>...
>>Niklas Mehner wrote:
>>
>>
>>This is a PL/SQL issue. PL/SQL always executes a SELECT ... INTO twice
>>in order to throw this exception. To repevent this, create an explicit
>>cursor for the query, open it, fetech from it, and close it.
>>
>>Some advice on PL/SQL...
>>
>>1. NEVER use implcit cursors for queries.
>>2. ALWAYS put an exception handler in your code
>>3. ALWAYS close your cursor before the PL/SQL program unit completes
>>(both in the body of the code and the exception handlers).
>>
>>
>>--
>>Ron Reidy
>>Oracle DBA
>>Reidy Consulting, L.L.C.
>>
>>
>
>
Received on Fri Aug 20 1999 - 04:31:12 CDT