Development

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

-- 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

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);

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:
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.

If the exception was caused in the declaration, we would see the “correct” backtrace, but exception would be still handled at higher level only:
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.

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]

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');

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

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
/

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.

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; 

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;

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;

DECLARE
   v_sql   VARCHAR2 (4000);
BEGIN
   v_sql := get_report_sql (:app_id, :app_page_id, FALSE);
   HTP.prn (v_sql);
END;

p_all_cols 

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

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>