1 2 Previous Next 15 Replies Latest reply: Jul 17, 2012 5:48 PM by lxiscas RSS

    How to pass a cursor's result to javascript code in APEX?

    lxiscas
      Hi, Guys:

      I am new to Apex and java script. I was wondering if anyone could tell me what document I should look for to pass a cursor generated by PL/SQL to java script code in APEX?

      Thanks.

      Sam
        • 1. Re: How to pass a cursor's result to javascript code in APEX?
          VC
          lxiscas wrote:
          Hi, Guys:

          I am new to Apex and java script. I was wondering if anyone could tell me what document I should look for to pass a cursor generated by PL/SQL to java script code in APEX?
          No, You won't be able to pass the PL/SQL cursor to JavaScript.

          Because PL/SQL is server-side and JavaScript is client/browser side, that means JavaScript can only get hold of what ever is already loaded in the browser.

          Can you tell me why would you need to do that? so that I can explain you how to do the same.

          Better you learn JavaScript by going through this easy tutorial http://www.w3schools.com/js/default.asp
          • 2. Re: How to pass a cursor's result to javascript code in APEX?
            lxiscas
            Hi:

            Thanks for your advice. I have been learning java script basics with W3 school for several days. However, I need more suggestion about direction. I need to develop a GIS system with APEX and Google map service. In this application I need to mark multiple points on Google map with javascript; these points' geocoding will be retrieved from a database. I can draw map with markers, and I can write PL/SQL to generate geocoding list, but I do not know how to pass these geocoding values to java script code.

            Thanks again for your advice.

            Sam
            • 3. Re: How to pass a cursor's result to javascript code in APEX?
              Joe Upshaw
              Just create some hidden page items to store on the page. P13_POINT_1, P13_POINT_2, etc.

              Not sure how this process is kicked off but, say, for example the user clicks a button. Create a Dynamic Action on click of the button. Select Advanced for the type. For the action select execute PL/SQL.

              In this one, write the PL/SQL to set the page items to your desired values.

              OPEN lcsr_GetPoints;
              FETCH lcsr_GetPoints into :P13_POINT_1, :P13_POINT_@, etc.
              CLOSE lcsr_GetPoints

              ... or something to that effect.

              Now, on the Dynamic action, select to Add True Action. This time, for the Action, select execute JavaScript. Now, write the javsacript to do whatever is needed with these values. $v('P13_POINT_1') gets the value.

              -Joe
              • 4. Re: How to pass a cursor's result to javascript code in APEX?
                VC
                lxiscas wrote:
                Hi:

                Thanks for your advice. I have been learning java script basics with W3 school for several days. However, I need more suggestion about direction. I need to develop a GIS system with APEX and Google map service. In this application I need to mark multiple points on Google map with javascript; these points' geocoding will be retrieved from a database. I can draw map with markers, and I can write PL/SQL to generate geocoding list, but I do not know how to pass these geocoding values to java script code.

                Thanks again for your advice.
                Simple, you have to use ajax to do the server side processing and printing the data to browser and handling the returned data in javascript

                What apex version you are on? If you are lon a lower version than 4 see this {message:id=10396224}
                create an application on-demand process called 'test' with following code
                Begin
                // you are printing the apex global variable that is sent from ajax call
                //use wwv_flow.g_x01 syntax to use the value in pl/sql sent from javascript
                htp.prn(wwv_flow.g_x01);
                end;
                
                
                //execute this in your page javascript
                <script>
                $.post('wwv_flow.show', 
                       {"p_request"      : 'APPLICATION_PROCESS=test',
                        "p_flow_id"      : $v('pFlowId'),
                        "p_flow_step_id" : $v('pFlowStepId'),
                        "p_instance"     : $v('pInstance'),
                        "x01" : 'test'
                // you can use these built-in variables x01 through x10 for passing them to server
                       },
                        function(data){ 
                         //handle the returned data here
                         alert(data);
                        }
                       );
                </script>
                • 5. Re: How to pass a cursor's result to javascript code in APEX?
                  lxiscas
                  Hi, Joe:

                  Thanks for your advice. I was wondering if I use dynamic action, is there any hidden item of array or a table? the data that I try to return is a array of struct, every row contains name, Address, and geocoding, and there can be hundreds of rows. If I set up hidden items to hold scalar value, I do not know how many hidden items I should set.

                  Thanks.

                  Sam
                  • 6. Re: How to pass a cursor's result to javascript code in APEX?
                    VC
                    lxiscas wrote:
                    Hi, Joe:

                    Thanks for your advice. I was wondering if I use dynamic action, is there any hidden item of array or a table? the data that I try to return is a array of struct, every row contains
                    name, Address, and geocoding, and there can be hundreds of rows. If I set up hidden items to hold scalar value, I do not know how many hidden items I should set.
                    Use my above ajax example to get the JSON data and handle the same in JavaScript to loop each row of that json array.

                    http://stackoverflow.com/questions/1078118/how-to-iterate-over-a-json-structure
                    //try this in APEX sql commands and use the same in on-demand application process to print
                    //undocumented api
                    begin
                      apex_util.json_from_sql('select * from emp');
                    end;
                    I would prefer PLJSON utility packages rather than using the above undocumented API http://sourceforge.net/projects/pljson/
                    • 7. Re: How to pass a cursor's result to javascript code in APEX?
                      fac586
                      lxiscas wrote:
                      Hi, Joe:

                      Thanks for your advice. I was wondering if I use dynamic action, is there any hidden item of array or a table? the data that I try to return is a array of struct, every row contains name, Address, and geocoding, and there can be hundreds of rows. If I set up hidden items to hold scalar value, I do not know how many hidden items I should set.
                      An "array of struct"? In PL/SQL? Not really the correct terminology. An associative array of records? A collection of objects? Post the code for clarification.

                      The 2 formats that are suitable for passing data over AJAX are XML and JSON. I'd probably opt for the former as I'm familiar with (and a big fan of) How to create XML from relational tables based on an XML Schema ?. There's a JSON alternative in the [as yet undocumented] <tt>apex_util.json_from_sql</tt> and related methods.
                      • 8. Re: How to pass a cursor's result to javascript code in APEX?
                        lxiscas
                        Hi, Joe:

                        It is a cursor actually. I am reading collection decription, hopefully it would help. I will check apex_util.json_from_sql. Thanks a lot!

                        Sam
                        • 9. Re: How to pass a cursor's result to javascript code in APEX?
                          VC
                          lxiscas wrote:
                          Hi, Joe:

                          It is a cursor actually. I am reading collection decription, hopefully it would help. I will check apex_util.json_from_sql. Thanks a lot!

                          Sam
                          If you want more control over the way you generate the json data then download this http://sourceforge.net/projects/pljson/

                          And get back to me if you need any help with json and JavaScript/jQuery
                          • 10. Re: How to pass a cursor's result to javascript code in APEX?
                            lxiscas
                            Hi, VC:

                            Thanks for your help. I am back to the problem again. I tried to install PL_JSON on the our developing schema with SQL developer as you said. But I got the following error message:

                            -- Setting optimize level --
                            session SET altered.
                            -----------------------------------
                            -- Compiling objects for PL/JSON --
                            -----------------------------------
                            anonymous block completed
                            TYPE json_value compiled
                            TYPE json_value_array compiled
                            TYPE json_list compiled
                            TYPE json compiled
                            PACKAGE json_parser compiled
                            PACKAGE BODY JSON_PARSER compiled
                            PACKAGE json_printer compiled
                            PACKAGE BODY JSON_PRINTER compiled
                            TYPE BODY json_value compiled
                            PACKAGE json_ext compiled
                            PACKAGE BODY json_ext compiled
                            TYPE BODY json compiled
                            TYPE BODY json_list compiled
                            PACKAGE json_ac compiled
                            PACKAGE BODY json_ac compiled
                            ------------------------------------------
                            -- Adding optional packages for PL/JSON --
                            ------------------------------------------

                            Error starting at line 52 in command:
                            @@addons/json_dyn.sql --dynamic sql execute
                            Error report:
                            Unable to open file: "addons/json_dyn.sql"

                            Error starting at line 53 in command:
                            @@addons/jsonml.sql --jsonml (xml to json)
                            Error report:
                            Unable to open file: "addons/jsonml.sql"

                            Error starting at line 54 in command:
                            @@addons/json_xml.sql --json to xml copied from http://www.json.org/java/org/json/XML.java
                            Error report:
                            Unable to open file: "addons/json_xml.sql"

                            Error starting at line 55 in command:
                            @@addons/json_util_pkg.sql --dynamic sql from http://ora-00001.blogspot.com/2010/02/ref-cursor-to-json.html
                            Error report:
                            Unable to open file: "addons/json_util_pkg.sql"

                            Error starting at line 56 in command:
                            @@addons/json_helper.sql --Set operations on JSON and JSON_LIST
                            Error report:
                            Unable to open file: "addons/json_helper.sql"


                            I am confused as I noticed that the missing files are in the sub folder of addons, why PL_JSON cannot be installed successfully?
                            • 11. Re: How to pass a cursor's result to javascript code in APEX?
                              lxiscas
                              Hi, guys:

                              I am back to the issue of using PL_JSON to pass cursor data to javascript. I have installed PL_JSON on back database schema successfully, and I have tested hell world with PL_JSON. My questions is:

                              Do I need to install any JSON parser in APEX so when my javascript code in APEX needs to parse JSON object, it understands JSON data format?
                              How to call PL_JSON in java script to parse the JSON data?

                              Thanks.

                              Sam
                              • 12. Re: How to pass a cursor's result to javascript code in APEX?
                                VC
                                Hi Sam,

                                What version of apex you are using?

                                If you are on 4.x you don't have to integrate anything you can simply use this http://api.jquery.com/jQuery.parseJSON/
                                • 13. Re: How to pass a cursor's result to javascript code in APEX?
                                  lxiscas
                                  Hi, VC:

                                  Thanks for your help! I have tried the example sucessful. However, I also need to get cursor result from a query and have javascript to acess it. I probably can generate JSON object with "sql_to_json" with PL_JSON on the server, but how can javascript in APEX knows where to get JSON object? Or I need to use htmldb_get?

                                  Thanks again.

                                  Sam
                                  • 14. Re: How to pass a cursor's result to javascript code in APEX?
                                    lxiscas
                                    Hi, VC:

                                    I use PL_JSON generated a JSON object as follows:
                                    {"Offenders": [{
                                      "ADDRESS1" : "418A South 2nd St.",
                                      "CITY" : "Hartshorne"
                                    }, {
                                      "ADDRESS1" : "6112 N. Meridian Ave. ",
                                      "CITY" : "Oklahoma City"
                                    }]}
                                    And I passed it to a hiddenitem in an APEX page in a process on load before header:
                                    declare
                                    
                                      query_result json_list;
                                      list_string varchar2(10000);
                                      
                                    begin
                                    
                                        query_result:=json_util_pkg.sql_to_json('select sl.address1, sl.city from sor_location sl where sl.offender_id=41012 ');
                                        list_string:='{"Offenders": '||json_printer.pretty_print_list(query_result)||'}';
                                        dbms_output.put_line(list_string);
                                        :P3006_H_JSON:=list_string;
                                        :P3006_TF_JSON:=:P3006_H_JSON;
                                    end;
                                    and I noticed in :P3006_TF_JSON the value of JSON object is correct.

                                    However, when I try to test alert in javascript, it did not work:
                                    <script type="text/javascript"       src="http://maps.googleapis.com/maps/api/js?sensor=false">     </script>     
                                    <script type="text/javascript">       
                                    function initialize() {  
                                    
                                    
                                    var json_obj = "&P3006_H_JSON";
                                    var obj = jQuery.parseJSON(json_obj);
                                    
                                    //alert( obj.name === "Sam" );
                                    alert (obj.Offenders[0].CITY==="Hartshorne");
                                    }     
                                    </script> 
                                    But if I assign value to json_obj directly, it works well:
                                    <script type="text/javascript"       src="http://maps.googleapis.com/maps/api/js?sensor=false">     </script>     
                                    <script type="text/javascript">       
                                    function initialize() {  
                                    
                                    
                                    var json_obj = '{"Offenders": [{  "ADDRESS1" : "418A South 2nd St.",  "CITY" : "Hartshorne"}, {  "ADDRESS1" : "6112 N. Meridian Ave. ",  "CITY" : "Oklahoma City"}]}';
                                    var obj = jQuery.parseJSON(json_obj);
                                    
                                    //alert( obj.name === "Sam" );
                                    alert (obj.Offenders[0].CITY==="Hartshorne");
                                    }     
                                    </script> 
                                    I think the value of hidden item was not passed to javascript variable, could any one help me on this?
                                    1 2 Previous Next