APEX 18.2 Data Load Wizard w date format - no data found

K Cannell, TH Tech · Jan 21, 2019 3:16PM

Anyone working with the APEX 18.2 Data Load Wizard?

Working with a vanilla data load wizard - no transformations, no rules, straight upload against a table -

whenever I enter a date format for a field - i.e. DD/DD/YYYY for a BIRTH_DATE filed, I get "no data found" when I click Next on the Column Mapping page.

When I remove the date format, all works fine.

When I enter the data format in double quotes, I get to the next page, then get a literal date format error upon data load.

Has anyone gotten past this?

What Is the data load wizard expecting for a Date format? It used to be no quotes, just the format.

For our application we need the date format - our different users send us data in different date formats.

Yeah, we could preprocess the file but … this is an APEX feature that is supposed to work, and has worked in the past.

Does anyone have any insights here?

Thank you,

Karen

K Cannell, TH Tech · Jan 26, 2019 7:00PM

For those who may be wondering ..

I was able to track down the "no data found" error on Next moving from the column mapping page to the summary page, when:

- There are 3 Unique columns defined in the Data Load Definition

- The 3rd unique column either not in the upload data set OR selected as Do Not Load

AND in the Data Load Definition, the Skip Verification setting is No.

I am able to work around this issue by setting Skip Validations in the Data Load Definition to Yes.

Carsten identified that this is likely a bug, that will possibly be addressed in a future version. Many thanks for the help!

In the meantime, if someone encounters this issue, try one of these workarounds:

- Upload to a stage table w/o the unique keys, than use a custom process to move data to the real tables, or,

- Set Skip Validations to Yes.

I realize the latter may not be OK is you are relying on the DL Wiz to for validations and transforms ... in our case we are not, we do those elsewhere, so Skip Validations does the trick.

I hope this helps someone,

Karen

Franck N · Jan 21, 2019 3:24PM

maybe instead of using DD/DD/YYYY as date Format, use DD/MM/YYYY.

it might work.

regards,

Franck

K Cannell, TH Tech · Jan 21, 2019 8:39PM

I wish, the DD/DD/YYYY was a typo - I had entered MM/DD/YYYY. And I even tried the other way. Just in case, which would have invalidated many rows. No matter. Interesting that entering "MM/DD/YYYY" got me past the no data found to the next page, BUT then I get the literal error.

K Cannell, TH Tech · Jan 21, 2019 8:50PM

This is a definite change in how the data load wizard worked in APEX 5.1.

The same data load wizard, when an app is upgraded, no longer works, giving the no data found error.

The specific example I am asking about is a new data load wizard created on the same table, bare bones, to see what APEX is expecting … but I have yet to figure that out. Any help or advice is appreciated.

K Cannell, TH Tech · Jan 21, 2019 11:15PM

More information:

After much testing, with different files, copy/paste and upload file, I get the "no data found" message on most every data set, whether I have a date column and enter a date format or not. The exact error message is:

Exception in "Prepare Uploaded Data":

Error Stack: ORA-01403: no data found

ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2777

ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 394

ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2645

ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1126

ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1232

ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN", line 2451

Backtrace: ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2777

ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 394

ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2645

ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1126

ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1232

ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN", line 2451

ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS", line 201

Via debug I can see that the PARSE_COL_HEAD, LOAD_COL_HEAD, LOAD_CONTENT, NOT_LOAD_CONTENT collections have all been populated. Actually there are several rounds of loading NOT_LOAD_CONTENT, the last ending with a row count:

