Forum Stats

  • 3,769,663 Users
  • 2,253,000 Discussions
  • 7,875,140 Comments

Discussions

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

K Cannell, TH Tech
K Cannell, TH Tech Member Posts: 27 Blue Ribbon
edited Feb 13, 2019 1:22PM in APEX Discussions

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

Jon FinkeK Cannell, TH Tech

Best Answer

  • K Cannell, TH Tech
    K Cannell, TH Tech Member Posts: 27 Blue Ribbon
    edited Jan 26, 2019 7:00PM Accepted 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

    Jon Finke

Answers

  • Franck N
    Franck N Member Posts: 1,016 Silver Trophy
    edited 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
    K Cannell, TH Tech Member Posts: 27 Blue Ribbon
    edited 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
    K Cannell, TH Tech Member Posts: 27 Blue Ribbon
    edited 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
    K Cannell, TH Tech Member Posts: 27 Blue Ribbon
    edited 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.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

    0%

    0.287550.00010
    Collection - Begin Create Collection, Collection Name: NOT_LOAD_CONTENT
    4

    0%

    0.287650.00036
    Collection - Getting collection ID for Collection Name: NOT_LOAD_CONTENT
    4

    0%

    0.288010.00090
    Collection - Returning Collection ID  for Collection Name: NOT_LOAD_CONTENT
    4

    0%

    0.288920.00011
    Collection - End Create Collection, Collection Name: NOT_LOAD_CONTENT
    4

    0%

    0.289030.00029
    Collection - Getting collection ID for Collection Name: NOT_LOAD_CONTENT
    4

    0%

    0.289320.01901
    Collection - Returning Collection ID 30704688269398 for Collection Name: NOT_LOAD_CONTENT
    4

    5%

    0.308320.04066
    Collection - create_collection_from_queryb2, Begin bulk insert, Collection Name: NOT_LOAD_CONTENT
    4

    10%

    0.348980.00121
    Collection - create_collection_from_query, End bulk insert, Collection Name: NOT_LOAD_CONTENT
    4

    0%

    0.350200.02636
    Collection - End create_collection_from_queryb2, Inserted 892 members.  Collection Name: NOT_LOAD_CONTENT
    4

    6%

    0.376560.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.376670.00106
    Add error onto error stack




  • K Cannell, TH Tech
    K Cannell, TH Tech Member Posts: 27 Blue Ribbon
    edited 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
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited 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
    K Cannell, TH Tech Member Posts: 27 Blue Ribbon
    edited Jan 22, 2019 10:36AM

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

  • K Cannell, TH Tech
    K Cannell, TH Tech Member Posts: 27 Blue Ribbon
    edited Jan 26, 2019 7:00PM Accepted 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

    Jon Finke
  • Jon Finke
    Jon Finke Member Posts: 62 Bronze Badge
    edited Feb 13, 2019 1:22PM

    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.

    K Cannell, TH Tech