No Data Found Or Too Many Rows Exception Error Occured During Bulk Record Insertion

I have Write a pl/sql process to insert bulk record which is getting from select statement after first begin but i will raised error and not insert any record help me to fix the e

Solution 1:

ORA-01403 (No data found) happens when your query returns nothing and you try do pass it to variable via INTO.

ORA-01422 (Too many rows) happens when your query returns more than 1 row that you are trying to fit at INTO.

You are handling the no_data_found from the first SELECT query but you are not handling too_many_rows from the first query. If your first SELECT query throws an no_data_found exception, it will be handled from the EXCEPTION, otherwise if it throws an too_many_rows exception than it will fail as your second image.

So where is the no_data_found exception happening? Probably from you second SELECT query.

You should loop the first SELECT query in order to avoid a no_data_found or a too_many_rows and make sure that your second SELECT query returns always 1 row.

Solution 2:

The no_data_found is actually being raised twice. The first time during the initial select (the 2nd statement after begin). The second time by the exception procedure handling the first occurrence. This occurs because your exception handler attempts the exact same query. Thus the exact same result. So why is the no_data_found raised the first time. You define the local variable vtm as number, but you do not initialize the value. This makes its value NULL. You then go on to use vtm in the where clause of the select statement :(AND gr_number = vtm;) This predicate will only return NULL thus making the entire where clause always false, which means no data satisfies the where condition. Correction: Well I don't know. You didn't provide sample data nor parameter values. However, somehow you must initialize the variable vtm to an existing gr_number (that may NOT be the only cause) or remove the variable from the query. Then again perhaps removing it is what caused your "too many rows" exception. If that is the case then you need to convert "select ... into ..." and and the loop from processing a collection to processing the cursor.

declare
    gr_cursor cursoris 
       ( select s.gr_number
           from student      s
           leftjoin class_time   ct 
                  on ct.class_id = s.class_id
                 and instr(s.class_time, ct.class_time) >0whereupper(trim(ct.class_id)) =upper(trim(:app_user))
            and s.gr_number isnotnulland is_active_flg ='Y'and gr_number = vtm 
       ); 

beginfor grc in gr_cursor
    loop 
      insertinto student_class_attend (
        ... ;
    end loop;

    commit; 
end;

Disclaimer: I did NOT verify the variables vtm, tab are not in the expansion for the insert, nor attempt other changes, if any, within that expansion. But there may very well be some needed.