1 2 Previous Next 20 Replies Latest reply on Jul 26, 2019 9:02 PM by Raviteja

    Showing progress while running a procedure in the back ground

    Raviteja

      Hi,

          I'm on Apex 19.1 and I have a page that uploads and parses XLSX file before loading. I created a button that calls a PL/SQL process to load that data into the table(s). This excel file has over 160 columns and over 1000 rows.

      When users click on the button, I want to show progress so that they know it's doing something. Ive used the technique listted here https://community.oracle.com/thread/4171805

      Now, when the button is clicked, I see the spinner and the procedure runs successfully but I see an error on screen listed below and the spinner keeps on spinning continuously unless I click on some other link and move away from the page. Am I doing something wrong?

      "1 error has occurred

          Error: SyntaxError: JSON.parse: unexpected end of data at line 1 column 1 of the JSON data"

       

       

      My Ajax callback process

      declare 
      begin 
         /* ********************** * 
         * PL/SQL Process Content * 
         * ********************** */ 
        sp_load_file(:P23_FILE); 
            
         apex_json.open_object; 
         apex_json.write('success', true); 
         apex_json.write('message', 'Success'); 
         apex_json.close_object; 
      exception 
         when others then 
            apex_json.open_object; 
            apex_json.write('success', false); 
            apex_json.write('message', sqlerrm); 
            apex_json.close_object; 
      end; 

       

      My Java Script code under dynamic action:

      var lSpinner$ = apex.util.showSpinner(); 

       

      apex.server.process("PLS_AJAX", 

                          {pageItems: "#P23_FILE" /*List of the items that are used in your process */ 

                           }, 

                          {success: function( pData ) { 

                              /* If the AJAX is successful */ 

                              if (pData.success === true){ 

                                 /* Show success message */ 

                                 apex.message.showPageSuccess( pData.message ); 

                              } 

                              else { 

                                 /* Show error message */ 

                                 apex.message.clearErrors(); 

       

                                 apex.message.showErrors([ 

                                 { 

                                    type:       "error", 

                                    location:   "page", 

                                    message:    pData.message, 

                                    unsafe:     false 

                                 } 

                                 ]); 

                              } 

                               

                              /* Remove the processing image */ 

                              lSpinner$.remove(); 

                            } 

                           } 

                          ); 

       

       

      Thank you

        • 1. Re: Showing progress while running a procedure in the back ground
          Sven W.

          The problem seems to be that the ajax response is not a valid JSON.

          Because it is not valid JSON, the code never enters your success function and the spinner is not stopped.

           

          I remember I encountered the same issue once, but I don't recount why this happend. It might be the APEX engine is interfering incorrectly with the json response.

          "PLS_AJAX" seems to be defined as an AJAX callback on the same page. Maybe change it into an on demand application process and test if it behaves differently.

          1 person found this helpful
          • 2. Re: Showing progress while running a procedure in the back ground
            Raviteja

            Hi Sven,

                             Thank you so much for the post. I don't see "On-Demand" option in 19.1. Is there any other way?

             

            Thank you..

            • 3. Re: Showing progress while running a procedure in the back ground
              John Snyders-Oracle

              Hi,

              I recommend that you let apex.server.process do more of the work for you. It will handle the spinner.

              See the doc:

              https://docs.oracle.com/en/database/oracle/application-express/19.1/aexjs/apex.server.html#.process

              Note the options: loadingIndicatorPosition and loadingIndicator and refreshObject.

              If you just want the spinner centered on the page you can just use

              {

                  loadingIndicatorPosition: "page"

              }

               

              If you still want to create and remove your own spinner or just want to understand why it never stopped spinning. You must remove the spinner no mater if the request was success or error. I recommend following the second example in the doc for apex.server.process

              var result = apex.server.process( "MY_PROCESS", {

                  x01: "test",

                  pageItems: ["P1_DEPTNO","P1_EMPNO"]

              } );

              result.done( function( data ) {

                  // do something here

              } ).fail(function( jqXHR, textStatus, errorThrown ) {

                  // handle error

              } ).always( function() {

                  // code that needs to run for both success and failure cases

              } );

               

              It is in the always callback function that you would remove the spinner.

               

              Regards,

              -John

              1 person found this helpful
              • 4. Re: Showing progress while running a procedure in the back ground
                Pavel_p

                Hi,

                please, what does it exactly mean that "the procedure runs successfully"? I'm not sure if it's possible to submit a File Browse item type this way in apex.server.process, definitely not that easily. I believe you'll have to change button's action to Submit page (set its Storage Type attribute to APEX_APPLICATION_TEMP_FILES) and then create a (conditional) page process to parse the uploaded file like this:

                declare
                  l_fileblob blob;
                begin
                  /* ********************** *
                    * PL/SQL Process Content *
                    * ********************** */
                
                  select blob_content
                    into l_fileblob
                    from apex_application_temp_files
                    where name = :p23_file;
                
                
                  sp_load_file(l_fileblob);--assuming that the sp_load_file takes the actual blob as an input parameter, not the file name
                
                end;
                

                The spinner will be shown during page submission.

                Regards,

                Pavel

                1 person found this helpful
                • 5. Re: Showing progress while running a procedure in the back ground
                  Raviteja

                  Hi Pavel,

                               Thanks for your post.  I have a created a button  "Load Data"  that is conditional and displayed after the file is uploaded using the file browse. I'm passing the file name to the procedure and have this following code in the procedure. The procedure runs and loads the data from the uploaded file into table successfully, it's just that when I added spinner, the spinner keeps on spinning. I was not sure how to stop the spinner after the procedure ran. Is it possible to return an output from the procedure at the end like 'COMPLETE' and stop the spinner using it?

                      (select
                             to_number( col001 ),
                             upper( col002 ),
                             upper( col003 ),  
                             to_number( col004 ) ,
                             upper ( col005 ),
                             upper( col006 ),
                             upper( col007 ),
                             upper( col008 ),
                             upper( col009 ),
                             upper( col010 ),
                             upper( col011 )
                       from apex_application_temp_files f,
                            table( apex_data_parser.parse(
                                p_content                 => f.blob_content,
                                 p_skip_rows => 1 ,
                                p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                                p_file_name               => f.filename ) ) p
                  where f.name = p_file

                   

                   

                  Thank you

                  • 6. Re: Showing progress while running a procedure in the back ground
                    Pavel_p

                    Hi Raviteja,

                    I'm not sure if I fully understand your description and I think it would be highly helpful if you created a showcase on apex.oracle.com (and share developer's credentials to your workspace), especially this part is unclear

                    I have a created a button  "Load Data"  that is conditional and displayed after the file is uploaded using the file browse.

                    You don't have to create any conditional button, just create

                    1) Filebrowse item,

                    2) Submit button,

                    3) Page Process (in the processing section) with the statement

                    insert into my_table(columns...)
                    select to_number(col001),
                          upper(col002),
                          upper(col003),
                          to_number(col004),
                          upper(col005),
                          upper(col006),
                          upper(col007),
                          upper(col008),
                          upper(col009),
                          upper(col010),
                          upper(col011)
                    from apex_application_temp_files f,
                        table(apex_data_parser.parse(
                          p_content                      => f.blob_content,
                          p_skip_rows                    => 1,
                          p_store_profile_to_collection  => 'FILE_PARSER_COLLECTION',
                          p_file_name                  => f.filename
                        ))p
                    where f.name = :px_filebrowse_item ;
                    

                    or some similar code that parses the uploaded excel file and just specify the Success Message attribute according to your needs (COMPLETE). That's all you need to do, nothing else - once the file is uploaded into APEX_APPLICATION_TEMP_FILES the page process is executed (just one click).

                    You'll see the spinner during file processing and at the end you'll get a nice green message COMPLETE.

                    Regards,

                    Pavel

                    • 7. Re: Showing progress while running a procedure in the back ground
                      Sven W.

                      Raviteja wrote:

                       

                      Hi Sven,

                      Thank you so much for the post. I don't see "On-Demand" option in 19.1. Is there any other way?

                       

                      Thank you..

                      Not an AJAX Callback on page level, but an APPLICATION process of type on-demand. It seems in 19.1 it is not called on-demand anymore, but AJAX Callback. I'm not sure if also some internal logic has changed.

                       

                       

                       

                      Having said that, I think the way that John Snyders suggested is the better approach. Also note how he uses a slighty different way for the returned js promise.

                       

                      .done and .always instead of .success

                      1 person found this helpful
                      • 8. Re: Showing progress while running a procedure in the back ground
                        Raviteja

                        Hi Pavel,

                                          Thank you so much for your time. I meant the "Load Data" button appears only after the file is uploaded and parsed data preview and available columns are shown like the way they do in the packaged application "Sample Data Loading".

                        I could not load using page process (after submit)  like you have advised in your step three due to the application work flow and few other selections user has to make before submit.

                         

                           I was able to create a sample page on apex.oracle.com and simulate the same error I was getting on my app. If I use the page item "P3456_NEW" in the java script for the "Load Data" button's dynamic action, I get the java script error and the spinner keeps on spinning, BUT loads excel data into the table. If I use the page item "P3456_FILE" in the java script, I get the message "Success" but does NOT load data into the table.

                         

                        Workspace: idea

                        Username: dev

                        Password: july092019

                        Application: 55769

                        Page 3456  (Right most tab)

                        Excel file "employees.xlsx" is in workspace files

                        Procedure "sp_load_xls" loads the table "EMPLOYEES_LOAD"

                         

                         

                        Thank you

                        • 9. Re: Showing progress while running a procedure in the back ground
                          Pavel_p

                          Sorry, the provided credentials do not work for me

                          1 person found this helpful
                          • 10. Re: Showing progress while running a procedure in the back ground
                            Raviteja

                            Hi Pavel,

                                              I apologize for the inconvenience, someone must've locked it. I've reset the password to the same.

                            Workspace: idea

                            Username: dev

                            Password: july092019

                            Application: 55769

                            Page 3456  (Right most tab)

                            Excel file "employees.xlsx" is in workspace files

                            Procedure "sp_load_xls" loads the table "EMPLOYEES_LOAD"

                             

                            Thank you

                            • 11. Re: Showing progress while running a procedure in the back ground
                              Pavel_p

                              Hi,

                              I haven't found so far why it does not work. Sorry, I cannot give it more time today but I'll be back tomorrow.

                              Regards,

                              Pavel

                              1 person found this helpful
                              • 12. Re: Showing progress while running a procedure in the back ground
                                Raviteja

                                Pavel, Thank you so much for your time.

                                • 13. Re: Showing progress while running a procedure in the back ground
                                  Pavel_p

                                  Hi Raviteja,

                                  this one was quite hard to fix.

                                  I think (someone from the APEX dev team would have to confirm) we're hitting here another variation of this bug Re: Using APEX_JSON in Validation Process and returning error text will end up in "Error: SyntaxError: Unexpected end of JSON input" (I've seen another similar thread/bug but cannot find it right now). Anyway, general advice is to avoid APEX_JSON package altogether if possible, which is more or less always since 12.2 (I'm not sure with the minor version number, maybe it is 12.1), and use native JSON capabilities instead. If not possible (i.e. on DBs prior to 12.1 or 2), at least avoid direct writing to htp buffer and use APEX_JSON.INITIALIZE_CLOB_OUTPUT and GET_CLOB_OUTPUT. APEX_JSON is heavily used by APEX engine internally which could cause some collisions.

                                  In your app I created a page 34567 XLS Load PP and modified the PLS_AJAX callback to use json_object_t instead of APEX_JSON package (and added some debug messages just to see what's happening under the hood):

                                  declare
                                    l_filename varchar2(1000) := :p34567_file;
                                    l_ret_json json_object_t  := json_object_t();
                                    l_ret_str  varchar2(32767);
                                  begin
                                    /* ********************** * 
                                    * PL/SQL Process Content * 
                                    * ********************** */
                                    /* Your code here */
                                    apex_debug.message('DEBUGMSG: passing file name: %s', l_filename);
                                    sp_load_xls_data_pp(l_filename);
                                    /*
                                    apex_json.open_object; 
                                    apex_json.write('success', true); 
                                    apex_json.write('message', 'Success'); 
                                    apex_json.close_object; 
                                    */
                                    l_ret_json.put('success', true);
                                    l_ret_json.put('message', 'Success');
                                    l_ret_str := l_ret_json.to_string();
                                    apex_debug.message('DEBUGMSG: returning JSON (success): %s', l_ret_str);
                                    htp.p(l_ret_str);
                                  exception
                                    when others then
                                        apex_debug.message('DEBUGMSG: error in PLS_AJAX: %s', sqlerrm);
                                        /*
                                        apex_json.open_object; 
                                        apex_json.write('success', false); 
                                        apex_json.write('message', sqlerrm); 
                                        apex_json.close_object; 
                                        */
                                        l_ret_json.put('success', false);
                                        l_ret_json.put('message', sqlerrm);
                                        l_ret_str := l_ret_json.to_string();
                                        apex_debug.message('DEBUGMSG: returning JSON (failure): %s', l_ret_str);
                                        htp.p(l_ret_str);
                                  end;
                                  

                                  and also I modified the JavaScript code according to John's suggestion (please note that no page item is being submitted as the File Browse item P34567_FILE had been submitted when the button Upload Data was pressed and at the time of firing the DA is empty/NULL).

                                  /* PLS_AJAX callback */
                                  
                                  var lSpinner$ = apex.util.showSpinner();
                                  
                                  var result = apex.server.process("PLS_AJAX");
                                  
                                  result.done(function (pData) {
                                  
                                    /* Show success message */
                                    if (pData.success) {
                                      apex.message.showPageSuccess(pData.message);
                                    } else {
                                      apex.message.clearErrors();
                                      apex.message.showErrors([
                                        {
                                          type: "error",
                                          location: "page",
                                          message: pData.message,
                                          unsafe: false
                                        }
                                      ]);
                                    }
                                  
                                    //and refresh region (cannot be a successive declarative DA because apex.server.process is asynchronous)
                                    apex.region('LOADED_DATA').refresh();
                                  
                                  }).fail(function (jqXHR, textStatus, errorThrown) {
                                  
                                    /* Show error message */
                                    apex.message.clearErrors();
                                  
                                    apex.message.showErrors([
                                      {
                                        type: "error",
                                        location: "page",
                                        message: errorThrown,
                                        unsafe: false
                                      }
                                    ]);
                                  
                                  }).always(function () {
                                  
                                    //finnally remove the spinner (in all cases)
                                    lSpinner$.remove();
                                  
                                  });
                                  

                                   

                                  Also read the comments in the modified procedure SP_LOAD_XLS_DATA_PP.

                                  Please try yourself and eventually ask questions, the testing file emps.xlsx can be found in Shared Components => Static Application Files.

                                  Regards,

                                  Pavel

                                  1 person found this helpful
                                  • 14. Re: Showing progress while running a procedure in the back ground
                                    Raviteja

                                    Pavel,

                                               Thank you so much for such a detail and helpful post, I really appreciate it. I understand how time consuming this is and you had to take your time away from what you do, for this.

                                    I sincerely respect that, thank you.

                                     

                                    Regards..

                                    1 2 Previous Next