APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

First release of the PL-jrxml2pdf - Generate iReport PDFs with PL/SQL only

Von Tobias Arnhold → 6.29.2012

A while ago I wrote about and development idea creating iReport (Jasper Report) PDF-files directly inside APEX (PL/SQL).

Andreas Weiden the brain and developer behind this project finally released the first version: http://andreas.weiden.orcl.over-blog.de/

I helped him as a beta tester and I must say what he did is really amazing. Even in the beta state it worked really good. It supports a lot of functionality from iReport so I could use it even with complex reports. If you want to try it now then here is the download link: http://sourceforge.net/projects/pljrxml2pdf/

Btw.: The project itself is open source.

Tabular Form - Validation issue

Von Tobias Arnhold → 6.28.2012

I'm currently have some questions about TABULAR FORM validations. I my questions inside the APEX forum: https://forums.oracle.com/forums/thread.jspa?threadID=2407939
Questions:
1. I only want that a select list entry get selected ones not several times. It must be some kind of validation check. Is there any example available?
2. If a select list value is already set within a row then this id should not be displayed in any other row anymore.

For question one I found an answer myself. This is a validation based on the idea from Denes Kubicek.

-- Validation of Type: Function Returning Error Text 

DECLARE
   l_facility_ids varchar2(32000);
   l_facility_bez varchar2(200);
   l_error   VARCHAR2 (4000);
BEGIN
   FOR i IN 1 .. apex_application.g_f07.COUNT -- select list with facilities
   LOOP
      IF instr(':'||l_facility_ids||':',':'||apex_application.g_f07(i)||':')>0
      THEN 
         select description into l_facility_bez from facilities where id = apex_application.g_f07(i);
      
         l_error :=
               l_error
            || '</br>'
            || 'Row '
            || i
            || ': facility"' || l_facility_bez  ||'" is already selected.'; 
      END IF;

      l_facility_ids := l_facility_ids ||':'|| apex_application.g_f07(i);
   END LOOP;

   RETURN LTRIM (l_error, '</br>');
END;

Is there an workaround for question two available?

APEX examples

Easy table export to XLS (based on HTML)

Von Tobias Arnhold → 6.22.2012

There is a quite easy way to generate nice looking Excel files based on your APEX reports. It doesn't require any special printing engine and should work even with Excel 2003.

It supports all kind of report views as long as they are build as tables. In my case I created an export based on this pivot table output: http://gumption.org/2004/pivot_table/test_page.html
Of course my pivot table is build up as plug-in and selects data from my project data tables.

All you need to do is to get the table element (including all html code) from your rendered page and write it into a blank XLS-file.
Those are the steps you have to follow:

1. Create a new APEX item: textarea
Textarea - P1_EXPORT_DATA
HTML Form Element Attributes: style="display:none;"

2. Create a button
Button: P1_EXPORT
Action: Defined by Dynamic Action

3. Edit your table region
Static ID: YOUR_APEX_TABLE_ID
Info: Check your report template. Your table element should look similar to this:
<table border="0" cellpadding="0" cellspacing="0" summary="" id="report_#REGION_STATIC_ID#"

3. Create a Dynamic Action
Event: Click
Selection Type: Button
Button: P1_EXPORT
Actions: Execute Javascript Code & Submit Page
- Execute Javascript Code:
$('#P1_EXPORT_DATA').val($('#YOUR_APEX_TABLE_ID').wrap('<p/>').parent().html());
$('#YOUR_APEX_TABLE_ID').unwrap();
- Submit Page:
EXPORT

4. Create a new Branch
Branch to Page or URL - OnSubmit
Page: 1
Request: Export
Conditions - PL/SQL
:REQUEST = 'EXPORT'

5. Add a new process
Process: Export
Type: PL/SQL
Process Point: On Load - Before Header
Process:
DECLARE
     l_mime        VARCHAR2 (255);
     l_length      NUMBER;
     l_file_name   VARCHAR2 (2000);
     lob_loc       BLOB;
