13 Replies Latest reply: Oct 31, 2008 12:04 PM by 668162 RSS

    javascript in pl/sql process block

    575150
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Thanks Arie,

                        I will give your approach a try.


                        Regards,
                        Deepa
                        • 9. Re: javascript in pl/sql process block
                          135285
                          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
                            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
                              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
                                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
                                  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