1 2 Previous Next 16 Replies Latest reply: Aug 14, 2009 11:44 AM by 704520 RSS

    How to call stored procedure from javascript? (about Google Suggest, AJAX)

    704520
      Hi I want to implement a text field so that it behaves like [Google Suggest|http://www.google.com/webhp?complete=1&hl=en] .

      I read this post .

      Now I've setup everything according to that document. But it just doesn't work. And I don't know why.

      I think problems may fall into the following three categories:
      1. Does the text field and the page invoke the proper javascript?
      2. Does the javascript successfully call the stored procedure?
      3. Can the stored procedure correctly return the formatted result?

      I am affirmative for 1 and 3, but I'm not sure about 2. Because I don't know how to tell if a stored procedure has been called? Is there a PL/SQL statement that I can query in SQL*Plus?

      Also, I would to know how to debug AJAX in APEX. It involves many things.

      Last, I used APEX 3.2 and Oracle XE. I cannot find either dads.conf or marvel.conf file. Is "/apex/" the virtual directory for APEX?

      Thanks a lot!
        • 1. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
          dmcghan
          Buffalo,

          Are you trying to call a stored procedure from within an Apex application?

          This is a little dated but can give you an idea of how to call an application process from JavaScript for Ajax:
          http://htmldb.oracle.com/pls/otn/f?p=11933:51

          An application can be any PL/SQL - even calls to stored procedures. You can even kick off scheduled jobs in DBMS_SCHEDULER.

          Regards,
          Dan

          http://danielmcghan.us
          http://sourceforge.net/projects/tapigen

          You can reward this reply by marking it as either Helpful or Correct ;-)
          • 2. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
            704520
            Yes, I am trying to call the stored procedure, according to the demo (the last paragraph).
            <script src="wwv_flow_file_mgr.get_file?p_security_group_id=563610923303911988&p_flow_id=22777&p_fname=ac.js" type="text/javascript"> </script>
            
            <script language="JavaScript1.1" type="text/javascript">
            function iac() 
              {  InstallAC(document.wwv_flow,document.getElementById('P1_REPORT_SEARCH'),"","YourSchema.incsearch","en"); }  
            </script>
            <b>IncSearch</b> is the stored procedure. I just have no idea whether I have successfully called the stored procedure from the javascript line. No clue at all.

            I will try your method tomorrow morning at my development machine. Thanks!
            • 3. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
              692192
              Hi,

              I havent really gone through the links on this post and I know that the solution that I am suggesting is not an elegant one but it will definately solve the issue

              From javascript using set a application level item to a value(this application level item will be like a flag that this javascript function was called)

              Using window.open call the same page again

              Now have a page process which gets called onload and depends on the value in this application item.

              From this page process call the stored function

              Regards,
              Vishal
              • 4. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                d_wilhelm
                Hello

                i guess that this line
                <script src="wwv_flow_file_mgr.get_file?p_security_group_id=563610923303911988&p_flow_id=22777&p_fname=ac.js" type="text/javascript"> </script>
                won't work in your application. If you uploaded the file ac.js to your static files that line should read:
                <script src="#WORKSPACE_IMAGES#ac.js" type="text/javascript"> </script>
                As for the debugging, try firefox with the firebug plugin. You can than easily check if the requiered JavaScript files get loaded, see the requestst sent to the server and the responses, step through the javascript code and so on.
                • 5. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                  MatthiasHoys
                  Hello,

                  I recently also ran into problems with this and I will post my solution here:
                  1) if you need to pass parameters to your procedure, create it using "Flexible Parameter Passing". Then parse the parameters out of the array and put them in local variables inside your PL/SQL procedure.
                  Example:
                  CREATE OR REPLACE PROCEDURE MATTHIASH.incsearch(name_array IN owa.vc_arr,
                       value_array IN owa.vc_arr) as
                    l_List1 varchar2(4000);
                    l_List2 varchar2(4000);
                    l_query varchar2(255);
                    l_separator varchar2(10) default '';
                    qu varchar2(4000) default '';
                    hl varchar2(4000) default '';
                  BEGIN
                    FOR i IN 1 .. name_array.COUNT  
                    LOOP
                         IF name_array(i) = 'qu' THEN
                              qu := value_array(i);
                         ELSIF name_array(i) = 'hl' THEN
                              hl := value_array(i);
                         END IF;
                    END LOOP;
                    l_query := qu||'%';
                    FOR x IN ( 
                    select object_name, object_id from user_objects
                    where upper(object_name) like upper(l_query) and upper(object_type) = upper(hl) order by 1 asc)
                    LOOP
                      l_list1 := l_List1 || l_separator || '"' || x.object_name || '"';
                      l_list2 := l_List2 || l_separator || '"' || x.object_id || '"';
                      l_separator := ',';
                    END LOOP;
                      
                    owa_util.mime_header('text/html', false);
                    owa_util.http_header_close;
                    --htp.p('sendRPCDone(frameElement, "'|| qu ||'", new Array(' || l_List1 || '), new Array(' || l_List2 || '), new Array(""));');
                    htp.p('sendRPCDone(frameElement, "' || qu || '", new Array(' || l_List1 || '), new Array(' || l_List2 || '), new Array(""));');
                  END;
                  /
                  2) grant EXECUTE rights to APEX_PUBLIC_USER (the user APEX uses to connect to the database) on the procedure
                  grant execute on incsearch to apex_public_user;
                  3) upload the ac.js file as static file to your application
                  4) put the following javascript code in the HTML Header of your APEX page:
                  <script src="#WORKSPACE_IMAGES#ac.js" type="text/javascript"></script>
                  <script language="JavaScript" type="text/javascript">
                  function iac() 
                    {  
                  InstallAC(document.wwv_flow,document.getElementById('P1_X'),"","!MATTHIASH.incsearch","&P1_OBJECT_TYPE.");
                    }
                  </script>
                  In my example, P1_X is a text field and P1_OBJECT_TYPE is a dropdown list with all user object types.

                  Good luck,
                  Matthias Hoys
                  • 6. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                    704520
                    Hi d_wilhelm, I did change the location of javascript as you indicated. I am sure about it because when I "View Page Source" in Firefox, it shows me a brand new dynamic link and by clicking on that link, the content of ac.js shows up.

                    The thing that I'm not sure is whether the ac.js called the stored procedure. Thanks!
                    • 7. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                      704520
                      Hi Matthias, thanks for your help!

                      I would like to know what changed you made to ac.js file. Did you change the virtual directory location? (from "/complete/" to "/apex/")

                      In your script,
                      InstallAC(document.wwv_flow,document.getElementById('P1_X'),"","!MATTHIASH.incsearch","&P1_OBJECT_TYPE.");
                      I just kept "en" as the last parameter, and according to the demo document, it seems to be the language setting. Is that the problem?
                      Examining the source of the Google page shows that a script called InstallAC is run when the page loads to hook the javascript to a form field:
                      <SCRIPT>InstallAC(document.f,document.f.q,document.f.btnG,"search","en");</SCRIPT>
                      The parameters it takes are the names of the form, the text field, the submit button, the command to call on the server, and the language. 
                      • 8. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                        704520
                        Now I redo everything just as the demo instructed.

                        It still doesn't work.

                        The only difference is this: the demo is hosted at the <b>htmldb.oracle.com</b> site while mine is running locally with <b>Oracle Express Edition and APEX 3.2 on Windows XP</b>. The demo's author changed the <b>virtual path</b> according to the configuration of the dads.conf or marvel.conf, but I don't have either of them, because I installed the APEX this way:
                        [Installing from the Database and Configure the Embedded PL/SQL Gateway|http://download.oracle.com/docs/cd/E14373_01/install.32/e13366/db_install.htm#insertedID4]

                        I can only guesstimate from my local URL that my virtual path is "/apex/" (or should I use "\\apex\\" instead on Windows?), which I use to make change to the ac.js file. Totally frustrated..
                        http://127.0.0.1:8080/apex/f?p=101:4:372709965338086:::::
                        • 9. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                          MatthiasHoys
                          Hello,

                          Yes, I made a change to the ac.js file.
                          This part:
                          if(Pb)F="complete";else F="/pls/apex/"+na;
                          Our APEX pages are accessed as follows: http://<servername>:7777/pls/apex/f?p=4550:1:0.

                          Also in my example I used the "hl" parameter not as the language variable but as an extra variable that can be selected from a dropdown list. Guess in your case you can leave it to "en", if you don't do anything with it in your procedure.
                          Did you try leaving the language parameter empty?

                          Regards,
                          Matthias Hoys
                          • 10. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                            MatthiasHoys
                            Buffalo,

                            I will try my example on XE and let you know if it worked.

                            Matthias
                            • 11. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                              MatthiasHoys
                              Buffalo,

                              I managed to get it working on XE with APEX 3.2!
                              It took some more time than I expected because my XE installation was still on an older version of Apex and I had to upgrade it first...

                              But this is what I did:
                              - in the ac.js file, I changed the path to "/apex/" and I added the ac.js file to my application as static file. So like you did.
                              - I copied my application to XE and copied my procedure to the schema that is linked to my application
                              - I granted execute rights to ANONYMOUS on my procedure

                              Now initially this didn't work...
                              Then I suddenly remembered something: if you want to execute a procedure through the pl/sql gateway in XE, you have to tell XE what procedure this will be.
                              You do this by modifying the APEX_030200.wwv_flow_epg_include_mod_local function!

                              This is the code of the modified function for my example:
                              CREATE OR REPLACE function APEX_030200.wwv_flow_epg_include_mod_local(
                                  procedure_name in varchar2)
                              return boolean
                              is
                              begin
                                  --return false; -- remove this statement when you modify this function
                                  --
                                  -- Administrator note: the procedure_name input parameter may be in the format:
                                  --
                                  --    procedure
                                  --    schema.procedure
                                  --    package.procedure
                                  --    schema.package.procedure
                                  --
                                  -- If the expected input parameter is a procedure name only, the IN list code shown below
                                  -- can be modified to itemize the expected procedure names. Otherwise you must parse the
                                  -- procedure_name parameter and replace the simple code below with code that will evaluate
                                  -- all of the cases listed above.
                                  --
                                  if upper(procedure_name) in (
                                        'MATTHIASH.INCSEARCH') then
                                      return TRUE;
                                  else
                                      return FALSE;
                                  end if;
                              end wwv_flow_epg_include_mod_local;
                              /
                              After this, the AJAX function worked as expected...

                              HTH
                              Matthias Hoys

                              Edited by: mhoys on Aug 13, 2009 3:24 PM
                              • 12. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                                704520
                                Dear Matthias, you're amazing! Thanks so much! It finally works! Thanks to the modification of APEX_030200.wwv_flow_epg_include_mod_local function.

                                Actually, I almost killed myself when modifying that function, because [I dropped it and everything went crazy|http://forums.oracle.com/forums/thread.jspa?threadID=943486] ! Now it gets back to normal :-)

                                Thanks for your efforts!
                                • 13. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                                  704520
                                  Another thing comes into my mind: since my text field is not a search field, I don't want it auto-submit after I select an item form the AJAX list. What should I modify in order to disable the auto-submit for ac.js?
                                  • 14. Re: How to call stored procedure from javascript? (about Google Suggest, AJAX)
                                    MatthiasHoys
                                    Hi,

                                    Glad your issue got solved!

                                    As for the auto-submit: I did a quick test and if you search on the following piece of code in ac.js and remove it, the auto-submit should be disabled:
                                    sa.submit();
                                    This is in the function with the name "Gb".

                                    HTH
                                    Matthias Hoys

                                    Edited by: mhoys on Aug 14, 2009 9:28 AM
                                    1 2 Previous Next