ora-6510 ora-6512 ora-1403 errors
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: ora-6510 ora-6512 ora-1403 errors

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    ora-6510 ora-6512 ora-1403 errors

    Hi all,

    I want to insert records in table B from table A if the primary key of table A is not found in table B. So, I wrote foll. PL/SQL code.

    declare
    l_request_id NUMBER;
    g_end_program EXCEPTION;
    CURSOR
    reqs
    IS SELECT REQUEST_ID FROM table_A
    WHERE
    ACTUAL_COMPLETION_DATE <= SYSDATE
    AND STATUS_CODE = 'C'
    AND PHASE_CODE = 'C'
    AND PRINTER != 'noprint'
    AND NUMBER_OF_COPIES > 0;
    BEGIN
    FOR cr IN reqs
    LOOP
    SELECT request_id INTO l_request_id
    FROM table_B
    WHERE request_id = cr.request_id;
    if l_request_id is NULL
    then
    INSERT INTO table_B from cursor....
    end if;
    END LOOP;
    EXCEPTION
    when no_data_found then
    raise g_end_program;
    END;
    /
    But, it is giving me

    declare
    *
    ERROR at line 1:
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at line 26
    ORA-01403: no data found

    Pl. help.

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    INSERT INTO table_B from cursor....

    What is this?

    Tamil

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    What's wrong with something like:

    Insert Into table_B
    SELECT REQUEST_ID, etc, etc
    FROM table_A
    WHERE
    ACTUAL_COMPLETION_DATE <= SYSDATE
    AND STATUS_CODE = 'C'
    AND PHASE_CODE = 'C'
    AND PRINTER != 'noprint'
    AND NUMBER_OF_COPIES > 0
    and not exists
    (Select * From table_B
    where table_B.request_id = table_A.request_id)
    ;

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by tamilselvan
    INSERT INTO table_B from cursor....

    What is this?

    Tamil
    if l_request_id is NULL
    then
    insert into table_B
    ( REQUEST_ID,
    REQUESTED_BY,
    PHASE_CODE,
    STATUS_CODE,
    PROGRAM_APPLICATION_ID,
    CONCURRENT_PROGRAM_ID,
    NUMBER_OF_COPIES,
    PRINTER,
    DESCRIPTION,
    ACTUAL_COMPLETION_DATE,
    ARGUMENT_TEXT)
    values
    (cr.REQUEST_ID,
    cr.REQUESTED_BY,
    cr.PHASE_CODE,
    cr.STATUS_CODE,
    cr.PROGRAM_APPLICATION_ID,
    cr.CONCURRENT_PROGRAM_ID,
    cr.NUMBER_OF_COPIES,
    cr.PRINTER,
    cr.DESCRIPTION,
    cr.ACTUAL_COMPLETION_DATE,
    cr.ARGUMENT_TEXT);
    end if;
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    SELECT request_id INTO l_request_id
    FROM table_B
    WHERE request_id = cr.request_id;
    if l_request_id is NULL
    This does not do what you want (I think) - it will raise a no_data_found exception if the row doesn't exist.

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by DaPi
    This does not do what you want (I think) - it will raise a no_data_found exception if the row doesn't exist.
    My requirement is that if record is not found in table_B, then it should insert record with values from cursor.
    There is a no_data_found exception handler in the procedure. Still why error occurs.
    Guys, I am a beginner in development. Pl. help.
    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by DaPi
    What's wrong with something like:

    Insert Into table_B
    SELECT REQUEST_ID, etc, etc
    FROM table_A
    WHERE
    ACTUAL_COMPLETION_DATE <= SYSDATE
    AND STATUS_CODE = 'C'
    AND PHASE_CODE = 'C'
    AND PRINTER != 'noprint'
    AND NUMBER_OF_COPIES > 0
    and not exists
    (Select * From table_B
    where table_B.request_id = table_A.request_id)
    ;

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by DaPi
    I can use this but I want to know that why my code is not working for the sake of knowledge.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The first time you find a row you would insert, you get a no_data_found, which jumps out of the loop, raises your g_end_program exception, which is not handled, so it dies.

    Try something like:
    Code:
     . . . 
    BEGIN
    FOR cr IN reqs
    LOOP
       BEGIN
          SELECT request_id INTO l_request_id
          FROM table_B
          WHERE request_id = cr.request_id;
       EXCEPTION
       when no_data_found then
          INSERT INTO table_B from cursor....
       END;
    
    END LOOP;
    COMMIT;  ? ? ? 
    END;
    /

  10. #10
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by DaPi
    The first time you find a row you would insert, you get a no_data_found, which jumps out of the loop, raises your g_end_program exception, which is not handled, so it dies.

    Try something like:
    Code:
     . . . 
    BEGIN
    FOR cr IN reqs
    LOOP
       BEGIN
          SELECT request_id INTO l_request_id
          FROM table_B
          WHERE request_id = cr.request_id;
       EXCEPTION
       when no_data_found then
          INSERT INTO table_B from cursor....
       END;
    
    END LOOP;
    COMMIT;  ? ? ? 
    END;
    /
    Hi,

    With this logic, it gives the foll. error.

    ERROR at line 62:
    ORA-06550: line 62, column 7:
    PLS-00201: identifier 'CR.REQUEST_ID' must be declared
    ORA-06550: line 49, column 7:
    PL/SQL: SQL Statement ignored

    It seems that it is loosing the cursor out side the loop.

    Pl. advise.

    Thanks,
    Last edited by samdba; 01-06-2005 at 09:33 AM.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

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