I have my very own widget, do you?

We all have that thing we’ve dreamed of forever. That little something that we know would make us ever so happy, but that seems slightly out of reach. If only we knew how to get it, life would be simply… perfect.

For me, that thing was a widget. Yes, you read me right. I wanted a widget.

Goodreads has a great one where I can share my reading with friends. Amazon lets me share my wish list. Twitter lets me share my feed.

Well, my partner and I run a pretty cool website called Reading-Rewards.com. It’s developed with Oracle Application Express, and kids, parents and teachers around the world use it to log and track reading.

Because teachers that use it are generally pretty tech-savvy, I have always wanted a way for them to be able display and show off their students’ reading progress ‘live’ on their own blogs or websites.

So, before I go on, I should probably tell you that you have a little drool on your chin. You just KNOW you want one too. Well I’ll tell you what you need to do, because, my friends, sharing is caring.

1. 1. You need a procedure in your database that generates and prints the HTML for your widget.

      create or replace YOUR_PACKAGE as
      
         procedure yourProcedure(p_token in varchar2);
      
      end;
      
      /
      
      create or replace PACKAGE BODY YOUR_PACKAGE AS
      
        procedure yourProcedure(p_token in varchar2) AS
          l_css varchar2(32000);
          l_html varchar2(32000);
          l_value1 varchar2(1000);
          l_value2 varchar2(1000);
      
      
        BEGIN
          l_css := q'!
      <style>
        *** all the styles you want to include in your widget... make it pretty!!! ****
      </style>
          !';
          l_html := q'!
          /* all the html that your widget will render, including substitutions from your database
          */
      
          <div>#VALUE1#</div>  -- this will be substituted with value from database
          <div>#VALUE2#</div>  -- this will be substituted with value from database
          
          !';
      
          --- here are whatever bits of sql to grab data we want from the database....
      
            select sum(column1)
              into l_value1
              from mytable
            where id=p_token;
          
            select sum(column2)
              into l_value2
              from mytable2
              where id=p_token;
          
          -- now let's replace our substitutions with our calculations from db...
        
          
            l_html := replace(l_html, '#VALUE1#', nvl(l_value1,0));
            l_html := replace(l_html, '#VALUE2#', nvl(l_value2,0));
      
            htp.prn(l_css);
            htp.prn(l_html);
      
      
        EXCEPTION WHEN OTHERS THEN
          htp.prn(l_css);
          htp.prn('print some error message');
        END yourProcedure;

       

   2. You need a RESTFul Web Service that basically calls the procedure we created in step 1.
   3. You need a bit of jQuery code sitting on your server somewhere. Most of the code (and I won’t pretend to understand it all) is simply about loading the jQuery library in such a manner as to not interfere with anything else running on the destination site. The main function is just the last few lines, with the URL being the URL for the Restful Web Service created in step 2. You then need an  ID (in our case #rrwiginfo) to be the eventual destination for the html rendered by your procedure.

      (function() {
      
      // Localize jQuery variable
      var jQuery;
      
      /******** Load jQuery if not present *********/
      if (window.jQuery === undefined || window.jQuery.fn.jquery !== '3.3.1') {
          var script_tag = document.createElement('script');
          script_tag.setAttribute("type","text/javascript");
          script_tag.setAttribute("src",
              "//ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js");
          if (script_tag.readyState) {
            script_tag.onreadystatechange = function () { // For old versions of IE
                if (this.readyState == 'complete' || this.readyState == 'loaded') {
                    scriptLoadHandler();
                }
            };
          } else { // Other browsers
            script_tag.onload = scriptLoadHandler;
          }
          // Try to find the head, otherwise default to the documentElement
          (document.getElementsByTagName("head")[0] || document.documentElement).appendChild(script_tag);
      } else {
          // The jQuery version on the window is the one we want to use
          jQuery = window.jQuery;
          main();
      }
      
      /******** Called once jQuery has loaded ******/
      function scriptLoadHandler() {
          // Restore $ and window.jQuery to their previous values and store the
          // new jQuery in our local jQuery variable
          jQuery = window.jQuery.noConflict(true);
          // Call our main function
          main(); 
      }
      
      /******** Our main function ********/
      function main() { 
          jQuery(document).ready(function($) { 
              // We can use jQuery 3.3.1 here
      		var vData = JSON.parse('{"P_TOKEN": "'+rrtoken+'"}');
      		$.ajax({
      			type: "post",
      			url: "https://yourserver/ords/yourschema/yourrestful",
      			data: JSON.stringify(vData),
      			contentType: "application/json; charset=utf-8",
      			crossDomain: true,
      			success: function (data, status, jqXHR){
      				$('#rrwginfo').html(data);
      			},
      			error: function (jqXHR, status){
                  }
      		});
          });
      }
      
      })(); // We call our anonymous function immediately
      
      

   4. Create an APEX page for your users to grab their widget code!

      <script type="text/javascript">var rrtoken = "#TOKEN#";</script>
      <script type="text/javascript" src="**link to your jQuery code snippet from step 3**">
      </script>
      <div id="rrwginfo"></div>

      Make sure that the script that is returned has properly substituted #TOKEN# with however you wish to uniquely identify your user.

The sky’s the limit, here! Our APEX page allows our teachers to choose what they wish to display: All Time reading, This month’s reading, or Today’s reading. We’ve only just gotten started and look forward to making all sorts of improvements, but we’re pretty excited that we can now get our teachers to show off their students’ reading prowess on their own ‘properties’. Yay!