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

Inserting bulk records is an efficient way to transfer large amounts of data into a database. However, this process can be complicated if you encounter errors, such as the No Data Found or Too Many Rows exceptions, during the insertion phase.

In this article, we will explore how these exceptions occur and provide guidance on how to handle them when performing bulk record insertions using PL/SQL.

Introduction

The code provided is an example of a stored procedure that inserts records into a table based on data retrieved from another table. The procedure first retrieves the gr_number values from the second table, and then uses these values to fetch corresponding records from the first table. These records are then inserted into the target table.

ORA-01403 (No Data Found)

The ORA-01403 exception occurs when a query returns no rows that match the specified condition. This can happen if there is an error in your query or if you have incorrectly configured your database connections.

In this case, we have two separate queries: one for fetching records from the first table and another for inserting these records into the target table. We should verify that both of these queries are executing successfully.

Let’s take a closer look at each query to determine where the No Data Found exception might be occurring:

First Query

SELECT
    s.gr_number
INTO vtm
FROM
    student      s
    LEFT JOIN class_time   ct ON ct.class_id = s.class_id
                               AND instr(s.class_time, ct.class_time) > 0
WHERE
    upper(TRIM(ct.class_id)) = upper(TRIM(:app_user))
    AND s.gr_number IS NOT NULL
    AND is_active_flg = 'Y'
    AND gr_number = vtm;

The query above returns the gr_number values from the first table based on the specified conditions. If no records match these conditions, then this query will return no rows.

Second Query

SELECT
    st.gr_number,
    st.student_id,
    tm.next_day_flg,
    prd_start_tm,
    prd_end_tm,
    tm.prd_diff_minutes,
    tm.actual_minutes,
    tm.gap_minutes,
    st.course AS course_id,
    cr.course_name,
    cl.class_uid,
    st.class_id,
    st.class_time,
    st.branch_id,
    st.shift_id,
    cl.teacher_id
FROM
    tm2       tm
    JOIN student   st ON tm.gr_number = st.gr_number
    LEFT JOIN course    cr ON cr.course_id = st.course
    LEFT JOIN class     cl ON cl.class_id = st.class_id;

This query fetches the records from the second table and joins them with other tables to create the required columns. The No Data Found exception may occur here if there are no records in any of these tables.

ORA-01422 (Too Many Rows)

The ORA-01422 exception occurs when a query returns more than one row that you are trying to fit into a variable with a single value data type. This can happen if your query is not properly structured or if there is an error in the data retrieval process.

In this case, we should check our second query to see where it may be returning multiple rows:

SELECT
    st.gr_number,
    st.student_id,
    tm.next_day_flg,
    prd_start_tm,
    prd_end_tm,
    tm.prd_diff_minutes,
    tm.actual_minutes,
    tm.gap_minutes,
    st.course AS course_id,
    cr.course_name,
    cl.class_uid,
    st.class_id,
    st.class_time,
    st.branch_id,
    st.shift_id,
    cl.teacher_id
FROM
    tm2       tm
    JOIN student   st ON tm.gr_number = st.gr_number
    LEFT JOIN course    cr ON cr.course_id = st.course
    LEFT JOIN class     cl ON cl.class_id = st.class_id;

We may need to modify this query or the way we handle the returned rows.

Handling Exceptions

To handle these exceptions effectively, we should consider the following steps:

1. Verify Queries: Make sure that both queries are executing correctly and returning the expected results.
2. Use Looping Mechanisms: Instead of trying to fit multiple rows into a variable with a single data type, use looping mechanisms to process each row individually.
3. Check for NULL Values: Verify that all values are being populated properly before attempting to insert them into the target table.
4. Error Handling: Implement proper error handling using exceptions and statements like DBMS_OUTPUT.put_line to log any errors or unexpected results.

Conclusion

Handling No Data Found and Too Many Rows exceptions during bulk record insertion is crucial for maintaining data integrity and ensuring that your database operations are robust. By understanding the causes of these exceptions, implementing proper error handling mechanisms, and structuring your queries correctly, you can ensure successful and reliable bulk insertions.

Additional Considerations

• Query Optimization: Make sure to optimize your queries by minimizing unnecessary joins, indexing columns used in WHERE clauses, and improving overall query performance.
• Transaction Management: Use transactions effectively to group multiple operations into a single unit of work. This can help ensure data consistency and integrity during bulk insertions.

By following these guidelines, you can create robust stored procedures that handle potential exceptions during bulk record insertion, ensuring the reliability and efficiency of your database operations.

Next Steps

• Test Your Procedures: Test your stored procedures thoroughly to ensure they are working as expected.
• Refactor Code: Refactor your code based on lessons learned from testing and debugging.
• Monitor Performance: Monitor performance regularly to optimize query execution times and reduce the risk of exceptions.

Last modified on 2023-11-21