Why indexing nested tables is so good

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Last week i got a call from a customer who was complaining because a procedure exporting a file was taking a very long time. After some investigation it turned out that, for some reason, during a recent application upgrade, an index on a nested table had not been (re)created.

The symptoms were clear: a procedure that normally took seconds, was now taking 20 minutes to execute.
I could quickly find out the real reason for this huge performance problem after looking at the execution plan of an inner query, that is a query executed several thousands times because it's performed inside an outer explicit cursor:


If it is certainly true that in Oracle (as for any other know database) there is no FAST=TRUE parameter, but it must be said that when a procedure is very slow, either the procedure is poorly written or some index is missing or both... ;-)
If you are lucky, then it's enough to create the right index and voilá, it's almost like turning on that magic switch.
Indeed that was my case as it was enough to (re)create the missing index on the nested table to fix the problem, as follows:

CREATE INDEX IDX_TOTES_CNT ON NESTED_CNT(NESTED_TABLE_ID,MAT_ID);

Note: NESTED_TABLE_ID is a pseudo-column provided by Oracle containing the primary key of the nested table.
Soon the execution plan started looking much better:


The success was confirmed by the fact that the export procedure now took a few seconds again, as it was before the upgrade.
But why do i need an index on a nested table in the first place?
Here is a stripped down version of the inner query. Value par_tote is a cursor parameter that is populated by the outer cursor. This value uniquely identifies the record containing the nested table to be processed. Thereafter, as you can see, i need to filter out certain values (in green) that come from nested table cnt:

select
a.mat_id,
...
b.ean
from table(select cnt
            from totes
           where tote = par_tote) a,
    exits b
where a.mat_id != info_const.conMat_ID_NRR
and a.mat_id = b.mat_id (+)
order by a.mat_id;

With an index on the nested table, the optimizer can perform the table unnesting more efficiently, selecting the recordset with a common NESTED_TABLE_ID (having the same cardinality as tote) and filtering the data basing on mat_id, without having to perform a full table scan on each iteration, which explains why the procedure was taking that outrageous amount of time to execute.

A progress bar indicator for Oracle Apex

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

One of my favorite mottoes is "do not reinvent the wheel" and this posting shows you how to "recycle" a standard Apex component like a flash chart to display a nicely integrated and animated progress bar, a widget that may come in handy for "entertaining" users during certain long-lasting operations.

So, instead of creating something from scratch, i prefer to re-use an existing and supported component of Apex to achieve the same result, which complies with my initial statement.

The need for a progress bar arose while developing a new page for the recently released DBMS_PROFILER companion application, where i let the user import data from a remote db and owing to the duration of the operation, i wanted to show the current status of the operation occurring in background.

Let me summarize the basic requirements for a successful and meaningful implementation of this widget in your application:

1. the operation must take several seconds, otherwise why bother?
2. the process must be executable in background;
   
3. user must have the flash player installed but this should not be a problem nowadays.
4. you must be using Apex 3.1 or newer.
   

A short full screen video of the apex progress bar indicator is available at the download page linked below or you can click on the image below to see a cropped version.



In order to make the progress bar indicator work in your application, you will need to perform the following actions:

1. decide whether you want to display the progress bar inside a region of the same page where you execute the process or in a different page;
2. copy the flash chart region to the desired place;
3. change the display condition to suit your needs;
4. create an after submit process that submits your procedure as a background job (an example is provided);
5. create a page validation based on the code supplied in the demo application (an example is provided);
6. modify the "core" procedure to make use of the YY_UPD_PROGRESS_BAR procedure (sample procedure YY_TEST_PROGRESS_BAR is provided).
7. adjust the chart parameters or XML source code to fine tune the progress bar indicator look (optional).
   

Those interested in a hands-on experience with the progress bar indicator demo can download the auto-installing application from yocoya.com or see a live demo at apex.oracle.com.

See more articles about Oracle Application Express or download tools and utilities.