Collection - Returning Collection ID 30532026269375 for Collection Name: LOAD_COL_HEAD	4	1%
0.08706	0.00800	Collection - Begin create_collection_from_queryb2, Collection Name: LOAD_CONTENT	4	2%
0.09507	0.00091	Collection - create_collection_from_queryb2, Begin execute query, Collection Name: LOAD_CONTENT	4	0%
0.09598	0.00010	Collection - create_collection_from_queryb2, End execute query, Collection Name: LOAD_CONTENT	4	0%
0.09607	0.02351	Collection - create_collection_from_queryb2, Begin fetch rows, Collection Name: LOAD_CONTENT	4	6%
0.11959	0.00065	Collection - create_collection_from_queryb2, End fetch rows, Collection Name: LOAD_CONTENT	4	0%
0.12024	0.00010	Collection - Begin Create Collection, Collection Name: LOAD_CONTENT	4	0%
0.12034	0.00034	Collection - Getting collection ID for Collection Name: LOAD_CONTENT	4	0%
0.12068	0.00085	Collection - Returning Collection ID for Collection Name: LOAD_CONTENT	4	0%
0.12153	0.00011	Collection - End Create Collection, Collection Name: LOAD_CONTENT	4	0%
0.12164	0.00032	Collection - Getting collection ID for Collection Name: LOAD_CONTENT	4	0%
0.12195	0.02060	Collection - Returning Collection ID 30608317269381 for Collection Name: LOAD_CONTENT	4	5%
0.14256	0.10849	Collection - create_collection_from_queryb2, Begin bulk insert, Collection Name: LOAD_CONTENT	4	27%
0.25104	0.00299	Collection - create_collection_from_query, End bulk insert, Collection Name: LOAD_CONTENT	4	1%
0.25404	0.00112	Collection - End create_collection_from_queryb2, Inserted 892 members. Collection Name: LOAD_CONTENT	4	0%
0.25515	0.00747	Collection - Begin create_collection_from_queryb2, Collection Name: NOT_LOAD_CONTENT	4	2%
0.26262	0.00104	Collection - create_collection_from_queryb2, Begin execute query, Collection Name: NOT_LOAD_CONTENT	4	0%
0.26366	0.00012	Collection - create_collection_from_queryb2, End execute query, Collection Name: NOT_LOAD_CONTENT	4	0%
0.26378	0.02317	Collection - create_collection_from_queryb2, Begin fetch rows, Collection Name: NOT_LOAD_CONTENT	4	6%
0.28695	0.00060	Collection - create_collection_from_queryb2, End fetch rows, Collection Name: NOT_LOAD_CONTENT	4	0%
0.28755	0.00010	Collection - Begin Create Collection, Collection Name: NOT_LOAD_CONTENT	4	0%
0.28765	0.00036	Collection - Getting collection ID for Collection Name: NOT_LOAD_CONTENT	4	0%
0.28801	0.00090	Collection - Returning Collection ID for Collection Name: NOT_LOAD_CONTENT	4	0%
0.28892	0.00011	Collection - End Create Collection, Collection Name: NOT_LOAD_CONTENT	4	0%
0.28903	0.00029	Collection - Getting collection ID for Collection Name: NOT_LOAD_CONTENT	4	0%
0.28932	0.01901	Collection - Returning Collection ID 30704688269398 for Collection Name: NOT_LOAD_CONTENT	4	5%
0.30832	0.04066	Collection - create_collection_from_queryb2, Begin bulk insert, Collection Name: NOT_LOAD_CONTENT	4	10%
0.34898	0.00121	Collection - create_collection_from_query, End bulk insert, Collection Name: NOT_LOAD_CONTENT	4	0%
0.35020	0.02636	Collection - End create_collection_from_queryb2, Inserted 892 members. Collection Name: NOT_LOAD_CONTENT	4	6%
0.37656	0.00011	Exception in "Prepare Uploaded Data": Error Stack: ORA-01403: no data found ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2777 ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 394 ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2645 ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1126 ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1232 ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN", line 2451 Backtrace: ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2777 ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 394 ORA-06512: at "APEX_180200.WWV_FLOW_DATA_UPLOAD", line 2645 ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1126 ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS_NATIVE", line 1232 ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN", line 2451 ORA-06512: at "APEX_180200.WWV_FLOW_PROCESS", line 201	2	0%
0.37667	0.00106	Add error onto error stack

K Cannell, TH Tech · Jan 21, 2019 11:58PM

Based on the debug messages, my guess is that something is causing No rows to get loaded in the LOAD_CONTENT collection and all ros to be loaded in the NOT_LOAD_CONTENT collection, From that, I can guess that the no data found message is caused by the query agains the empty LOAD_CONTENT collection.

But why, when I am loading all valid rows, at least by pre-18.2 standards, are all these rows ending up in the NOT_LOADED_CONTENT collection?

APEX team, please help me understand what has changed behind the scenes, so I can adjust and get my applications working again.

Thank you!

Carsten Czarski-Oracle · Jan 22, 2019 7:27AM

Hi Karen,

would be happy to have a look into this ... from the error stack this seems to be related to the Primary Key Columns, but I would need to check in more detail:

Is it possible to create a test case ...? I would need ...

-) the table definition

-) details of the data load definition

-) sample data

You might also send this to me privately. The Oracle email scheme is {firstname}.{lastname}@oracle.com. As an alternative, could you set Debug mode to 9 (turn on Debug, then replace the "YES" in the URL with "LEVEL9") and send the debug output to me ...?

Best regards

-Carsten

K Cannell, TH Tech · Jan 22, 2019 10:35AM

Thank you Carsten - in progress. Will update with details soon and send privately.

K Cannell, TH Tech · Jan 26, 2019 7:00PM