Development
JDD Publisher
Your benefits:
- Flexibility
Templates can be customized in a fast and straightforward way just by using Microsoft Excel. Eliminate discussions between IT and business about security, effort and delivery periods.
- Process Reliability
Your development environment is Microsoft Excel©. You specify the design of the Excel report. JDD Spreadsheet Publisher only cares about data and correctly adjusted relations inside the workbook. Use any of the Excel built-in functions and features to enhance your report.
Custom OSB Reporting Provider
Meet Formspider at Scotland Oracle User Group Conference
Yalim Gegrer
OTN Forums updated
This is how the new APEX Forum looks like:

I need to get used to this new look and feel, but it looks more up-to-date design wise.
You can expand a thread straight from this page by clicking on the arrow-down icon which is nice.

At first sight it might actually organise the threads nicer; for example when people start to use the likes, the content page shows a nice overview and it should give an idea what is playing in the community.
For me the search and performance are one of the most important features of a forum, after a couple of days and when the upgrade is finalised, we know more...
Too many function executions in simple query
Often i see questions like “why function is executed too many times in query”.
Lets see simple example:
We have table with 10 rows:
SQL> select id from t10;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
And we have the query:
select *
from (
select xf(t10.id) a
from t10
)
where a*a >= 25
At first it may seem that the function should be executed as many times as rows in a table T10, i.e. 10 times.
Lets test it:
SQL> create or replace function xf(p int) return int as
2 begin
3 dbms_output.put_line('F fired!');
4 return p;
5 end;
6 /
Function created.
SQL> set serverout on;
SQL> select *
2 from (
3 select xf(t10.id) a
4 from t10
5 )
6 where a*a >= 25
7 /
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired! -- 15
F fired!
F fired!
F fired!
F fired!
F fired! -- 20
F fired!
F fired!
F fired!
F fired!
F fired! -- 25
F fired!
As you see, there are more than 10 executions, so lets see the execution plan:
SQL> @xplan +projection
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2919944937
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T10 | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T10"."ID"[NUMBER,22]
Now you see that inner view was merged, and the function was executed 20 times in the filter and 5 times on the fetch after filtering(plus 1 extra execution because of it was in SQL*plus: at first it fetches one row and after it fetches by “arraysize” rows).
I see that often in such cases “no_merge” hint is suggested, but let’s test it:
SQL> select *
2 from (
3 select/*+ no_merge */ xf(t10.id) a
4 from t10
5 )
6 where a*a >= 25
7 /
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
As you can see, the number of function calls wasn’t changed.
And if we look into the plan, we understood why:
SQL> @xplan +projection
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2027387203
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T10 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"[NUMBER,22]
2 - "T10"."ID"[NUMBER,22]
20 rows selected.
Now you see, that main problem is the “filter pushdown” transformation. Previously, if we were to disable the “filter pushdown” operation, we had to use a variety of tricks, such as “materialize” hint or adding the “rownum” in expession, etc. But all these solutions require rewriting the query.
But from 11.2.0.3 we can use “_optimizer_filter_pushdown” parameter, for example:
SQL> begin
2 dbms_sqltune.import_sql_profile(
3 sql_text => 'select * from (select xf(t10.id) a from t10) where a*a >= 25'
4 ,profile => sys.sqlprof_attr(
5 q'[NO_MERGE(@SEL$2)]'
6 ,q'[OPT_PARAM('_OPTIMIZER_FILTER_PUSHDOWN' 'FALSE')]'
7 )
8 ,category => 'DEFAULT'
9 ,name => 'TEST_PROFILE'
10 ,force_match => true
11 ,replace => true
12 );
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
SQL> set serverout on
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired! -- extra execution because of sql*plus
F fired! -- extra execution because of sql*plus
Elapsed: 00:00:00.17
-- there are no extra calls when we fetches by 100 rows in pl/sql:
SQL> exec for r in (select * from (select xf(t10.id) a from t10) where a*a >= 25) loop null; end loop;
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
Spoiler:: With changing arraysize
SelectShow
-- with fetching by 1 row:
SQL> declare
2 cursor c is select * from (select xf(t10.id) a from t10) where a*a >= 25;
3 n number;
4 begin
5 open c;
6 loop
7 fetch c into n;
8 exit when c%notfound;
9 end loop;
10 end;
11 /
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired! -- 15
F fired!
F fired!
F fired!
F fired!
F fired! -- 20
F fired!
F fired! -- 22
PL/SQL procedure successfully completed.
-- with arraysize = 3
SQL> set arraysi 3
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired! -- 14
Elapsed: 00:00:00.45
SQL> set arraysi 2
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired!
F fired! -- 16
Elapsed: 00:00:00.72
PS. I found that about “_optimizer_filter_pushdown” parameter already wrote Randolf Geist
Create a Success Message using Dynamic Action - Second
var my_counter = 5;
var success_message = $('#P299_MESSAGE').val() + '<br/>'
+ 'This message will close in ' +
'<span id="my_sec">' + my_counter + '</span>' + ' seconds.';
$('.t10messages').empty();
$('.t10messages').append(<div class="t10success" style="display: none;">
</div>
');
$('.t10success').append(success_message);
$('.t10success').fadeIn(1000);
var time_in_seconds = setInterval(function() {
my_counter--;
$('#my_sec').empty();
$('#my_sec').append(my_counter);
if (my_counter == 0) {
clearInterval(time_in_seconds);
$('.t10success').fadeOut(1000);
}
}, 1000);
Using jQuery this is quite easy to acomplish.

My thought on APEX 5.0 plans
The below are plans for APEX 5.0, not marked in stone objectives... So there are no promises it will all make it in APEX 5.0 and things might be different in the final release. David didn't show any pre-release, but if this release follows previous version, most likely at ODTUG KScope '13 (end of June) the APEX team will show something live.
The focus in APEX 5.0 is on improved developer productivity, which is great to hear for us developers.Below you find the main areas the APEX development team is looking into and what my thought are on them:
Modal Dialog
At the moment you can use some plugins to create modal dialogs, but in APEX 5.0 you'll be able to declaratively declare modal pages (so real pages no regions) with a nice UI where you can add buttons on the modal and have modals on top of modals. Yes!
Drag and Drop Layout Editor (different view)
With HTMLDB 1.5, the first public release of APEX, we had the Component View where you saw the definition of your page. Since APEX 4.0 we got next to the Component View the Tree View, which allowed you to see the definition of the page the way it gets rendered. Using that view also increased productivity as you could drill-down faster to certain areas or right click on components to create other things and, based on where you were, it would skip steps in the wizard.
APEX 5.0 will introduce a new view the "Design View". This is a more visual UI to build your pages. You find the Page Elements on the left, the Layout Editor - Source in the middle, and the Component Library below that and finally the Property Editor on the right. The following screenshot is showing the concept:

So you can do drag-and-drop to create your page elements and edit them straight in the property editor. You keep doing that till you are happy and after hitting Apply Changes button it gets saved. So when you edit your page, remember the changes won't be applied automatically only when you hit the button (which I find good).
I do wonder how the Layout Editor will work with for example responsive design; will it do ratios (percentage) or will it depends on the theme that it will be "pixel perfect"? When the early adopter comes out, it will probably be one of the first thing I want to see how the html gets generated behind the scenes.
I also wonder if this editor will work on a tablet (iPad). Would be cool to be in a meeting, grap your iPad and discuss requirements, you quickly drag items on the screen hit Apply Changes and presto they see immediate results.
It will also be interesting to see how they implement the Layout Editor behind the scenes as there is so much information on the screen. When do they get more data through an Ajax process or do they load most of it on the initial load?
The other thing that David asked was who would like the APEX team to keep maintaining all 3 views. Most people like to stick to what they know and don't like to give up something, but if the APEX team needs to maintain all 3 views, it will be time consuming. So if it comes down to get more features or get to keep the 3 views, I probably would give up a view for more/better features :-)
HTML5 capabilities
APEX 4.2 already introduced many HTML5 features and I'm a big fan of having more of those declarative available; things like new input types, improved HTML5 charts, incorporate more CSS3 instead of images, use CSS3 animations for transitions, latest jQuery Mobile features (panels, responsive table, dual range slider) etc.
Another thing I would find interesting is, if they looked into local storage and offline capabilities declaratively. I never heard that would be on the list for APEX 5.0, it's just something I threw in, in this section for the future to think of.
PDF Printing
Designing your pages for PDF Printing has been something that isn't as declarative and easy to do as creating for example pages. The APEX Listener has now build-in FOP support, so they can use that and in APEX 5.0 they will add additional declarative formatting options and will make it easier to work with different templates, add control break and master/detail reports.
Web Service Support
They look into improving the Restfull web services publising capabilities, so it's easier and more performant to integrate with other databases and be in a more SOA architecture.
In a next version of SQL Developer you'll be able to define DML stored procedures using future SQL Developer
Improved framework and enhancements to the packaged applications
This is something that is true for every version. Many small changes are done that makes APEX just a little bit better, more user-friendly and more productive.
The new packaged applications were already included in the latest APEX 4.2 patch set.
Another area they look into is allowing third-party apps to be included as packaged applications. This is especially useful in the Oracle Cloud.
Multi-Row Edit Region Type
The current tabular forms are a bit old school - they got introduced in HTMLDB 1.5 and got native validations in APEX 4.0, but apart from that it's still a bit behind what you can do with normal page items. For example multiple checkboxes are not declarative in APEX 4.2 tabular forms.
APEX 5.0 will introduce the Multi-Row Edit Region Type. It will be a new Region Type, so it would be a manual change of your existing tabular forms to move to that.
The biggest advantage for the APEX team is that they don't need to maintain the old code. I'm more than happy to switch my tabular forms manually to get a more feature rich experience.
This new region type would also allow to create a master-detail-detail page.
Currently they are investigating different plugins; JQGrid is one of them which might be used behind the scenes.
Multiple Interactive reports
This has been on the list for some time, but it looks like APEX 5.0 will allow any number of IR to be defined on a single page.
Application Builder Security
The security in APEX is a big area and has been increased in every release. In APEX 5.0 they will allow different authentication schemes to be used to control developer access and there will be more pre-built schemes to pick from. Although David didn't mention it, I hope SSO with AD is one of them and a remember me functionality will be declarative available.
Websheets
APEX 5.0 will continue to modernize and enhance websheet capabilities, improve usability, evolve the user interface and simplify the creation and maintenance of data grids.
Websheets are as good as every other Wiki, but with Data Grids, which are so much more powerful.
Again the above are things the APEX development team looks into, so it's not guaranteed it will make it in APEX 5.0. So when I said "APEX 5.0 will, read it as APEX 5.0 might".
You want a specific feature in APEX? Log it in the Feature Requests app: http://apex.oracle.com/vote
More info on Oracle Application Express (APEX): http://apex.oracle.com
Ah Beautiful Brain - a Haiku
Where have you been all my life?
And where are you going?
A couple of well-known but often forgotten things for PL/SQL developers
A little example:
Suppose we need to create a function, which would call some procedure:
create or replace procedure p_nested as a int; begin select 1 into a from dual where 1=0; end; / create or replace function f_no_data_found return varchar2 as begin p_nested; return 'ok'; end; /
When we call this function in PL/SQL, it will raise NO_DATA_FOUND and we will see it:
SQL> exec dbms_output.put_line(f_no_data_found); BEGIN dbms_output.put_line(f_no_data_found); END; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "XTENDER.P_NESTED", line 4 ORA-06512: at "XTENDER.F_NO_DATA_FOUND", line 3 ORA-06512: at line 1
But it doesn’t when we call it in SQL, because it’s normal for SQL: it’s just like a result of scalar subquery that returns nothing – NULL:
SQL> set null "NUL" SQL> col ndf format a10 SQL> select f_no_data_found ndf from dual; NDF ---------- NUL 1 row selected.
So if you want the function to behave the same way in PL/SQL and SQL, just add exception handling with reraising another exception or just return null.
It must be at the level of reflexes – “select into” → “exception when no_data_found”
Otherwise, later, when code become a big and difficult, you can get unstable hidden error.
Let’s take a look at a very simple example:
If the exception was caused in the declaration, we would see the “correct” backtrace, but exception would be still handled at higher level only:
And the function has many functions calls in default parameters initialization, so I couldn’t even find out which one contains a root problem.
Spoiler:: An example of exception in default parameter assigning
SelectShow
SQL> create or replace function f_value_error return int is
2 begin
3 raise value_error;
4 return 1;
5 end;
6 /
Function created.
SQL> create or replace function f(i int:=f_value_error) return varchar2 is
2 begin
3 return 'ok';
4 exception when others then
5 return dbms_utility.format_error_backtrace;
6 end;
7 /
Function created.
SQL> set serverout on;
SQL> begin
2 dbms_output.put_line('From f: '||chr(10)||f);
3 dbms_output.put_line('****************************');
4 exception when others then
5 dbms_output.put_line('****************************');
6 dbms_output.put_line('From higher level:'||chr(10)||dbms_utility.format_error_backtrace);
7 dbms_output.put_line('****************************');
8 end;
9 /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at line 2
****************************
PL/SQL procedure successfully completed.
As you can see, there are two problems:
1. an exception was handled at higher level
2. the error backtrace does not show the call of the function “F”.
Spoiler:: In the declaration
SelectShow
SQL> create or replace function f(i int:=null) return varchar2 is
2 l_i int:=nvl(i,f_value_error);
3 begin
4 return 'ok';
5 exception when others then
6 return dbms_utility.format_error_backtrace;
7 end;
8 /
Function created.
SQL> set serverout on;
SQL> begin
2 dbms_output.put_line('From f: '||chr(10)||f);
3 dbms_output.put_line('****************************');
4 exception when others then
5 dbms_output.put_line('****************************');
6 dbms_output.put_line('From higher level:'||chr(10)||dbms_utility.format_error_backtrace);
7 dbms_output.put_line('****************************');
8 end;
9 /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at "XTENDER.F", line 2
ORA-06512: at line 2
****************************
PL/SQL procedure successfully completed.
Sometimes it’s not so dangerous, but last week I was investigating a complex case for this reason: one function when called in SQL throws strange exception, but in PL/SQL it works fine.
The exception was:
SQL> select PKG1.F(1,0,0,1275) from dual;
select PKG1.F(1,0,0,1275) from dual
*
ERROR at line 1:
ORA-06553: PLS-801: internal error [1401]
Create a Success Message using Dynamic Action
var success_message = $('#P299_MESSAGE').val();
$('.t10messages').empty();
$('.t10messages').append('<div class="t10messages"><div class="t10success"
style="display: block;"></div></div>');
$('.t10success')
.append(success_message)
.slideDown('slow');The best thing is either to open the template and have a look at the structure of the success message part or to use firebug and inspect the HTML structure on your page.
Enjoy.

Who Knew That I Knew So Much?
Before this amazing website came along, I was pretty sure that my software skills could be described as:
1. Oracle PL/SQL: excellent
2. SQL: just OK
3. Database Design: well, sort of
4. HTML: dangerous
5. XML: really dangerous
6. Java: pitiful
7. Everything else: nothing going on
See, I am probably the most narrowly specialized high-tech "expert". I know PL/SQL and that's really about it.
At least, that's what I thought I knew. According to my endorsements on LinkedIn, however, well....I will let them speak for themselves:
Now all LinkedIn has to provide is a tool that takes these endorsements and constructs an iResume.
Enable and Disable a Checkbox in a Tabular Form

SQL*Plus tips #6: Colorizing output
If you have seen a colored scripts like a fish from “Session Snapper v.4″ by Tanel Poder or OraLatencyMap by Luca Canali, you may be also want to colorize your scripts.
I’ve created the script for this purposes with predefined substitution variables.
Just download colors.sql and use it like that:
@colors.sql; prompt ::: &_C_RED *** TEST PASSED *** &_C_RESET ::: prompt ::: &_C_RED *** &_C_BLINK TEST PASSED &_C_BLINK_OFF *** &_C_RESET :::
You’l get something like this:

Spoiler:: List of variables
SelectShow
_C_BOLD_OFF Set bright mode on/off _C_UNDERLINE
_C_UNDERLINE_OFF Set underline mode on/off _C_BLINK
_C_BLINK_OFF Set blink mode on/off _C_REVERSE
_C_REVERSE_OFF Exchange foreground and background colors _C_HIDE
_C_HIDE_OFF Hide text (foreground color would be the same as background) _C_BLACK
_C_RED
_C_GREEN
_C_YELLOW
_C_BLUE
_C_MAGENTA
_C_CYAN
_C_WHITE
_C_DEFAULT Font colors _CB_BLACK
_CB_RED
_CB_GREEN
_CB_YELLOW
_CB_BLUE
_CB_MAGENTA
_CB_CYAN
_CB_WHITE
_CB_DEFAULT Background colors
In addition, i want to show simple example of printing histograms.
We can simple print histogram with query:
Spoiler:: Сolorless histogram
SelectShow
-- loading colors variables:
@inc/colors;
-- set max length of bar:
def _max_length=80;
-- columns formatting:
col bar format a&_max_length;
-- clear screen:
prompt &_CLS
with t as (-- it's just a test values for example:
select level id
, round(dbms_random.value(1,100)) val
from dual
connect by level<=10
)
select t.*
-- bar length is just " (value / max_value) * max_length" in symbols:
,floor( val * &_max_length / max(val)over()
) as bar_length
-- generating of bar:
,lpad( chr(176)
,ceil(val * &_max_length / max(val)over())
,chr(192)
) as bar
from t;

And now we can colorize it:
Spoiler:: Colorized script
SelectShow
-- loading colors variables:
@inc/colors;
-- set max length of bar:
def _max_length=100;
-- column formatting
col bar format a&_max_length;
-- clear screen:
prompt &_CLS
-- test query which prints histogram(or may be simply bars?):
with t as (-- it's just a test values for example:
select level id
, round(dbms_random.value(1,100)) val
from dual
connect by level<=10
)
select
id
,val
, case
when pct >= 0.9 then '&_C_RED'
when pct <= 0.4 then '&_C_GREEN'
else '&_C_YELLOW'
end
-- string generation:
||lpad( chr(192)
,ceil(pct * &_max_length)-9 -- color - 5 chars and reset - 4
,chr(192)
)
||'&_C_RESET'
as bar
from (
select
t.*
,val / max(val)over() as pct -- as a percentage of max value:
from t
) t2
/
Simpler Partial Page Refresh (PPR) with Dependent Fields
This entry might seem a bit trivial, but from experience I know that sometime new features that are added to the product are not detected by developers who just continue working in the "traditional way". Well here is a quick update on such a feature - PPR:
Way back in 2009 I did a blog entry that showed how to implement partial page rendering (PPR) in ADF by setting the partialTrigger attribute of a field to depend on another.
Somewhere along the way* ADF got more advanced, and today there's a simpler way to do this without the need to define the partialTrigger property for your calculated field. Instead you just define dependency between fields in the model layer (ADF BC) and your View layer automatically handles the update to the screen. This is driven by the default use of the "ppr" mode for the ChangeEventPolicy of iterators in your page's binding layer.
Here is a quick demo that shows you how to define a calculated field that depends on the values of two other fields, and have it automatically display the value when the other fields are set.
For the record here is the bit of groovy code used in the calculated field:
if (CommissionPct != null)
{return Salary * (1+CommissionPct);}
else
return Salary
* - I'm not exactly sure in which version of JDeveloper this became the default behavior, but I just looked in 11.1.1.7 and the default changeEventPolicy is not PPR for a page - but it seems like you can change it to ppr to get it working.
Win a Copy of the New Oracle Multimedia Book!
Marcelle is a great speaker and able to communicate clearly how to work with complex data in Oracle. I first met and worked with Marcelle when she flew up from Australia to speak at my first Oracle PL/SQL Programming conference in November 2005). And now she's written her first book!Managing Multimedia and Unstructured Data in the Oracle Database has just been released by PackT and covers everything a person will need to know to get them working in multimedia. And Marcelle should know; as CTO of Piction, she works with customers all around the world including major museums and universities. She performs database administration on multi-terabyte Unix and Windows environments as well as doing serious development work with PL/SQL. She has designed and built a multimedia search engine, e-commerce system, security system, reporting engine, shipping system and her own XML parser and language specifically to handle the workflow needs of managing multimedia.
The book, according to Marcelle, provides an introduction to multimedia, how to search on it, how to manage it and how integrate it into your current environment. With lots of PL/SQL examples and schema setup scripts, Marcelle promises that this book will get you loading and using unstructured data in no time at all.
Best of all (to me), Marcelle is a strong advocate of PL/SQL and recommends it for anyone to use for building web based applications and for working with multimedia. My kind of Oracle technologist. J
To encourage us to check out her book, Marcelle is offering a chance to win a copy. All you have to do is choose from the most relevant section below, and send your answer to the question to Marcelle at marcelle@xor.com.au. Marcelle tells me that "The best, most creative and accurate answer will win a copy of the book." You must submit your answer by 31 May, 2013.
1. Open Category (open to everyone): Name three key advantages for storing multimedia in a database.
2. Museum/Gallery/Print Media (open to anyone in these industries): What is the biggest issue you face when digitizing and managing any multimedia in your organization?
3. Oracle (open to any Oracle employee): Name five Oracle products that are tightly integrated with Securefiles and Oracle Multimedia.
4. Oracle ACE Program (open to any Oracle ACE or ACE Director): Which Oracle development tool or product is best designed for working with unstructured data (including multimedia)?
APEX Tabular Form - Instant Update
1. save changes instantly
2. add rows and
3. delete rows
The whole code and the steps required to get it working are explained in the Code section.
Enjoy.

Getting Interactive Report Query
http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_ir.htm#BABEFDJE
but was confused by the statement for getting the IR Query:
DECLARE
l_report apex_ir.t_report;
l_query varchar2(32767);
BEGIN
l_report := APEX_IR.GET_REPORT (
p_page_id => 1,
p_region_id => 2505704029884282,
p_report_id => 880629800374638220);
l_query := l_report.sql_query;
for i in 1..l_report.binds.count
loop
dbms_output.put_line(i||'. '||
l_report.binds(i).name||
'='||l_report.binds(i).value);
end loop;
END;
If you run this statement, you will receive a concatenated string of binds used in for the filtering and the corresponding values and not the actual query (it is just not printed out). In addition to that, you need to combine this statement with the one for getting the last viewed report id:
DECLARE
l_report_id number;
BEGIN
l_report_id := APEX_IR.GET_LAST_VIEWED_REPORT_ID (
p_page_id => 1,
p_region_id => 2505704029884282);
END;
After talking to Patrick Wolf I realized that this statement delivers almost everything you need in order to get the complete query. I combined the two statements and created a function which you can use to get a query for any of your interactive reports including replaced binds. The function code is:
CREATE OR REPLACE FUNCTION get_report_sql (
p_app_id IN NUMBER,
p_page_id IN NUMBER,
p_all_cols IN BOOLEAN DEFAULT TRUE
)
RETURN VARCHAR2
IS
v_report_id NUMBER;
v_region_id NUMBER;
v_report apex_ir.t_report;
v_query VARCHAR2 (32767);
v_column VARCHAR2 (4000);
v_position NUMBER;
BEGIN
SELECT region_id
INTO v_region_id
FROM apex_application_page_regions
WHERE application_id = p_app_id
AND page_id = p_page_id
AND source_type = 'Interactive Report';
v_report_id :=
apex_ir.get_last_viewed_report_id (p_page_id => p_page_id,
p_region_id => v_region_id
);
v_report :=
apex_ir.get_report (p_page_id => p_page_id,
p_region_id => v_region_id,
p_report_id => v_report_id
);
v_query := v_report.sql_query;
FOR i IN 1 .. v_report.binds.COUNT
LOOP
v_query :=
REPLACE (v_query,
':' || v_report.binds (i).NAME,
'''' || v_report.binds (i).VALUE || ''''
);
END LOOP;
IF p_all_cols
THEN
FOR c IN (SELECT *
FROM apex_application_page_ir_col
WHERE application_id = p_app_id AND page_id = p_page_id
ORDER BY display_order)
LOOP
v_column := v_column || ', ' || c.column_alias;
END LOOP;
v_column := LTRIM (v_column, ', ');
v_position := INSTR (v_query, '(');
v_query := SUBSTR (v_query, v_position);
v_query := 'SELECT ' || v_column || ' FROM ' || v_query;
END IF;
RETURN v_query;
EXCEPTION
WHEN OTHERS
THEN
v_query := SQLERRM;
RETURN v_query;
END get_report_sql;
You can call this function in your application or in a PL/SQL package run from an application session like this:
DECLARE
v_sql VARCHAR2 (4000);
BEGIN
v_sql := get_report_sql (:app_id, :app_page_id, FALSE);
HTP.prn (v_sql);
END;
Setting the parameter
p_all_cols
to TRUE would export all columns used in the IR SQL.
Enjoy.
ADF Mobile Logging on Android
I posted before on how to do code level debugging in your ADF Mobile application, but sometimes debugging is an overhead and you would rather just put out some log messages that will allow you to track what's going on where.
ADF Mobile has built in support for a logging framework and it is documented in this chapter in the ADF Mobile Developer Guide.
You can use a line of code like this in your code:
Logger.getLogger(Utility.APP_LOGNAME).logp(Level.INFO, this.getClass().getName(), "Shay","We invoked the button");
Then don't forget to set the right level of logging (and possibly the log message format) in the logging.properties file under your META-INF directory.
The logging chapter in the doc, doesn't mention where to actually see the messages being logged.
One utility that you can use to see your log messages comes with the Android SDK - look into the tools directory there and you'll find the ddms.bat file - run it and you'll be able to see the log messages from your application.
On the side of that utility you can also define filters to just show you the messages you are interested in.
Here is a quick demo showing how this all works together:
By the way - a comment I got pointed out that ddms is old school and you should be using the new monitor.bat at the same locaiton. This will basically work just the same and will look like this:
More tips on debugging and logging in this blog entry by Joe
Simpler Development with the new List ADF Faces component in 11.1.1.7
A new component that showed up in the JDeveloper 11.1.1.7 release is the af:listView component. This component will become more and more popular as more people target tablet devices with ADF Faces UI. The component allows you to create a scrollable list from a collection of data, and it also does fetching with ranges so you don't get too much network traffic. If you ever used a contacts list on a smart phone you'll recognize the list view source of inspiration - check out the runtime demo of the component here.
The component was actually backported into 11.1.1.7 from the 12c version - and while in the 12c version of JDeveloper there is better design time support for adding and binding a listview to a page, in the current release the work will mostly be manual.
However, for the lazy developer there are some shortcuts you can take to create the list component faster.
Here is a short video that shows you how to leverage an existing table component on your page to make the creation of the list component easier and with more functionality.
<span id="XinhaEditingPostion"></span>



