Monday 26 March 2007

ApEx is Oracle


Very often I get asked if ApEx can do this or that, what usually is a functionality available in Oracle. Recently, there was a question if using ApEx, you can operate with nested tables (varrays). My answer is: Sure you can! However, it always depends what you want do do with it.

I created a simple example here. It shows how simple it is to create, insert and query upon nested tables using ApEx.


Friday 16 March 2007

Export to XML format


I received couple of emails asking me how to export a report to an .xml file. Therefore I decided to create a small package and a demo application showing how to do that.

You can have a look here:

http://htmldb.oracle.com/pls/otn/f?p=31517:118

and read the corresponding thread here:

http://forums.oracle.com/forums/thread.jspa?messageID=1740225


Basically, you need to fetch the region source and replace bind variables with the v('ITEM') function first. Second, you use the DBMS_XMLQUERY.getxml package to transfer you SQL Query into a result set, formated as XML. After that, you just need to cut your result into smaller portions and create an .xml file out of it.

This will not work for lower versions than 2.2 or XE. For those, you need to replace the part of the package fetching the region source with this block:


SELECT plug_source
INTO v_sql
FROM wwv_flow_page_plugs
WHERE ID = LTRIM (p_region, 'R')
AND page_id = p_page_id
AND flow_id = p_app_id;


and have the appropriate grants granted by sys on

GRANT SELECT ON flows_020100.wwv_flow_page_plugs TO <your_schema>

and

CREATE OR REPLACE PUBLIC SYNONYM wwv_flow_page_plugs FOR
flows_020100.wwv_flow_page_plugs

where flows_020100 is your html_db schema.

.csv Export Problem


This problem is quite old. Many times my users complained getting no output when they try to export what they see in a report. I just found out that nobody was so really aware of this problem. Have a look at my example and the corresponding thread in the Oracle Forum.


Thursday 15 March 2007

4000 Page Clicks


I never thought so many people from all arround the world will visit my Apex Demo Application. Yesterday, I saw more than 4000 pageclicks. Google Analytics says that over 250 visitors have opened my demo application within the last 24 hours. One of the biggest groups is located in Thousand Oaks and Redwood Shore in California - arround 10% of my visitors.



That's really cool.

Wednesday 14 March 2007

ApEx Evangelists


Couple of days ago, I was asked by Dimitri and John if I would like to join their project called ApEx Evangelists. This was an offer you can't refuse. You will find an entry about myself here.

I liked the idea of getting together and promoting ApEx - it is just cool. Also, I like the idea of having all these good people on board - Patric, Dietmar, Dimitri and John. Joining our efforts, we can offer an unbeatable quality of service, training and solutions.



So, ApEx Evangelists are coming to town - stay tuned.


Export to Excel


One of the most anoying things my users complain about (and all the time) is export of reports to excel. Let us not talk about how much sense does it make to do such exporting and what do they need it for. The fact is: it is required. The problem isn't so much that you need two steps instead of one:

1. export your report and save it as a .csv to your harddrive
2. open it using excel

It is more a problem of different office versions and local settings (formats). Then you may need three or four steps instead of "only" two. Data formating may also become an issue.

In this

thread

I posted a solution for that problem. This package is still in work and once I'm done, I will post the full code behind.

You can see a demo and download the package including all installation instructions on my

Demo Application

This is a real one-step export and it works for both ApEx and XE. Surelly, this package can be enhanced in the future - now it exports your result set one-to-one into excel.

All my postings related to ApEx will point to this demo application on apex.oracle.com.


Tell us Denes, why do you want to BLOG?



So, let's start blogging. It took me a half a year to make that decission and to start doing it. I am going to focus my blogs on ApEx demos and solutions for different problems. Very often, in

ApEx forum

there are interesting questions and solutions. Once, you post your solution it dissapears and maybe it could be usefull for the others. Creating this blog, I want to select the most interesting ideas and publish them once again on a separate place.

Denes Kubicek