Monday, 14 April 2014

APEX_MAIL.SEND() and email logs

Fairly quick post on how to check that emails are actually being sent from your application. 

We had a problem on test database that our PL/SQL code was using APEX_MAIL.SEND procedure but for some reason no emails were actually arriving in the user in-boxes. 

Question that I got from my DBAs was can you prove that your code really raises and sends emails out. 

Our problems at the end were some internal network configurations but things that help me prove my point were: 

select * from APEX_MAIL_QUEUE
 
select * from APEX_MAIL_LOG

--as sys
select *
from ALL_SCHEDULER_JOB_RUN_DETAILS
where job_name = 'ORACLE_APEX_MAIL_QUEUE'
order by log_date desc;
Just to be clear first make sure that after you call a procedure that you call  APEX_MAIL.PUSH_QUEUE; 

And there should be no problems from APEX side.


Cheers,
SL

How to delete error handling feedback message?

I am using a modified version of apex_error_handling_message function to log and display errors that are raised within my APEX application. Where instead of having a separate error message table error messages are logged as FEEDBACK messages using standard apex_util.submit_feedback() procedure.

On how to do this there are quite a few examples out there so I will not go into more details here.

Problem that I had and that this post is about is - OK now that application is logging all errors and they are easily visible under Team Development -> Feedback but how can I manage and monitor these from my application. 

I have found an answer goggling Oracle forums but for some reason it wasn't as easy to find on one occasion when it was pretty urgent.  So decided to post it here so that more blogs we have easier it will be to find. :)

Report query: 

SELECT feedback_number "No.",
       feedback,
       CASE
          WHEN feedback_type = 1 THEN 'General Comment'
          WHEN feedback_type = 2 THEN 'Enhancement Requested'
          WHEN feedback_type = 3 THEN 'Bug/Error'
       END
          "Type",
       CASE
          WHEN feedback_status = 0 THEN 'No Status'
          WHEN feedback_status = 1 THEN 'Acknowledged'
          WHEN feedback_status = 2 THEN 'Additional Info. Requested'
          WHEN feedback_status = 3 THEN 'Open'
          WHEN feedback_status = 4 THEN 'closed'
       END
          "Status",
       deployment_system,
       application_id,
       application_name,
       application_version,
       page_id,
       page_name,
       developer_comment,
       public_response,
       created_by,
       created_on,
       updated_by,
       updated_on
FROM   apex_team_feedback
where application_id = :APP_ID
ORDER BY updated_on DESC


 Once you get to this point it will be quite easy to create a page and write a PL/SQL that will delete any of these particular messages.

Hope it helps.

Other APEX posts on this subject:  

Sunday, 13 April 2014

Dynamic breadcrumbs in APEX


Recently I had to implement a sort of dynamic breadcrumbs for one of our client. As usual there were two master tables while there was only one detail page.

The idea that I came up with is:


1. Add this into your detail page header:

<script>
$(function() {
// Document is ready
var nLink = '<a href="f?p=118:&A2_PARENT_PAGE.:&SESSION.::NO:::">&A2_PARENT_PAGE_NAME.</a>';
//ADD breadcrumb text
$( "div.uBreadcrumbs ul li a").html(nLink);

});
</script>



2. Create two application level parameters: PAR_PAGE_NAME & PAR_PAGE_ID
3. Master pages need to submit these two values


This should do the trick.

APEX form tabs

(APEX 4.2.x)

I have used APEX since 3.2 release and always had a need to implement pages that include tab sub-regions. Since it is quite common user request that is why I posted this to possible offer you one more way of how you can create tabs on your APEX pages. 

There are different solutions out there such as: tab-pluggin or jquery-tabs and few others.

Both solutions are equally good but I wanted something even more simple so this is what I have used.

Cookbook:

1. Download these two scripts:
JavaSript code: 



function stringToPrepend(tabID, class_title){
  //return prepand String based on tabID provided
  // tabID must match corresponding DIVs
 var htmlText = "<ul>";   

  //LOOP through all tab divs where id like SubTabs
 //and create <UL> element
 $("div[id^="+tabID+"]").each(function(i) {
   var position = i+1;
   var tabTitle = $('div[class$=' + class_title + ']:first', this).text(); // template class used here as Title if subregion then use only parent title
 
   htmlText = htmlText
         + "<li><a href=#"
         + tabID
         + position
         + "><span id=span"
         + position
         + ">"
         + tabTitle
         + "</span></a></li>";
 }); //end loop
 htmlText = htmlText + "</ul>";
 return htmlText;
} //end stringToPrepend

