How to call Javascript from inside a PLSQL code in dynamic action of oracle apex

Question

How can we call javascript code from a PLSQL code in dynamic action of Oracle apex. Actually, my requirement is to select a Role(P2_ROLE) from a dropdown list. And than find all the usernames attached to this role using sql query. Once the usernamess are retrieved, all the rows in interactive grid should get coloured for these list of users. The IG contains a column name USER_NAME.

If The role is present in the IG, than I can make it work by writing the below code in dynamic action ---> Javascript code

     $(document).ready(function() {
$("td:nth-child(28)").each(function() {
    if ($(this).text() === apex.item( P2_ROLE ).getValue()) {
        $(this).parent().children().css({'background-color': '#FF9999'});
    }       
});
});

But the issue is the Role that is picked up is not displayed in the interactive grid.

So as a workaround, I want to fetch all the usernames specific to that role using PLSQL and match them with the USER_NAME column in interactive grid using JAVASCRIPT.

But I am not sure how can I call JAVSCRIPT code in a PLSQL code. Basically I want to do something like this :

DECLARE
Cursor c_user_name is
 select distinct user_name  from wf_user_roles where role_name = :P2_ROLE;
l_USER_NAME varchar2(1000);
BEGIN
 OPEN C_USER_NAME ;
  LOOP
   FETCH C_USER_NAME into l_USER_NAME;
   EXIT WHEN C_USER_NAME%NOTFOUND;

  -- Call this JAVASCRIPT code now  
  /*
  $(document).ready(function() {
   $("td:nth-child(28)").each(function() {
    if ($(this).text() === l_USER_NAME) {
        $(this).parent().children().css({'background-color': '#FF9999'});            

     }        
   });
  });
 */

 END LOOP; 
 END;

Can somebody please help me regarding this.

related question: https://stackoverflow.com/questions/52613246/how-to-display-a-plsql-error-message-with-dynamic-action — romeuBraga, Oct 03 '18 at 20:19

score 4 · Accepted Answer · edited Nov 25 '19 at 21:30

4

You can use apex.server.process:

https://docs.oracle.com/cd/E71588_01/AEAPI/apex-server-namespace.htm#AEAPI30050

With this function you can call a PL/SQL process with javascript and do something after this code return something.

Example: https://community.oracle.com/thread/4094475

UPD.

JAVASCRIPT

apex.server.process("new_assign_roles", 
   { x01: 'a_value', x02: 'b_value', x03: 'c_value' },
   {
      success: function(pData) {
         //you can do anything here
         console.log(pData);
         apex.message.alert(pData.v_result);
      }
   }
);

ON DEMAND PROCESS

DECLARE

p_a VARCHAR2(1000) := APEX_APPLICATION.g_x01; 
p_b VARCHAR2(1000) := APEX_APPLICATION.g_x02; 
p_c VARCHAR2(1000) := APEX_APPLICATION.g_x03; 

v_result VARCHAR2(4000) := p_a||' - '||p_b||' - '|| p_c;

BEGIN 
-- you can do anything here  

    apex_json.open_object;  
    apex_json.write('success', true);  
    apex_json.write('v_result', v_result);  
    apex_json.close_object;  

END;

You can see this example here:

https://apex.oracle.com/pls/apex/f?p=145797:33

login with:
workspace: stackquestions
user: user_test
pwd: stackquestions
application: 145797
page: 33

20191125AL > User user_test is locked.

edited Nov 25 '19 at 21:30

Johan Doe

33
1
9

answered Oct 03 '18 at 20:25

romeuBraga

2,135
1
8
20

Hi, Thanks for your suggestion. I am trying to implement this apex.server.process in my code. I have succesfully managed to execute a simple insert statement by using the AJAX Callback Process, but when I am trying to call another function from this process, it doesn't work. Below is that code that I am wrting in my JAvascript : apex.server.process("new_assign_roles", { x01: USER_NAME, x02: l_role, x03: l_justification, x04: l_date, } ); – Abha Oct 05 '18 at 13:41
And, in the AJAX cllback process, I am writing : declare p_return_status varchar2(1000) := NULL; p_msg_data varchar2(1000) :=NULL; begin -- INSERT INTO TEST VALUES( apex_application.g_x01,apex_application.g_x02,apex_application.g_x03,apex_application.g_x04); XXUA_APEX_UMX_PKG.proc_assign_roles( APEX_APPLICATION.g_x01, APEX_APPLICATION.g_x02, APEX_APPLICATION.g_x03, APEX_APPLICATION.g_x04, P_return_status, P_msg_data ); --HTP.p(P_msg_data); end; Can you please suggest what is wrong here. – Abha Oct 05 '18 at 13:41
Just have doubt, Can we even call a function/procedure from AJAX Callback Processes? Or only the DML statements are allowed? – Abha Oct 05 '18 at 14:29
Thanks romeuBraga ! You saved me ! This worked like a charm. I am now successfully able to call plsql procedure from JAVASCRIPT. – Abha Oct 08 '18 at 06:34
One quick question. Does the Refresh (True Action) doesn't work, if we are using apex.server.process in our dynamic action ? I am trying to refresh my interactive grid but it is not getting refreshed !! – Abha Oct 08 '18 at 08:06
Check this tutorial http://hardlikesoftware.com/weblog/2017/02/20/how-to-hack-apex-interactive-grid-part-3/ try to add this code on success section of apex.server.process – romeuBraga Oct 08 '18 at 16:55
Hello, I tried to add the code in success section , but it is not working. As a workaround, i have added a new TRUE action (Submit Page) after my apex.server.process is called.This seems to work fine. Thanks for your help – Abha Oct 09 '18 at 07:04
when try to login using "20191125AL" username and "stackquestions" password, it says invalid credentials, can I get the current valid user and password please!. – Saddam Meshaal Nov 01 '20 at 09:54

How to call Javascript from inside a PLSQL code in dynamic action of oracle apex

1 Answers1