This discussion is archived
5 Replies Latest reply: Apr 19, 2012 7:00 AM by pj*433620*ng RSS

Refresh PL/SQL anonymous HTML region

pj*433620*ng Newbie
Currently Being Moderated
Hi, All
Is there anyone found out the easier way or plugin to refresh a PLSQL anonymon HTML region,
for example, I display time in one of the HTML region with
BEGIN
HTP.P(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
END;

how can I refresh the region to make it time running...

Thanks

Vincent Pek
  • 1. Re: Refresh PL/SQL anonymous HTML region
    pj*433620*ng Newbie
    Currently Being Moderated
    Anyone got the answer?
  • 2. Re: Refresh PL/SQL anonymous HTML region
    WannaRock Explorer
    Currently Being Moderated
    I have 'AN' answer but it won't be using pl/sql. PL/SQL will require you to query the database for the time. Which will then mean you have to refresh the page every second or use ajax to hit the database every second. Plus, the db server time will show and not the user's (if they are in different time zones).

    With Javascript you can accomplish what you need all on the client side using the client's clock time.

    So you can put this in an external js file or you can EDIT Page:
    - in Function and Global Variable declaration, paste this:
    function runClock() {
    var currentTime = new Date ( );

    var rawHr = currentTime.getHours ( );
    var rawMin = currentTime.getMinutes ( );
    var rawSec = currentTime.getSeconds ( );

    // min and secs must be padded with leading zeros
    var sMin = ( rawMin < 10 ? "0" : "" ) + rawMin;
    var sSec = ( rawSec < 10 ? "0" : "" ) + rawSec;

    /*** this is only if you want the 12 hour time format instead of 24 hour clock ***/
    // is it AM or PM? Easy using the 24 hour result
    var sAMPM = ( rawHr < 12 ) ? "AM" : "PM";
    // converts 24 hour clock to 12 hour (i.e. 1 PM instead of 1300 hours)
    var sHr = ( rawHr > 12 ) ? rawHr - 12 : rawHr;
    // In the event we're seeing this at midnight (zero hour in 24 hour format)
    sHr = ( rawHr == 0 ) ? 12 : sHr;
    /*********** | end of 24 hour to 12 hour time conversion | ***************/
    // concatenate final time string in HH:MM:SS AMPM format
    var sTime = sHr + ":" + sMin + ":" + sSec + " " + sAMPM;

    // Update the time display in a div with an id of clock
    $('#clock').html(sTime);
    // refresh every second
    setTimeout("runClock()",1000);
    }

    - In your Execute when Page Loads call teh function:
    runClock();

    - finally, wherever on your page you want the clock, create an html region with:
    &lt;div id="clock"&gt;&lt;/div&gt;

    - or you can put that div as the region header/footer to your pl/sql region although, I don't generally recommend it.
  • 3. Re: Refresh PL/SQL anonymous HTML region
    pj*433620*ng Newbie
    Currently Being Moderated
    Hi, Wannarock
    The example I provided is only a sample to demonstrate my requirement,
    the HTML region can be any pl/sql which I need to refresh it.

    Thanks for your reply.

    Vincent
  • 4. Re: Refresh PL/SQL anonymous HTML region
    Tom Petrus Expert
    Currently Being Moderated
    If you really want to keep in line with partial refreshes/ajax then you have only one way i believe, which is using a page process. If you don't mind reloading the page, then a submit/branch will suit you just fine.
    Anyway, this is how i solved it where submits were not useful:

    1. Create a plsql region and write your code which outputs the html. Debug it. Make sure your output is grouped somewhat together in containers (div for example) and give them an ID (necessary for DOM manipulation)
    2. Create an html region, and put a blueprint there of your containers.
    3. Create an ajax callback process, and copy your code from the plsql region over to it. Calling this process will then return you the html.
    4. You'll need javascript code which calls the process and then handles the replacing of your current content with the fetched content, and also bind some events which cause the update in the first place.

    To help you along, here is some tangible help instead of an abstract list :)

    What i currently have is a div with a list of photographs/contacts. Essentially, it is a report icon view and has pagination, and i wasn't able to use the standard reports.
    My HTML region code:
    <div id="photoview_container">
       <span id="photoview_set"></span>
       <div id="photoview">
          <div id="contact_0000" class="contact_container">
             <div class="img_container"></div>
             <div class="data_container"></div>
          </div>
       </div>
       <div id="photoview_pagination">
          <a id="photoview_prev_set" href="#"></a>
          <a id="photoview_next_set" href="#"></a>
       </div>
    </div>
    <div id="dropcontent"></div>
    This is essantially my blueprint html with no content in it yet. Note that i have 2 containers: "photoview_container" and "dropcontent"

    My page process (ajax callback):
    Actually builds up my html content. I'm not posting it cause it is a lot of code an irrelevant. But it uses the same html blueprint as my region, save for the dropcontent container. It builds up my photoview_container and fills it up with several divs with data retrieved from the db.

    My javascript:
    Take a look at the code, should be quite straightforward. I actually got the rename/fetch in drop/replace idea from the apex javascript, this is the same thing that happens with report refreshing actually.
    function get_photoview_set(sPrevNext, sSearchValue, sCurrentSet){
       //get_photoview_set outputs the html code containing all the photos
       var get = new htmldb_Get(null, $v("pFlowId"), "APPLICATION_PROCESS=get_photoview_set", $v("pFlowStepId"));
       //add params for submit (x## are temp vars)
       get.addParam("x01", sPrevNext);
       get.addParam("x02", (!!sCurrentSet) ?sCurrentSet : $("#photoview_set").text());
       //add page items for submit (p_args, p_values arrays)
       get.add("P2_SEARCH_PHOTOVIEW", (!!sSearchValue) ?sSearchValue : $v("P2_SEARCH_PHOTOVIEW"));
       get.add("P2_ROWS_PHOTOVIEW", $v("P2_ROWS_PHOTOVIEW"));
       get.add("P2_DEPARTMENTS_PHOTOVIEW", $v("P2_DEPARTMENTS_PHOTOVIEW"));
       get.add("P2_LOCATIONS_PHOTOVIEW", $v("P2_LOCATIONS_PHOTOVIEW"));
       //get results
       var result = get.get();
       //rename the current content container div id
       $("#photoview_container").attr("id", "photoview_container_old");
       //put the output html into a drop container
       $("#dropcontent").html(result);
       //replace the old content container with the new html
       $("#photoview_container_old").replaceWith($("#photoview_container"));
       //clean up: empty the drop container
       $("#dropcontent").empty();
       
        //when a contact is clicked on: popup info
       $(".contact_container").click(function(oEvent){
          var cid = $(this).attr("id");
          cid = cid.substring(cid.indexOf('_')+1);
          popupinfobox(oEvent, cid);
       });
    
       //when apexrefresh is triggered on the region then refetch info
       $("#photoview_container").bind("apexrefresh", function(){
          get_photoview_set('RESET');
       });
    
       //next set has to be fetched
       $("#photoview_next_set").click(function(oEvent){
          oEvent.preventDefault ? oEvent.preventDefault() : oEvent.returnValue = false;
          get_photoview_set('NEXT');      
       });
    
       //previous set has to be fetched
       $("#photoview_prev_set").click(function(oEvent){
          oEvent.preventDefault ? oEvent.preventDefault() : oEvent.returnValue = false;
          get_photoview_set('PREV');
       });
    };
    What is left to me then is:
    removing my plsql region: i don't need this anymore and i don't want it anymore. Having the region + my page process is double the maintenance. I just call my fetching process on page load and i'll have the same results.
    dynamic actions: if i want to dynamically refresh i can bind some dynamic actions to elements which will then do that.

    It's actually not all that much work, the only time you'll end in pain is having to debug your app process if something goes wrong there. Something i then usally solve by emitting debug code through the fetched html.
    Good luck.
  • 5. Re: Refresh PL/SQL anonymous HTML region
    pj*433620*ng Newbie
    Currently Being Moderated
    Thanks Tom for your time writting all these.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points