9 Replies Latest reply on Feb 13, 2019 6:22 PM by Jon Finke

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

    K Cannell, TH Tech

      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

        • 1. Re: APEX 18.2 Data Load Wizard w date format - no data found
          Franck N

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

          it might work.

           

          regards,

          Franck

          • 2. Re: APEX 18.2 Data Load Wizard w date format - no data found
            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.

            • 3. Re: APEX 18.2 Data Load Wizard w date format - no data found
              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.

              • 4. Re: APEX 18.2 Data Load Wizard w date format - no data found
                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

                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




                • 5. Re: APEX 18.2 Data Load Wizard w date format - no data found
                  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!

                  • 6. Re: APEX 18.2 Data Load Wizard w date format - no data found
                    Carsten Czarski-Oracle

                    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

                    • 7. Re: APEX 18.2 Data Load Wizard w date format - no data found
                      K Cannell, TH Tech

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

                      • 8. Re: APEX 18.2 Data Load Wizard w date format - no data found
                        K Cannell, TH Tech

                        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

                        • 9. Re: APEX 18.2 Data Load Wizard w date format - no data found
                          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.