Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
PL/SQL - Catching NO_DATA_FOUND Exception

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
-
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.
-
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
-
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.
-
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;
-
It worked @cormaco. Thank you!
-
NO_DATA_FOUND is the named exception linked to Oracle error -1403, (though, unusually it is defined in ANSI terms as error 100) through a pragma "exception_init".
As a variation on @cormaco 's answer you could add translated the SQLCODE into an exception name in a DECLARE section for your PL/SQL
declare no_data_in_file exception; pragma exception_init(no_data_in_file, -29307); begin .... exception when no_data_in_file then dbms_output.put_line('No data in file'); when others then raise; end;