title: Concurrent requests from PL/SQL
body: <p>I want to work out the logic on following requirements.</p>
  
  <p>XML Publisher Data Definition will fire the beforeReport trigger which calls function that returns TRUE or FALSE. </p>
  
  <p>This function is contained within PL/SQL package. 
  Within this function first the concurrent request will be sent in order to SQL<em>Load some data into APPS database table. After, report will show records that didn't pass. SQL</em>Loader is called as concurrent request with Executable that is HOST(shell script). </p>
  
  <p>Somehow the RETURN(sucess or failure) from SQL*Loader should be checked within PL/SQL. Meaning, I need some output parameters from concurrent request? Or?</p>
  
  <p>If SUCCESS then it proceeds forward. If FAILED, it should check the database table to see if there are old records to process.</p>
  
  <p>So here the next 'event' follows which SHOULD happen in beforeReport trigger. Call procedure which I already have, which performs some validations upon table records and calls API to create person within HZ_PARTIES table. But depending on SQL*Loader return status:</p>
  
  <p>-if there are old record, then call API, but return WARNING status of concurrent request. What's the best way to return WARNING in this case?</p>
  
  <p>-if there are no old records then API is not submitted but (execution)report will show that SQL*Loader failed and there is no data to show. </p>
  
  <p>In short, I need to work out how to structure functions and procedures, that is:</p>
  
  <p>Say, I put the function which is called in beforeReport trigger as public, and then within this function everything else happens: 
  1) SQL*Loader concurrent request - but is it better to put this into some procedure or nested function and then call it within the above "main" function?
  Then, if its success which I get as out parameter, step 2:</p>
  
  <p>2) Validation and API - can I put this inside private procedure or function as well, as above?</p>
  
  <p>3) Report will be shown. Containing results from two prior steps.</p>
  
  <p>I just need some clarification on this what is best to do. I can clear up some questions further if needed.</p>
  
  <p>Thanks.</p>