Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

PL/SQL - Catching NO_DATA_FOUND Exception

Jasper TanglibApr 15 2021

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:
image.pngThis 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:
image.png

How do I catch the NO_DATA_FOUND exception?

Any ideas or suggestions are appreciated.
- Jazz

This post has been answered by User_H3J7U on Apr 15 2021
Jump to Answer
Comments
Post Details
Added on Apr 15 2021
4 comments
43,633 views