1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

cursors

Discussion in 'SQL PL/SQL' started by bnramesh8, Jan 22, 2015.

  1. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    How can Handle errors in explicit cursors? i got error while fetching 5th record the cursor contain total 10 records?
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    You will have to be more specific. Saying you get an error fetching the 5th record w/ an explicit cursor is no different than saying you get an error upon return of the 5th record in a select statement.

    CJ
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What errors? You have posted nothing to show the error message you're receiving, nor have you posted any code showing exactly what you are doing to fetch this 'fifth' record. We don't know if it's a problem with Oracle, your database or simply mistakes in your code. Your post is exactly like going to a doctor and saying 'I'm sick, cure me but don't examine me.' The task is impossible, just like asking us to answer your ill-formed and incomplete question.


    If, and when, you post details showing exactly what YOU are doing with this cursor and the error or errors you're generating this is the best you get.
     
  4. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Hi ,

    Please post the your query. We will check and provide the suggestion to fix.

    Thanks,
    Venkatesh G
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  6. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello ,

    You can use inner plsql block to handle the exception while processing the cursor.

    Please refer the below scenario for re-produce and resolve the issue

    1) Create a table like CREATE TABLE TEST (emp VARCHAR2(10))
    2)Insert the values to the table 1 to 10 . insert any character in between eg: 'S'
    3) Excecute the the below code

    DECLARE
    v_num NUMBER;

    CURSOR test_cur
    IS
    SELECT emp
    FROM TEST;
    BEGIN
    FOR i IN test_cur
    LOOP
    v_num := i.emp;

    END LOOP;
    END;
    4) You will get ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    5) Modify the code as below

    DECLARE
    v_num NUMBER;

    CURSOR test_cur
    IS
    SELECT emp
    FROM TEST;
    BEGIN
    FOR i IN test_cur
    LOOP
    BEGIN
    v_num := i.emp;
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line (i.emp);
    END;
    END LOOP;
    END;

    6) It will process all the records except the error record. DBMS_OUTPUT return the character 'S'