Forum Stats

  • 3,770,225 Users
  • 2,253,082 Discussions
  • 7,875,369 Comments

Discussions

PL/SQL - Catching NO_DATA_FOUND Exception

Jasper Tanglib
Jasper Tanglib Member Posts: 68 Green Ribbon

Hi,

In Oracle Apex 20.2, I have a Process that should display a message once it encounters an ORA-20987: No data found in uploaded file but my current code(please refer below) does not seem to notice it.

Code:

BEGIN

INSERT INTO xt_media_data_types (rn, owner, table_name, column_name, data_type, con_id)

    SELECT  REGEXP_REPLACE(col001,'\s*(^,>)', '') RN,

            REGEXP_REPLACE(col002,'\s*(^,>)', '') OWNER, 

            REGEXP_REPLACE(col003,'\s*(^,>)', '') TABLE_NAME,

            REGEXP_REPLACE(col004,'\s*(^,>)', '') COLUMN_NAME,

            REGEXP_REPLACE(col005,'\s*(^,>)', '') DATA_TYPE,

            REGEXP_REPLACE(col006,'\s*(^,>)', '') CON_ID

    FROM wwv_flow_files f,

        TABLE ( apex_data_parser.parse(

                                        p_content                     => f.blob_content,

                                        p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',

                                        p_file_name                   => f.filename,

                                        p_skip_rows                   => 2,

                                        p_csv_col_delimiter           => '<,>' 

                                    )) p

    WHERE name LIKE '%columns_with_media_datatypes.csv';

    EXCEPTION 

    WHEN NO_DATA_FOUND THEN 

        dbms_output.put_line('File has no data');

END;

Screenshot:

This code is inserting a blank csv file into a table and that should be giving no data found exception. My code should be able to catch that and it does have the exception clause but it is not passing through it.


I have tried to catch other exceptions to make sure I do have the correct syntax, like the INVALID_NUMBER exception (please refer below), and it seems I have the correct syntax but for some reason it can't catch a NO_DATA_FOUND:


How do I catch the NO_DATA_FOUND exception?


Any ideas or suggestions are appreciated.

- Jazz

Best Answers

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy
    edited Apr 15, 2021 9:24AM Accepted Answer

    An insert statement cannot raise no_data_found. No_data_found is raised by the non-bulk into clause or by collection index.

    ORA-20987 is custom, not predefined exception no_data_found.

  • cormaco
    cormaco Member Posts: 1,723 Bronze Crown
    Accepted Answer

    You should be able to handle the exception like this (untested):

    WHEN OTHERS THEN
      IF SQLCODE = -20987 THEN
         dbms_output.put_line('File has no data');
      END IF;
    

Answers