Interactive Report - Function Returning SQL Query

Current version of APEX does not have the option for function returning SQL query when we use interactive report as report type
.
Below is the work around for that.

1. Create a function in the database.

Ex : 

CREATE OR REPLACE FUNCTION IR_COLLECTION_FUN RETURN CLOB AS
V_QUERY CLOB;
BEGIN
V_QUERY := 'SELECT * FROM EMP';
RETURN (V_QUERY);
END;

2. Create a page and add the below process to your page
Process Point : On load before header

3. Create a report that querying from the collection

select * from apex_collection where collection_name = 'IR';

4. As we are using collection, header names will be cxx...
Hide all the unnecessary columns.
For header names create hidden items.

5. Create a process to populate the header names as below

select
'empno',
'ename',
'job',
'mgr',
'hiredate',
'sal',
'comm',
'deptno'
into
:P3_COL1,
:P3_COL2,
:P3_COL3,
:P3_COL4,
:P3_COL5,
:P3_COL6,
:P3_COL7,
:P3_COL8
from dual;

6. In column attributes heading add the hidden item names as below

7. Final report as below

Working example : http://apex.oracle.com/pls/apex/f?p=43578:3

Classic report - Fixed headers and columns

 When we have reports with number of columns and rows, then viewing that report is difficult to the users.

There, we can use fixed headers and columns to view the report easily with horizontal and vertical scroll bars.

Followed Article : http://dbswh.webhop.net/htmldb/f?p=BLOG:READ:0::::ARTICLE:59900346164562

Thanks to Jari for his great article.

1. Create a report with static ID (Ex:EMP)

2. Add the below function to your page attributes - Function and global value declaration

;(function($){$.fn.htmldbDscroll=function(opt){
 opt=$.extend({
  width:400,
  height:200,
  freezeColumns:1,
  freezeRows:1,
  addCellWidth:18,
  addRowHeight:2,
  addTblWidth:12,
  scrollWidth:18
 },opt);

 if(opt.freezeColumns<1&&opt.freezeRows<1){
  return this;
 }

 return this.each(function(i){

  var lId=this.id.substr(6)
  /* styles */
  $("#lh"+lId).css({"right":"0","bottom":"0"}).parent().css({"right":"0","bottom":"0"});
  $("#rh"+lId).css({"overflow-x":"hidden","width":opt.width-opt.scrollWidth,"left":"0","bottom":"0"}).parent().css({"bottom":"0"});
  $("#lb"+lId).css({"overflow-y":"hidden","height":opt.height-opt.scrollWidth,"right":"0","top":"0"}).parent().css({"right":"0","top":"0","vertical-align":"top"});
  $("#rb"+lId).css({"overflow":"scroll","width":opt.width,"height":opt.height});

  /* report cell width */
  if(opt.freezeRows>0){
   $("#rb"+lId).find("tr:first,tr:eq("+opt.freezeRows+")").children().each(function(){   
    var w=$(this).width()+opt.addCellWidth;
    $(this).width(w);
   });
  }else{
   $("#rb"+lId).find("tr:first").children().each(function(){
    var w=$(this).width()+opt.addCellWidth;
    $(this).width(w);
   });
  }

  /* row process */
  if(opt.freezeColumns>0){
   $("#rb"+lId).find("tr").each(function(j){
    var t1=$(this);
    /* set row height and copy row */
    var h=t1.height()+opt.addRowHeight;
    t1.height(h);
    var t2=t1.clone().empty().append(t1.children(":lt("+opt.freezeColumns+")"));
    if(j>(opt.freezeRows-1)){
     $("#lb"+lId).children().append(t2);
    }else{
     $("#rh"+lId).children().append(t1);
     $("#lh"+lId).children().append(t2);
    }
   });
  }else{
   for(var j=0;j<=(opt.freezeRows-1);j++){
    $("#rh"+lId).children().append($("#rb"+lId).find("tr:eq("+j+")"));
   }
  }

  /* table width */
  if(opt.freezeColumns>0){
   if($("#lh"+lId).children().width()>$("#lb"+lId).children().width()){
    var w=$("#lh"+lId).children().width();
    $("#lb"+lId).children().width(w);
    $("#lh"+lId).children().width(w);
   }else{
    var w=$("#lb"+lId).children().width();
    $("#lh"+lId).children().width(w);
    $("#lb"+lId).children().width(w);
   }
  }
  if($("#rh"+lId).children().width()>$("#rb"+lId).children().width()){
   var h=$("#rh"+lId).children().width()+opt.addTblWidth;
   $("#rb"+lId).children().width(h);
   $("#rh"+lId).children().width(h);
  }else{
   var h=$("#lb"+lId).children().width()+opt.addTblWidth;
   $("#rh"+lId).children().width(h);
   $("#rb"+lId).children().width(h);
  }

  $("#rh"+lId).children().css({"table-layout":"fixed"});
  $("#rb"+lId).children().css({"table-layout":"fixed"});

  /* scroll */
  $("#rb"+lId).scroll(function(){
   $("#rh"+lId).scrollLeft(this.scrollLeft);
   $("#lb"+lId).scrollTop(this.scrollTop);
  });
 });

}})(jQuery); 

3. Create a dynamic action as below

After refresh

execute below java script code

$("#report_EMP").htmldbDscroll({freezeColumns:2});

4. Apply the report template as below

Before Rows

<style>#report_#REGION_STATIC_ID# table *{white-space:nowrap!important}</style>
<table cellpadding="0" border="0" cellspacing="0" summary="" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">#TOP_PAGINATION#
<tr><td><div id="lh_#REGION_STATIC_ID#">

Column heading template

