Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    25

    Unanswered: Execute immediate throwing no data found exception

    I am using execute immediate in my stored procedure (Oracle 10G) as below


    EXECUTE IMMEDIATE 'SELECT BUCKET_COLUMN FROM TABLENAME ' ||
    'WHERE ID = '||var_ID||' AND CODE_ID = '||var_CODE_ID|| ' AND source_column = ''CODE''' INTO var_COLUMN_NAME;

    The query sometimes returns null whihc is expected however I get an exception as below how do I handle this so a null value is accepted

    ORA-1403: No Data Found

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by qts1
    however I get an exception as below how do I handle this so a null value is accepted
    By catching the exception

  3. #3
    Join Date
    Feb 2009
    Posts
    25
    Ok thats was pretty simple thanks!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    It's amazing what wealth of information the manual contains.
    Isn't it?

  5. #5
    Join Date
    Feb 2009
    Posts
    25

    Still an issue....

    If I have multiple Execute Immediates in my stored procedure as I am trying to gather multiple values from different tables and eventually insert them in one common table do I have to catch the exception after each statement for the execute immediate?

    If I catch the exception at the end of my loop and say the 2nd execute immediate throws a 'No Data Found' error then the other statements are not executed. Please advise!

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please advise!
    Change the code to avoid this undesired action.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2009
    Posts
    25
    thanks but that really is not an option

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can catch the exception either inside the loop or outside it. If you catch it inside the loop you can take whatever action is required and then continue to the next loop iteration - if that is what you require:
    Code:
    begin
       loop
          begin
             ... -- do stuff
          exception
             when no_data_found then
                --handle this exception e.g.
                insert into error_log ...;
          end;
       end loop;
    end;

  9. #9
    Join Date
    Feb 2009
    Posts
    25
    Right but say in my example I have as below the second stmt throws an error then the control goes to the exception block and stmt 3 and 4 are never executed ... correct? Unless I catch the exception after every stmt that is getting executed which is kind of inefficient

    Code:
    begin
       loop
          begin
             ... select stmt 1
             ... select stmt 2 -- this statement throws exception
             ... select stmt 3
             ... select stmt 4
    
          exception
             when no_data_found then
                --handle this exception e.g.
                insert into error_log ...;
          end;
       end loop;
    end;

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It wouldn't be inefficient if that was the requirement, it would be necessary. However, that would be an unusual scenario where the failure of any statement could just be ignored.

Posting Permissions

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