ORA-06502 when deinstalling apex supporting objects

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It took me a while to figure out why i was getting this strange error when attempting to deinstall the supporting objects of an Apex application:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Unable to deinstall application.

I had the suspect there was something wrong with the statements being executed but after several attempts to understand which statement was the culprit, i turned to the application file dump to see if i could spot something in there.

Indeed this is what i found:

Highlighted in grey color you can see some junk characters at the beginning of the deinstallation script.
As the simple deinstallation script was assembled from copying and pasting ddl statements taken from a SQL Developer window, i imagine that, at some point, something must have gone wrong.

The tricky bit is in that neither from the apex editor nor from the deinstallation script preview page you spot anything strange, those characters are nowhere to be seen.

So, if you are getting the error above, check out the application file dump with a text editor!

See more articles about Oracle Application Express or download tools and utilities.

Skipping apex item validations if others have already failed

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick tip that may come in handy if you need to skip one or more validations if other prerequisite validations have already failed.

This technique can be useful to prevent Oracle Application Express unhandled errors during the validation phase owing to exceptions raised while checking certain non-standard conditions.

A simple scenario will certainly help to understand the point:

say we have an item called P1_STEPS and it must contain a positive numeric value.
At a first glance one may be tempted to create a simple validation based on a PL/SQL expression or SQL expression like:

:P1_STEPS > 0

This will certainly work if the end user plays a fair game, but it will throw an unhandled exception like:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
 Error  ERR-1025 Error processing PLSQL expression. :P1_STEPS > 0 

if it is a PL/SQL expression validation or:

ORA-06503: PL/SQL: Function returned without value
Error  ERR-1023 Unable perform validations.

if it is defined as a SQL expression validation, if the end user enters a non numeric character like 'A' in that form field.

Typically in Apex one should check first if a value is numeric, before attempting to use the value and apex provides the developer with several built-in validations. However even if you perform a built-in validation like "item specified is numeric" followed by "item is not null or zero" and either of the two fails, nothing will prevent Apex from running the additional validation which will end up in error.

So, basically, what i am proposing here is to "skip" a particular validation when we are already aware that other validations have failed along the path.
In order to do so, i need to check the value of an apex global variable called:
wwv_flow.g_inline_validation_error_cnt

CAVEAT
This global variable might change in the future without notice.
Although this is somewhat unlikely to happen, you must be aware that there is no official Oracle document (to my knowledge) stating that the usage of this variable is supported. As far as i know this variable is present since Apex 2.0 and probably earlier.

This constant contains a numeric counter of the inline failed validations (so far), so we can make the validation rule conditional as follows:

if :REQUEST = 'GO' and wwv_flow.g_inline_validation_error_cnt = 0
then
return TRUE;
else
return FALSE;
end if;

In this example i am assuming that the validation is already conditional and is fired when the request equals 'GO', that's why i need to combine the two conditions.

Thanks to this simple technique, now we can safely check if an item is numeric and also ascertain whether it is positive or negative or zero without incurring in run-time exceptions.

Hopefully, in the future, there will be some official API to check for this useful global variable or even a checkbox in the validation GUI that allows a developer to skip a validation if previous ones have already failed or even specify some kind of dependency.

See more articles about Oracle Application Express or download tools and utilities.

ORA-01789: query block has incorrect number of result columns

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-01789: query block has incorrect number of result columns

You may see this error in the following situation:

create table test1_1789 (
col_a varchar2(30),
col_b number,
col_c date);

create table test2_1789 (
col_a varchar2(30),
col_c number,
col_b date,
col_d char(1));

select * from test1_1789
union all
select * from test2_1789;

ORA-01789: query block has incorrect number of result columns

Table test2_1789 contains one column too many.

This fact becomes a problem because we are using * as column list specifier and it can be avoided by specifying an explicit list of columns common to both tables.


See message translations for ORA-01789 and search additional resources.