<th#ALIGNMENT# id="#COLUMN_HEADER_NAME#" class="header">#COLUMN_HEADER#</th>

Before each row

<tr #HIGHLIGHT_ROW#>

Column template1

<td#ALIGNMENT# headers="#COLUMN_HEADER_NAME#" class="data">#COLUMN_VALUE#</td>

After each row

</tr>

After rows

</table></div><div class="CVS">#EXTERNAL_LINK##CSV_LINK#</div></td></tr>
#PAGINATION#
</table>

Next page template

<a href="#LINK#" class="pagination">#PAGINATION_NEXT#<img src="#IMAGE_PREFIX#themes/theme_1/paginate_next.gif" alt="Next"></a>

Previous page template

<a href="#LINK#" class="pagination"><img src="#IMAGE_PREFIX#themes/theme_1/paginate_prev.gif" alt="Previous">#PAGINATION_PREVIOUS#</a>

Next set template 

<a href="#LINK#" class="pagination">#PAGINATION_NEXT_SET#<img src="#IMAGE_PREFIX#themes/theme_1/paginate_next.gif" alt="Next"></a>

Previous template

<a href="#LINK#" class="pagination"><img src="#IMAGE_PREFIX#themes/theme_1/paginate_prev.gif" alt="Previous">#PAGINATION_PREVIOUS_SET#</a> 

Working example : http://apex.oracle.com/pls/apex/f?p=43578:2

When clicking on report link populate confirm dialogue box and then process the record

When clicking on report link populate confirm dialogue box and then process the record : 

Create a report with delete option : 

Create one text item to hold the selected empno when click on report link (delete) ex : P1_DELETE_EMP

Go to report attributes > 'Delete' column attributes >

Add below java script in URL field : 

javascript:apex.confirm('Are you sure you want to delete this record', {request: 
'DELETE',SET : {'P1_DELETE_EMP': #EMPNO#}})

Write a PL/SQL process to delete the selected record : 

Add condition to the process : 

Request = Expression1 

Expressoin : 'DELETE'

 That's it, when you click on delete report link you can see the confirm dialogue box and clicking on 'OK', Record will be deleted.

Scheduling Reports

Scheduling a report(s) from the database using APEX utility : 

Till now using subscription, we can schedule the interactive report only.
Most cases we may get requirement to schedule the multiple reports with custom
definitions.

Here is the example to schedule a report from database using APEX utility.

 1. Create a stored procedure as below.

create or replace procedure Employee
as
l_body_html CLOB;
l_body CLOB;
BEGIN
l_body:= 'Employee Report';
l_body_html := l_body_html || '<font color="green"><center><h3>Employee
Report</h3><br><h3>Employee Report</h3></center></font><table border="1"
width="100%" ><tr bgcolor="#CEE3F6"><td align="center">Empno</td><td
align="center">Employee Name</td><td align="center">Job</td><td
align="center">Salary</td></tr>';
for c1 in(Select empno,ename,job from emp)
loop
l_body_html :=l_body_html || '<tr><td>' || c1.empno || '</td>' || '<td>' || c1.ename
|| '</td>' || '<td>' || c1.Job || '</td></tr>';
end loop;
l_body_html :=l_body_html || '</table>';
apex_mail.send(
p_to => 'to@test.com
',
p_from => 'from@test.com
',
p_body => l_body,
p_body_html => l_body_html,
p_subj => 'Employee Report',
p_bcc => 'bcc users@test.com
');
END Employee;

 2. Create a job to schedule the report. 

Schduler : 
BEGIN
 dbms_scheduler.create_schedule(  
schedule_name  => 'Employee_Schedule',  
  start_date         => trunc(sysdate),  
  repeat_interval => 'freq=Daily;Day='Mon',  
  comments        => 'Runtime: Every Monday ');
END;

Program : 
begin  
-- Call a procedure  
dbms_scheduler.create_program  
(program_name=> 'Employee_Program',  
 program_type=> 'STORED_PROCEDURE',  
 program_action=> ?Employee? ,  
 enabled=>true,  
 comments=>'Procedure to collect session information'  
 );  
end;


Job :
begin  
-- Connect both dbms_scheduler parts by creating the final job  
dbms_scheduler.create_job  
 (job_name => 'Employee_Job',  
  program_name=> 'Employee_Program',  
  schedule_name=>'Employee_Schedule',  
  enabled=>true,  
  auto_drop=>false,  
  comments=>'Job to collect data about session values every 5 minutes');  
end;

 Below is the screenshot for report : 

ORA-24247: network access denied by access control list (ACL) tips

Assume that you have successfully installed or upgrade your APEX, but when you tried to send an email, it's not working and you are able to see the above error in APEX_MAIL_QUEUE log.

Reason : The database user don't have a necessary permissions.

Below are some steps you need follow/check :

1. Run the below procedure as SYS user where APEX installed.

   
DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Above procedure will check the ACL's list and it will give the necessary permissions to the database user.

NOTE : Need to change the database user according to your APEX version.

2. Assign ACL to your SMTP host address

begin
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
  ACL  => 'power_users.xml',
  host => 'localhost'
  );
  commit;
end;

Introduction

Oracle Application Express (Oracle APEX), formerly called HTML DB, is a fully supported "no-cost" option of the Oracle Database. Oracle Application Express is certified against all editions of the Oracle Database 10.2.0.3 and above, including Oracle Database 10g Express Edition (Oracle XE).
Oracle Application Express installs as part of the seed database installation with Oracle Database 11g. The latest version of Oracle Application Express can be downloaded for free from the Oracle Technology Network (OTN) [http://otn.oracle.com/apex]