This discussion is archived
13 Replies Latest reply: Oct 31, 2008 10:04 AM by 668162 RSS

javascript in pl/sql process block

575150 Newbie
Currently Being Moderated
Hi,
Can someone please tell me why this code is not working.....
I have put this in my pl/sql process....
htp.p('<script language=javascript>');
htp.p('var r=confirm("This is a duplicate record , do you want to proceed?");');
htp.p('if (r==true)');
htp.p('{document.wwv_flow.submit();}');
htp.p('else{ }');
htp.p('</script>');

Please help

thanks and regards,
deepa
  • 1. Re: javascript in pl/sql process block
    135285 Oracle ACE
    Currently Being Moderated
    Deepa,

    what do you expect that it does? Does it not execute when your page gets rendered?
    Do you get an error? You have to give more details, "code is not working" leaves a lot of space for assumptions.

    A easier way to try out if your Javascript works is to put the code into a HTML region without the htp.p calls.

    Patrick
    ----------------------------------------------------------------------------------------------------
    My APEX Blog: http://inside-apex.blogspot.com
    The ApexLib Framework: http://apexlib.sourceforge.net
    The APEX Builder Plugin: http://sourceforge.net/projects/apexplugin/
  • 2. Re: javascript in pl/sql process block
    575150 Newbie
    Currently Being Moderated
    Hi Patrick,

    thanks for your response.
    I dont get any error , what i want to see is that confirm box.
    I call this proc on click of a button and this process checks if there are any duplicate rows, if yes then it should display me this confirm box, and submit the page....the page submits but the code within the <script> tag does not get called.

    Below is my entire proc, can you please help me?

    Declare
    v_found number:=0;
    v_found2 number;
    cursor cur1 is
    select * from table1;

    Begin
    for v_rec in cur1
    loop

    if v_rec.customer_name = :P1_CUST and v_rec.days=:P1_NOD and
    v_rec.current_contract=:P1_CCC and v_rec.customer_no=:P1_CUST_NO

    then

    v_found :=1;
    v_found2 :=2;
    --raise_application_error(-20000,v_found);
    exit;

    else
    v_found := 2;
    --raise_application_error(-20000,v_found);
    end if;
    if v_found2=2 then
    exit;
    end if;
    end loop;

    --JS for the confirmation box
    if v_found=1 then

    htp.p('<script language=javascript>');
    htp.p('var r=confirm("This is a duplicate Bid , do you want to proceed?")
    if (r==true)
    {

    var a=document.getElement.ById(''P1_NO').value=1;
    document.wwv_flow.submit();



    }
    else
    {
    var a=document.getElement.ById(''P1_NO').value=2;
    document.wwv_flow.submit();
    }
    ');


    htp.p('</script>');


    end if;
    end;



    I want the users to be able to see tat comments box , if there are duplicate records,and the page shud submit....
    but its not appearing

    Regards,
    Deepa
  • 3. Re: javascript in pl/sql process block
    135285 Oracle ACE
    Currently Being Moderated
    Deepa,

    try the following code
    htp.p('&lt;script type="text/javascript">');
    htp.p('
      var r=confirm("This is a duplicate Bid, do you want to proceed?");
      if (r==true)
      {
        var a=document.getElement.ById("P1_NO").value=1;
        document.wwv_flow.submit();
      }
      else
      {
        var a=document.getElement.ById("P1_NO").value=2;
        document.wwv_flow.submit();
      }
    ');
    htp.p('</script>');
    There where some issues with quotes and a missing ; in your code.
    Are you testing your page with IE? Because Firefox would show you JavaScript errors under Tools\Error Console. IE is a little bit weak in that area.

    BTW, it would also be nice if you would update your forum profile with your name. user572147 is so anonymous.

    Patrick
    ----------------------------------------------------------------------------------------------------
    My APEX Blog: http://inside-apex.blogspot.com
    The ApexLib Framework: http://apexlib.sourceforge.net
    The APEX Builder Plugin: http://sourceforge.net/projects/apexplugin/
  • 4. Re: javascript in pl/sql process block
    575150 Newbie
    Currently Being Moderated
    Hello Patrick,

    It still did not work ,
    I am not able to figure out why it doesnt get executed.
    If i try the same script in a html region , it works well....

    I tried it in firefox too , but it doesnt error out , but the script doesnt get executed....all the code before the script runs.
    I am not even able to see that confirmation box ???


    Regards,
    Deepa
  • 5. Re: javascript in pl/sql process block
    Arie Geller Guru
    Currently Being Moderated
    Hello,

    >> I am not able to figure out why it doesnt get executed.

    You can’t fire a JavaScript code from within a PL/SQL code – the first is running on the client side, and the other on the server.

    Your htp.p statements sequel do create a script, while your PL/SQL is running, but nothing cause this script to be execute. It can only happen while your page is being rendered (which means that this kind of use only makes sense in a before header PL/SQL block, so the created script will be part of the page HTML code, prior to rendering).

    What you need, in your case, is the reverse approach – calling PL/SQL from JavaScript code – which is possible, using AJAX. You can start learning about using AJAX in APEX in here - http://htmldb.oracle.com/pls/otn/f?p=11933:63 .

    It seems like what you are trying to do is a validation process. Pressing the button should fire a JavaScript code, which in turn will fire an on-demand PL/SQL procedure, and according to the returned results will display a confirm dialog box, or submit the page (and in this case, you should use the APEX built-in JS function doSubmit() - http://apex.oracle.com/pls/otn/f?p=38704:2:::NO::P2_ID1:92 ).

    Regards,
    Arie.
  • 6. Re: javascript in pl/sql process block
    575150 Newbie
    Currently Being Moderated
    Hello Arie,

    Thank you very much for your respose.

    What I want is :
    1) On click of a button , a process runs to check if there are duplicates
    2)If yes , then I want that Confirmation box to appear
    3)and run another insert process when the "OK" in the confirm box is choosen.

    But I have fired a Js from a PL/SQL block before , displyed alerts etc but the page should submit

    I even added the doSubmit() did not work, I even tried something like this:

    1)Define this confirm box JS in my page header.
    2)Calling this function as htp.p('duplicate_record();');
    3)anyhow the page will submit when the button is pressed , so ia m guessing it should show me that alert but it did not.


    Is there any alternative way of acheiving this


    Regards,
    Deepa
  • 7. Re: javascript in pl/sql process block
    Arie Geller Guru
    Currently Being Moderated
    Hello Deepa,

    >> But I have fired a Js from a PL/SQL block before , displyed alerts etc

    Please don’t take it personally, but allow me to doubt that. As I said in my first post, this is not possible, as the PL/SQL code runs on the server, and JavaScript code runs on the client.

    People sometimes tend to confuse creating a JavaScript code, using PL/SQL packages, and actually running the code. You can use any of the ‘On Load’ firing points to incorporate a JavaScript code – created using PL/SQL means – into your page HTML code. The APEX engine will do that for you, according to the point of execution you chose, as part of creating the HTML code for the entire page. Only after that, the page is being rendered by your client (browser) and only then the JavaScript is being fired. At this point it has nothing to do with the PL/SQL code. Now you are on the client side.

    In your case, a validation process is being fired after the page has been rendered for the first time. Now you on the client side. If you want to fire a PL/SQL code (to interact with the database) without submitting the page, you must use AJAX (as I pointed out in my first post).

    An alternative option is to use the current (built-in) APEX approach – submitting the page, perform validations, and when in needs, create an error message – Using a before header PL/SQL processes, or if you are using the built-in PL/SQL validation option, its own error message field – and re-render the page, allowing the user to take corrective actions.

    You should search this forum for “validation && ajax” and you’ll probably find several threads on the subject.

    Regards,
    Arie.
  • 8. Re: javascript in pl/sql process block
    575150 Newbie
    Currently Being Moderated
    Thanks Arie,

    I will give your approach a try.


    Regards,
    Deepa
  • 9. Re: javascript in pl/sql process block
    135285 Oracle ACE
    Currently Being Moderated
    Deepa,

    what is the "Process Point" of your PL/SQL process?
    When you view the HTML output in a text area, do you see somewhere your code?

    Patrick
    ----------------------------------------------------------------------------------------------------
    My APEX Blog: http://inside-apex.blogspot.com
    The ApexLib Framework: http://apexlib.sourceforge.net
    The APEX Builder Plugin: http://sourceforge.net/projects/apexplugin/
  • 10. Re: javascript in pl/sql process block
    575150 Newbie
    Currently Being Moderated
    Hi Patrick,

    The process point was the whole problem,

    I changed the proces to on Load after footer , and the code started working :).
    I cant beleive I did not notice that.
    Thank you very much.

    Regards,
    Deepa
  • 11. Re: javascript in pl/sql process block
    431127 Newbie
    Currently Being Moderated
    What you need, in your case, is the reverse approach
    – calling PL/SQL from JavaScript code – which is
    possible, using AJAX...

    It seems like what you are trying to do is a
    validation process. Pressing the button should fire a
    JavaScript code, which in turn will fire an on-demand
    PL/SQL procedure, and according to the returned
    results will display a confirm dialog box
    Hi Arie,
    This is exactly what I am struggling to do, but I don't think is working...is there a good example somewhere, with good documentation saying what goes where? I've tried looking at Carl's On-Demand page example and I just can't tell if I'm following it correctly.

    Based on what is entered in P4_PROJECT_NUM, I need to verify that the value isn't already in the database when the user tabs out of the field.

    My javascript for page 4 (in HTML Header):
    function checkCO()
    {      
    var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=validate_value',4);

    get.add('ID', $x('P4_PROJECT_NUM').value)
    gReturn = get.get();

    if(gReturn) // if function return true
    {
    alert($x('P4_PROJECT_NUM').value + ' already exists in database.');
    }
    get = null;
    }
    //-->
    </script>

    My onDemand process, from the Application Processes:

    declare
    v_count number;

    cursor cr_check_co is
    select count(*)
    from usd_changeorders
    where chg_ref_num = v('ID');

    begin
    open cr_check_co;
    fetch cr_check_co into v_count;
    close cr_check_co;

    if (nvl(v_count, 0) = 0) then
    return true;
    else
    return false;
    end if;
    end;

    I am calling the javascript from the onBlur event of the field I want verified.

    I am getting an alert, but it is displaying every time, whether the value is valid or not.

    Can you tell me what I am doing wrong?

    Thanks!!
    Janel
  • 12. Re: javascript in pl/sql process block
    Arie Geller Guru
    Currently Being Moderated
    Hello Janel,

    >> I am getting an alert, but it is displaying every time, whether the value is valid or not.

    Which means that gReturn is always true. That is not the case if you have any errors in the ondemand process (or in the APEX process in general). That leads me to think that the logic of your PL/SQL process is in fault.

    You should use the SQL Commands to check if your PL/SQL block returning the desired results.

    The following is a very simple example code I’m using to check existence of values in the database:
    DECLARE
      l_flag  varchar2(1);
    BEGIN
    select 1 
    into l_flag
    from emp
    where empno = 7788;
    
    htp.p('true');
    
    EXCEPTION
      when no_data_found then htp.p('false');
    END
    Hope this helps,
    Arie.
  • 13. Re: javascript in pl/sql process block
    668162 Newbie
    Currently Being Moderated
    Has anyone got a good working example of this validation. I used the javascript example from above...and made mods based on my scenerio, and the last pl/sql on-demand process from above. Mine fires evertime too. So to test it, I commented everything else in the on-demand process except passing false, and it still fired. Can someone tell me what I'm doing wrong? Here is what I have...


    (* ----PL/SQL on-demand Process - Named 'NameExists' ----*)

    DECLARE
    l_flag varchar2(1);
    BEGIN
    select 1
    into l_flag
    from personnel
    where last_name = v('ID');

    htp.p('true');

    EXCEPTION
    when no_data_found then

    htp.p('false');
    END;


    (* ---- Javascript Function - In the Page HTML Header ----*)

    function CheckName()
    {
    var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=NameExists',0);

    get.add('ID', $x('P2_LAST_NAME').value)
    gReturn = get.get();

    if(gReturn) // if function return true
    {
    alert($x('P2_LAST_NAME').value + ' ' + $x('P2_FIRST_NAME').value + ' already exists in the database. Please verify that this is not the same Person.');
    }
    get = null;
    }
    //-->
    </script>

    (*---- Item Form Element Attribute ----*)
    onchange="CheckName()"


    Thanks so much in advance!!
    Juanita