9 Replies Latest reply: Oct 24, 2008 11:41 AM by 483850 RSS

    json_from_sql

    220621
      I've found that into FLOWS_030100.wwv_flow_ajax package there are some json procedures, but I don't understand how they work, they're not documented.
      Anybody knows how to use these procedures?
        • 1. Re: json_from_sql
          Tyson Jouglet
          I just stumbled upon these as well and i too am just as baffled as how to use them. Has anyone experimented successfully with this?
          • 2. Re: json_from_sql
            Tyson Jouglet
            I have successfully used the json_from_sql procedure provided in the flows and here is how to use it...

            1. First create an ondemand application process. I named mine MAKE_JSON.
            2. Inside that process call the json_from_sql procedure like so:
            HTMLDB_UTIL.JSON_FROM_SQL('SELECT * FROM table_name');
            3. Lastly you need to make an ajax call to fire off the application process like so:
              <script type="text/javascript">
                  function getJSON()
                  {
                    var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=MAKE_JSON',0);
                    var gReturn = get.get();
                  }
              </script>
            4. Here is a sample of the JSON object that is returned from the procedure:
            (The structure will resemble the select statement.
            This particular table has 3 columns: PK_ID, NAME, LNAME)
              {"row":[{"PK_ID":"0001","NAME":"Scott","LNAME":"Tiger"},
                      {"PK_ID":"0002","NAME":"Tyson","LNAME":"Jouglet"}]}
            5. The code to access the information stored in the structure looks like this:
                $x('item_id').value = row[0].PK_ID; // 0001
                $x('item_id').value = row[0].NAME;  // Scott
                $x('item_id').value = row[0].LNAME; // Tiger
            null
            • 3. Re: json_from_sql
              orlandok
              Is it possible to run this procedure straight from sql/plsql. I want to produce some json data for my portal application which uses extjs. So I am not running an actual apex application, can I still call this procedure?
              • 4. Re: json_from_sql
                486393
                See here for a JSON-PL/SQL library: http://reseau.erasme.org/pl-sql-library-for-JSON
                • 5. Re: json_from_sql
                  357241
                  Hello,

                  Here are some APEX + JSON examples from my session at OOW [http://apex.oracle.com/pls/otn/f?p=38462:3] you can download the application and try it out yourself.

                  The PL/SQL packages and JS used to support this will be extended in APEX 4.0

                  Regards,
                  Carl

                  blog : [http://carlback.blogspot.com/]
                  apex examples : [http://apex.oracle.com/pls/otn/f?p=11933:5]
                  • 6. Re: json_from_sql
                    483850
                    Is there a problem when the query passed to the apex_util.json_from_sql() procedure returns no rows? I have some cases when the query returns no rows and I get the error: "sqlerrm:ORA-06502: PL/SQL: numeric or value error". Am I supposed to check to be sure the query returns data before running the procedure or is there another way to handle this error?
                    • 7. Re: json_from_sql
                      Tyson Jouglet
                      I do not know of any built in ways to validate the query. The only thing I can think of is to have an exception handler that returns a blank JSON object that looks something like:
                      {"row":[]}
                      This way it can be parsed and your JavaScript will not blow up.

                      Good Luck,

                      Tyson
                      • 8. Re: json_from_sql
                        357241
                        Hello,

                        Doh you are correct we need to deal with that better , I will put in as a bug right now.

                        One thing you can do is wrap the javascript code that parses the string into a JSON object in a try{}catch(){} call.

                        http://www.w3schools.com/js/js_try_catch.asp

                        Regards,
                        Carl

                        blog : [http://carlback.blogspot.com/]
                        apex examples : [http://apex.oracle.com/pls/otn/f?p=11933:5]
                        • 9. Re: json_from_sql
                          483850
                          Thanks!

                          I wasn't getting a PL/SQL error when running the procedure. It would just return the text "sqlerrm:ORA-06502: PL/SQL: numeric or value error" to my javascript variable. I'll have to work out how to handle the exception in javascript.

                          ~Jonathan