SELECT INTO and CURSORS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SELECT INTO and CURSORS

  1. #1
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41

    Talking

    Hi all,

    I have a stored procedure that currently has 3 cursors. One of them is definitely required, but the other 2 are used ONLY to get past the 'NO ROWS FOUND' ORA error when doing a SELECT INTO cluase.

    The most rows the SELECT INTO will return is 1 (I know this from the data) - but there are definitely cases where I will get no results.

    Is there no way I can use SELECT INTO with a 'do this if there are no rows found' clause? I do not want to use 2 extra cursors if I do not have to.

    Thanks in advance,

    Keith

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    BEGIN
    ....

    --BEGIN
    ----SELECT
    ------COL1
    ----INTO
    ------L_Col1
    ----WHERE
    ------<whatever>
    --EXCEPTION
    ----WHEN NO_DATA_FOUND THEN
    ------<whatever>
    --END

    ...

    END

    HTH,

    - Chris

  3. #3
    Join Date
    Jan 2001
    Posts
    515
    declare

    cursor c1 is
    (this is the one that returns 1 or 0 rows)

    begin

    for v1 in c1 loop

    if c1%rowcount = 0 then
    (this checks to see how many rows are returned in your cursor)

    end loop

    end


  4. #4
    Join Date
    Jul 2000
    Posts
    296
    As Chrisrlong stated you can also put the SELECT INTO in a PL/SQL block with an exception handler. You also need to handle the TOO_MANY_ROWS exception.

    Why don't you want to use the cursors? With cursors you don't have to change your code if the data changes and there are no or many rows returned. And with cursors the code is more easy to read and maintain, then with the different exception handlers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width