Oracle Apex: Javascript code in PL/SQL Block

Question

Is it possible to have JavaScript code in the PL/SQL block. I want to execute the pl/sql block containing JavaScript code on submit in oracle Apex page process.

DECLARE
  v_count   NUMBER;

  BEGIN
        select count(*) into v_count
        from summary
        where prd_items = 'Total';

 HTP.p ('<script type="text/javascript">');
 HTP.p (   'alert(''The value of Total for BU is ' ||v_count|| '.\n'
      || 'You have to enter correct values to proceed further \n'');');
 HTP.p ('</script>');
END;

I have Submit button in my page region and this pl/sql block is page processing item and execute on page submit(Conditional:Submit).

But I am not able to pop-up the alert box. Please advise.

Thank you.

Where is this code defined? Your JavaScript will be coming back as the result of an asynchronous request to the server, but you also need to insert it into your page in order for it to be executed. — Drumbeg, Dec 14 '15 at 09:36
Do you get any error in the browser console? Would [this](http://forums.devshed.com/oracle-development-96/using-javascript-pl-sql-224043.html) help? — Hawk, Dec 14 '15 at 09:47
NO I am not getting any errors and My code is part of page processing after Submit. — ApexDev, Dec 14 '15 at 10:55

brenners1302 · Accepted Answer · 2015-12-22T06:02:09.033

Is it possible to have JavaScript code in the PL/SQL block?

YES

But, what you're trying to do wont work which is passing javascript function AFTER SUBMIT.It'll only work if you change the point of execution to AFTER HEADER.

Alternatively, if you just want to validate the values entered and doesn't want to use apex validation, you can use APEX_ERROR package.Try this.

DECLARE
  v_count   NUMBER;

  BEGIN
        select prd_items into v_count
        from summary
        where prd_items = 'Total';
        -- I dont really know what you want to 
        --accomplish with this query but Im pretty sure 
        --It will not return a number
        -- if you want to count the number of prd_items it should be like this
        --select COUNT(*) 
        --into v_count
        --from summary
        --where prd_items = 'Total';


    APEX_ERROR.ADD_ERROR(
      p_message            => 'The value of Total for BU is '||v_count||'.<br>'||
                              'You have to enter correct values to proceed further',
      p_display_location   => apex_error.c_inline_in_notification 
    );  

END;

EDIT: if you want to show the error if count not equal to 100 then do something like this:

DECLARE
  v_count   NUMBER;

  BEGIN

     Select COUNT(*) 
     into v_count
     from summary
     where prd_items = 'Total';

  IF v_count != 100 THEN
    APEX_ERROR.ADD_ERROR(
      p_message            => 'The value of Total for BU is '||v_count||'.<br>'||
                              'You have to enter correct values to proceed further',
      p_display_location   => apex_error.c_inline_in_notification 
    );  


 END IF;
END;

I tried this, but the error alert pop up message is showing up for both correct and wrong values. I wanted to alert only if count is not 100. — ApexDev, Dec 22 '15 at 05:56
that is where `IF statement` should go in.if you have read my comment on the code, you should change your query to what i have written and add an if statement — brenners1302, Dec 22 '15 at 05:59

score 1 · Answer 2 · answered Dec 14 '15 at 13:04

To embed the javascript code from the pl/sql procedure you will have to place the procedure at a "Before Header" point. But i do not think this is the best solution for what you are trying to achieve.

What you are trying to do is to add a validation right? If so why not use the apex validations. Create a validation with options like this:

Identify the validation level:Page Item
Identify the Page Item that is to be validated: Select the item you want.
Select a validation type: PL/SQL
Pick the type of validation you wish to create:Function Returning Error Text
Validation Code:

    DECLARE  v_count          NUMBER;
  V_validation_msg VARCHAR2(500);
BEGIN
  SELECT prd_items INTO v_count FROM summary WHERE prd_items = 'Total';
  V_validation_msg:='The value of Total for BU is ' ||v_count|| '.\n' || 'You have to enter correct values to proceed further';
  IF 1= 1 THEN --add some condition here if you want
    RETURN V_validation_msg;
  ELSE
    RETURN NULL;
  END IF;
END;

When Button Pressed: Your Submit button.

The problem is, I want this above process to run after the submit process. I have One more page process before this, which will calculate the values and then I want to pop up the alert if total is not equal to 100. — ApexDev, Dec 14 '15 at 14:33
Why don't you make this calculation inside the Validation Code procedure? — Cristian_I, Dec 14 '15 at 14:45
The calculation is part of the classic report where I am entering the values for remaining rows and Once I press `submit` button, my process will calculate the total value of the column and store the value in an Item. If item value is 100, then no need of an alert, else I need to pop up the alert informing user about the value is less or greater than 100. — ApexDev, Dec 14 '15 at 15:29

Oracle Apex: Javascript code in PL/SQL Block

2 Answers2