This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,039 Users
  • 2,269,775 Discussions


PL/SQL - Catching NO_DATA_FOUND Exception

Jasper Tanglib
Jasper Tanglib Member Posts: 76 Red Ribbon


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.



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';



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



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