function appendTabChild(tabID, base){  
 //LOOP through all tab divs where id like SubTabs
 //and append element to tab
 $("div[id^="+tabID+"]").each(function(i) {
   var position = i+1;
   var tabs = tabID + position;
   if(position > 1){
   $("div[id^="+tabs+"]").css('display', 'none');
   }
   base.appendChild( $x(tabs));  
 }); //end loop
} //end appendTabChild

// end JS

CSS code:

#base {
    padding: 0px;
    background: none;
    border-width: 0px;
}
#base .ui-tabs-nav {
    padding-left: 0px;
    margin-bottom: 2px;
    background: transparent;
    border-width: 0px 0px 0px 0px;
    -moz-border-radius: 0px;
    -webkit-border-radius: 0px;
    border-radius: 0px;
}
#base .ui-tabs-panel {
    background: #f5f3e5 url(http://code.jquery.com/ui/1.8.23/themes/south-street/images/ui-bg_highlight-hard_100_f5f3e5_1x100.png) repeat-x scroll 50% top;
    border-width: 0px 1px 1px 1px;
}
#base .ui-tabs-nav li.ui-tabs-active {
      background: #CCCCCC !important;
}

.ui-state-active a, .ui-state-active a:link, .ui-state-active a:visited {
    color: #296EB3;   
}

.nr-title, .rc-title{
font-size:10px;
}
 

2. Save them as .js and .css files 

3. Upload them into your APEX environment

4. Add this to your APEX page header



<link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>

<script src="#WORKSPACE_IMAGES#Form_tab.js"></script>
<link rel="stylesheet" href="#WORKSPACE_IMAGES#Form_tab.css">
<script type="text/javascript">

$(function() {

/**************** KEY PARAMETERS

1. create 3 html regions
  - first to be id=base or change the variables in javascript with no template
  - other two id = SubTabs1, 2  or change the variables in javascript 

2. paste this code into page header or include it as java script

WARNING: '-title' class selector used and this might cause Tab title
to be empty if Application template doesn't contain this
**************************************/

//html region ID=base that holds all tabs
var container = $("#base");
var base = $x("base");
var tabs_id = 'SubTabs';
var v_class = '-title'; //// template class used here as Title

//populate container div with extended html
container.prepend(stringToPrepend(tabs_id, v_class));
//append tabs
container.tabs();
appendTabChild(tabs_id, base);
});
// end JQuery function

</script>

5. Create one HTML region as



6. Create the rest of the regions with IDs: SubTabsx  i.e 1,2,3,...... (or change JavaScript)

 

And that is it.

To summarize: Container region is where HTML will be generated.

I have tested this in 4.2.3 and 4.2.5 Theme 22, but I am sure it should work in other themes too. 

The only thing to note is current scripts do not work if parent script does not contain a title. Sub-regions and nested regions should be supported plus nested regions can be without a title.

For any other problem I am sure you should be able to adjust these scripts to your needs. 

CSS file is where you might want to change the appearance of your tabs.  
More details on jquery tabs css: jQuery Tabs


Hope this helps.

SL 

........

I have been playing with theme25. The only changes are 
in header:

var v_class = 'Heading'; //// template class used here as Title

in javascript code: 


function stringToPrepend(tabID, class_title){
  //return prepand String based on tabID provided
  // tabID must match corresponding DIVs
 var htmlText = "<ul>"; 


  //LOOP through all tab divs where id like SubTabs
 //and create <UL> element
 $("section[id^="+tabID+"]").each(function(i) {
   var position = i+1;
   //alert($("div[class$='-title']:first", this).text());
   var tabTitle = $('div[class$=' + class_title + ']:first', this).text(); // template class used here as Title if subregion then use only parent title
   //alert('Here: ' + position /*+ $(this).text()*/ + tabTitle );

   htmlText = htmlText
         + "<li><a href=#"
         + tabID
         + position
         + "><span id=span"
         + position
         + ">"
         + tabTitle
         + "</span></a></li>";
 }); //end loop

 htmlText = htmlText + "</ul>";

 return htmlText;
} //end stringToPrepend


function appendTabChild(tabID, base){  
 //LOOP through all tab divs where id like SubTabs
 //and append element to tab
 $("section[id^="+tabID+"]").each(function(i) {
   var position = i+1;
   var tabs = tabID + position;
   if(position > 1){
   $("section[id^="+tabs+"]").css('display', 'none');
   }
   base.appendChild( $x(tabs));  
 }); //end loop
} //end appendTabChild