ORA-01790: expression must have same datatype as corresponding expression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-01790: expression must have same datatype as corresponding expression

This error message can be raised when you are performing a union of two queries, as follows:

create table test1_1790 (
col_a varchar2(30),
col_b number,
col_c date);

create table test2_1790 (
col_a varchar2(30),
col_c date,
col_b number);

select * from test1_1790
union all
select * from test2_1790;

ORA-01790: expression must have same datatype as corresponding expression

As you see the root cause of the error is in the mismatching column ordering that is implied by the use of * as column list specifier. This type of errors can be easily avoided by entering the column list explicitly:

select col_a, col_b, col_c from test1_1790
union all
select col_a, col_b, col_c from test2_1790;

A more frequent scenario for this error is when you inadvertently swap (or shift) two or more columns in the SELECT list:

select col_a, col_b, col_c from test1_1790
union all
select col_a, col_c, col_b from test2_1790;

ORA-01790: expression must have same datatype as corresponding expression

See message translations for ORA-01790 and search additional resources.

PLS-00457: expressions have to be of SQL types

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

PLS-00457: expressions have to be of SQL types

This error can be seen when attempting to compile a PL/SQL unit containing a FORALL statement in combination with EXECUTE IMMEDIATE, as follows:

create table tab_pls_435 (
col_a number,
col_b varchar2(30));

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
execute immediate
'insert into tab_pls_435
values :1'
using
plsql_rec_tab(i);
end;

The PL/SQL parser doesn't like the syntax of the FORALL statement in combination with EXECUTE IMMEDIATE and a collection of RECORD data type.

Note however that the program can be successfully compiled and executed if we make the FORALL statement static (as opposed to native dynamic):

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435
 values plsql_rec_tab(i);
end;

See also PLS-00436 for a list of other possibilities with native dynamic SQL.

See message translations for PLS-00457 and search additional resources.

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

You may see this error when you attempt to compile a PL/SQL unit containing a FORALL statement combined with an EXECUTE IMMEDIATE statement like, as follows:

create or replace
procedure test_pls_435
is

type tab_type  is table of tab_pls_435%rowtype; -- defines a RECORD data type
plsql_rec_tab  tab_type := tab_type();          -- defines a collection of RECORDs

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
execute immediate
'insert into tab_pls_435
 values (:1, :2)'
using
plsql_rec_tab(i).col_a
 ,plsql_rec_tab(i).col_b;
end;

Whilst it is possible to specify bulk FORALL statements, in practice there are limitations when RECORD data type collections are involved. If PLS-00436 is present in the list of parsing errors, then i suggest you to read that entry for a detailed explanation and try out alternative solutions.

See message translations for PLS-00435 and search additional resources.

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get the following error along with PLS-00435.

PLS-00436: implementation restriction:
cannot reference fields of BULK In-BIND table of records

if you attempt to compile a PL/SQL procedure containing native dynamic SQL like this:

create table tab_pls_435 (
col_a number,
col_b varchar2(30));

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
execute immediate
'insert into tab_pls_435
values (:1,:2)'
using
plsql_rec_tab(i).col_a
,plsql_rec_tab(i).col_b;

end;

The problem here is that i defined a PL/SQL table of records and i am trying to bulk bind the individual fields of the record. The main problem however is with the EXECUTE IMMEDIATE type of native dynamic SQL that is also considered invalid thus returning PLS-00435.

The solution is to completely rewrite the code, depending on the specific case, in one of these ways:

1) by eliminating the bulk FORALL (slower solution)

 ...
for i in 1..plsql_rec_tab.count
loop
execute immediate
'insert into tab_pls_435
values (:1,:2)'
using
plsql_rec_tab(i).col_a
,plsql_rec_tab(i).col_b;
end loop;
...

2) by splitting the individual record fields into different collections, thus eliminating the RECORD data type collection, while keeping the bulk FORALL:

create or replace
procedure test_pls_435
is

