Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

K Cannell TH TechJan 21 2019 — edited Feb 13 2019

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
This post has been answered by K Cannell TH Tech on Jan 26 2019
Jump to Answer

Comments

Franck N

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

it might work.

regards,

Franck

K Cannell TH Tech

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

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

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.087060.00800
Collection - Begin create_collection_from_queryb2, Collection Name: LOAD_CONTENT
4

2%

0.095070.00091
Collection - create_collection_from_queryb2, Begin execute query, Collection Name: LOAD_CONTENT
4

0%

0.095980.00010
Collection - create_collection_from_queryb2, End execute query, Collection Name: LOAD_CONTENT
4

0%

0.096070.02351
Collection - create_collection_from_queryb2, Begin fetch rows, Collection Name: LOAD_CONTENT
4

6%

0.119590.00065
Collection - create_collection_from_queryb2, End fetch rows, Collection Name: LOAD_CONTENT
4

0%

0.120240.00010
Collection - Begin Create Collection, Collection Name: LOAD_CONTENT
4

0%

0.120340.00034
Collection - Getting collection ID for Collection Name: LOAD_CONTENT
4

0%

0.120680.00085
Collection - Returning Collection ID  for Collection Name: LOAD_CONTENT
4

0%

0.121530.00011
Collection - End Create Collection, Collection Name: LOAD_CONTENT
4

0%

0.121640.00032
Collection - Getting collection ID for Collection Name: LOAD_CONTENT
4

0%

0.121950.02060
Collection - Returning Collection ID 30608317269381 for Collection Name: LOAD_CONTENT
4

5%

0.142560.10849
Collection - create_collection_from_queryb2, Begin bulk insert, Collection Name: LOAD_CONTENT
4

27%

0.251040.00299
Collection - create_collection_from_query, End bulk insert, Collection Name: LOAD_CONTENT
4

1%

0.254040.00112
Collection - End create_collection_from_queryb2, Inserted 892 members.  Collection Name: LOAD_CONTENT
4

0%

0.255150.00747
Collection - Begin create_collection_from_queryb2, Collection Name: NOT_LOAD_CONTENT
4

2%

0.262620.00104
Collection - create_collection_from_queryb2, Begin execute query, Collection Name: NOT_LOAD_CONTENT
4

0%

0.263660.00012
Collection - create_collection_from_queryb2, End execute query, Collection Name: NOT_LOAD_CONTENT
4

0%

0.263780.02317
Collection - create_collection_from_queryb2, Begin fetch rows, Collection Name: NOT_LOAD_CONTENT
4

6%

0.286950.00060
Collection - create_collection_from_queryb2, End fetch rows, Collection Name: NOT_LOAD_CONTENT
4

K Cannell TH Tech

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!

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

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

K Cannell TH Tech
Answer

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

Marked as Answer by K Cannell TH Tech · Sep 27 2020
1 person found this helpful
Jon Finke

Thanks for the suggested - I had the same (or very similar) problem - skipped validation and now things work again.  No dates in my data though.

1 person found this helpful
1 - 9

Post Details

Added on Jan 21 2019
#apex-discussions
9 comments
4,160 views