BEGIN
     dbms_lob.createtemporary(lob_loc, TRUE);
     DBMS_LOB.WRITE(lob_loc, LENGTH(:P1_EXPORT_DATA), 1, UTL_RAW.CAST_TO_RAW(:P1_EXPORT_DATA));
     OWA_UTIL.mime_header ('application/xls', FALSE);
     HTP.p ('Content-Length: ' || DBMS_LOB.GETLENGTH(lob_loc));
     HTP.p ('Content-Disposition: attachment; filename="my_xls_doc.xls"');
     OWA_UTIL.http_header_close;
     WPG_DOCLOAD.download_file(lob_loc);
END;

The output will look like this:

It also supports images but they must include the complete URL.

Btw.: APEX 4.2 EA is out. New Application Builder looks mostly nice but I think the region headers are far to big. Check it out yourself: https://apexea.oracle.com/i/index.html
New feature list: http://www.grassroots-oracle.com/2012/06/oracle-apex-42-early-adopter-announced.html

SQL Developer

Select table and column comments (Oracle SQL)

Von Tobias Arnhold → 6.07.2012

My default development client is the Oracle SQL Developer. If I compare it with TOAD it is slim, free to use and includes a table modeling area.
One thing I don't like is the ability to see table comments. To find this information you need to click on the table inside the table view ("Connections" > #MY_CON2# > Tables), then on tab: "Details", scroll to column "Comments" and finally double click on the comment field to see all details.

Instead of going this way each time you can use this select instead:

-- All tables inside my user
select table_name, comments 
from user_tab_comments
where table_name = :MY_TABLE;

-- All tables for all users:
select table_name, comments 
from all_tab_comments 
where owner = :MY_USER
and   table_name = :MY_TABLE;

To select the column comments use this select: (idea comes from bdelmee)

select TABLE_NAME,
  K.COLUMN_ID, COLUMN_NAME,
  K.NULLABLE, K.DATA_TYPE || 
    case when K.DATA_SCALE is not null then '(' || K.DATA_PRECISION || ',' || K.DATA_SCALE || ')'
      when K.DATA_PRECISION is not null then '(' || K.DATA_PRECISION || ')'
      when K.DATA_LENGTH is not null and K.DATA_TYPE like '%CHAR%' then '(' || K.DATA_LENGTH || ')'
    end DATA_TYPE,
  C.COMMENTS
from user_col_comments C join user_tab_cols K
using(TABLE_NAME,COLUMN_NAME)
where table_name = :MY_TABLE
order by TABLE_NAME, K.COLUMN_ID;

Now we join both selects together and get all information we need:

select 
  decode(column_id,0,TABLE_NAME,null) as TABLE_NAME,
  decode(column_id,0,null,column_id)  as COLUMN_ID,
  COLUMN_NAME, NULLABLE, DATA_TYPE, COMMENTS
from (
  select cc.TABLE_NAME,
    tc.COLUMN_ID, tc.COLUMN_NAME,
    tc.NULLABLE, tc.DATA_TYPE || 
      case when tc.DATA_SCALE is not null then '(' || tc.DATA_PRECISION || ',' || tc.DATA_SCALE || ')'
        when tc.DATA_PRECISION is not null then '(' || tc.DATA_PRECISION || ')'
        when tc.DATA_LENGTH is not null and tc.DATA_TYPE like '%CHAR%' then '(' || tc.DATA_LENGTH || ')'
      end DATA_TYPE,
    cc.COMMENTS
  from user_col_comments cc
  INNER JOIN user_tab_cols tc ON (cc.TABLE_NAME = tc.TABLE_NAME and cc.TABLE_NAME = tc.TABLE_NAME and cc.COLUMN_NAME = tc.COLUMN_NAME) 
  UNION
  select tab.table_name as TABLE_NAME, 
         0 as COLUMN_ID, '' as COLUMN_NAME,
         '' as NULLABLE, '' as DATA_TYPE,
         tab.comments as COMMENTS
  from user_tab_comments tab
) 
where table_name = UPPER(:MY_TABLE)
order by table_name, column_id  ;

That's it.