type col_a_tab_type is table of tab_pls_435.col_a%type;
type col_b_tab_type is table of tab_pls_435.col_b%type;
plsql_col_a_tab     col_a_tab_type := col_a_tab_type();
plsql_col_b_tab     col_b_tab_type := col_b_tab_type();

begin
plsql_col_a_tab.extend;
plsql_col_b_tab.extend;
plsql_col_a_tab(1)           := 100;
plsql_col_b_tab(1)           := 'TEST';

forall i in 1..plsql_col_a_tab.count
execute immediate
'insert into tab_pls_435
values (:1, :2)'
using
plsql_col_a_tab(i), plsql_col_b_tab(i);

end;

3) It's worth noting that using native dynamic SQL makes things more complex.
Indeed if there isn't a valid reason for using native dynamic SQL, we could rewrite the code getting rid of EXECUTE IMMEDIATE and using a shorter syntax,:

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435
values plsql_rec_tab(i);

end;

The shorter syntax requires that the PL/SQL RECORD structure matches exactly the table structure. Trying to perform the insert on a subset of columns may lead to ORA-03001.

See message translations for PLS-00436 and search additional resources.

ORA-03001: unimplemented feature

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-03001: unimplemented feature

This error can be returned attempting to compile a PL/SQL unit containing a FORALL statement, as follows:

create table tab_pls_435 (
col_a number,
col_b varchar2(30));

create or replace
procedure test_pls_435
is

type rec_type     is record (col_b tab_pls_435.col_b%type);
type tab_type     is table of rec_type;
plsql_rec_tab     tab_type;

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435 (col_b)
values plsql_rec_tab(i);

end;

My current understanding is that currently is not possible to specify a subset of columns in an bulk INSERT statement that bulk binds a RECORD type collection inside FORALL.
Note that in the code above, if we get rid of column specification, we incur in ORA-00947, however this is caused by the mismatching number of columns between the table structure and the PL/SQL RECORD data type.
If you can redefine the RECORD definition to match the table structure, then the code can be compiled and executed successfully, as follows:

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435
values plsql_rec_tab(i);

end;

I ignore if ORA-03001 can be returned also in different situations.

See message translations for ORA-03001 and search additional resources.

Updated January 4, 2008:
If you are getting this error when using a CAST/MULTISELECT construct, check out the comments section!

ORA-01007: variable not in select list

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You can get the following error:

ORA-01007: variable not in select list

when you are trying to perform a SELECT... BULK COLLECT INTO using a collection of RECORD data type and the column list in the query differs from the column list of the RECORD data type.
For instance, i was executing a program containing the following code, where the RECORD definition (implicitly created with %rowtype) is based on the structure of a local table but the data is retrieved from a remote table via a db link and the two structures are supposedly identical (but they are not):

declare
type prf_runs_type     is table of plsql_profiler_runs%rowtype;
rmt_runs_tab           prf_runs_type;
...
begin
...
execute immediate
'SELECT *
FROM  plsql_profiler_runs@yy_rmt_link
ORDER BY runid'
  bulk collect into rmt_runs_tab;
...
end;

When i executed the program, i got ORA-01007 because the remote table had a missing column.
Indeed i forgot to update the remote table definition and add the same column i have in my local database.
Note also that implicit columns defined with statements like SELECT * ... are a typical source of problems because exact column positioning becomes a critical factor. If instead we specify a fixed list of columns, the column positioning issue will be ruled out. A mismatching columns order will easily lead to ORA-01858.

Likewise, we might want to define a RECORD structure that is a subset of the original columns.
In this case we have two options:

1. manually define the record type, specifying the desired columns explicitly;
2. define a view containing the columns in the desired sequence and use %ROWTYPE to implicitly create a RECORD type based on the view definition.

I prefer the latter approach because theoretically it makes possible to shrink or enlarge the column list without touching the program, under certain conditions.

See message translations for ORA-01007 and search additional resources.