Function-based indexes and the easy life of a database developer

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

Yesterday i was working at a big customer site on a 9.2.0.7 database and i was checking the results of a procedure before and after the extensive change and in particular i was trying to understand why i was getting more records after the last modification.
In order to do so, i had to perform a piece-wise comparison of certain sub-strings taken from a column in a table where the only available index was the on the numeric primary key.

You can see my original query below:

select
substr(message, 33, 5) as pfx
,substr(message, 87, 9) as tt_a
,substr(message, 107, 9) as fv_a
,substr(message, 127, 7) as id_a
from test_fidx_tab a
where a.master_id = 110539
and not exists (
select 1
from test_fidx_tab b
where b.master_id = 110538
and substr(b.message, 33, 5) = substr(a.message, 33, 5)
and substr(b.message, 87, 9) = substr(a.message, 87, 9)
and substr(b.message, 107, 6) = substr(a.message, 107, 6)
and substr(b.message, 127, 7) = substr(a.message, 127, 7)
);

The table contained roughly 360,000 rows and a first attempt to execute this query did not return any results within a reasonable time (several minutes), so i started thinking of creating a function-based index on the table:

create index fx_1 on test_fidx_tab(
master_id
,substr(message, 33, 5)
,substr(message, 87, 9)
,substr(message, 107, 6)
,substr(message, 127, 7)
);

But after creating the index, the execution plan of the query did not change.
After some attempts of changing the index structure, in case the optimizer didn't like it for some reason, i finally manually added an optimizer hint.

select
substr(message, 33, 5) as pfx
,substr(message, 87, 9) as tt_a
,substr(message, 107, 9) as fv_a
,substr(message, 127, 7) as id_a
from test_fidx_tab a
where a.master_id = 110539
and not exists (
select /*+ INDEX(B FX_1) */ 1
from test_fidx_tab b
where b.master_id = 110538
and substr(b.message, 33, 5) = substr(a.message, 33, 5)
and substr(b.message, 87, 9) = substr(a.message, 87, 9)
and substr(b.message, 107, 6) = substr(a.message, 107, 6)
and substr(b.message, 127, 7) = substr(a.message, 127, 7)
);

The execution plan was now taking into account the special function-based index and the execution of the query lasted less than a second.
But wait a minute, why didn't the optimizer pick up the index automatically?

After working for years on 10g, i had almost forgotten that on 9iR2 and earlier, the cost based optimizer would not work properly until the statistics were collected on the index and on the underlying table.

exec dbms_stats.gather_table_stats(
ownname => 'TEST',
tabname => 'TEST_FIDX_TAB',
cascade => TRUE);

After gathering the statistics, the function based index was picked up by the optimizer *without* having to specify any hint.

Then i decided to repeat this exercise on a 10gR2 instance where i could verify that the optimizer was instantly picking up the function-based index because oracle 10g automatically gathers the statistics for objects having stale or missing statistics. Again, no need of adding optimizer hints.

Note that the automatic collection of statistics feature appeared in 10gR1.

May be it's not enough to justify an upgrade for a company, but certainly it's one of those things that makes happy a developer, especially when you have to suddenly downgrade the brain after years of easy life with Oracle 10g.

In memory of Carl

This no news for anyone anymore, Carl Backstrom is no longer among us, which is really hard to believe. That was the last thing i'd imagine to read when i woke up this morning.

I met him in person for the first time this year, just 5 weeks ago indeed, at Oracle Open World where, besides a couple of presentations, he was often at the Apex booth where he was proudly showing the new exciting features of Apex 4.0, like websheets, a feature that makes large use of javascript and AJAX.

On one day i had lunch with him and Joel Kallman and i really enjoyed hanging out with them for an hour or so talking about Apex but also about our kids and what they do and so on. If there is something i like about the Apex team is in that they are all very approachable, very friendly, very enthusiastic about their work and helpful with the community of developers.

That's why i'm going to miss Carl.

PLS-00363: expression cannot be used as an assignment target

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

PLS-00363: expression '%s' cannot be used as an assignment target

where %s is a placeholder for the actual string that applies to the particular case.

Oracle raises this error at compilation time when it detects an unsuitable parameter type in the parameter list of a procedure or function call, as in the following cases (the list i give here is presumably a sub-set of all possible situations):

case #1

declare
c_val   constant varchar2(10) := 'test';
begin
test_proc(c_val);           -- test_proc's parameter is declared as IN/OUT,
                           -- but c_val is a constant
end;

ORA-06550: line 4, column 11:
PLS-00363: expression 'C_VAL' cannot be used as an assignment target
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

case #2

create or replace procedure test_proc2 (p_in in varchar2) is
begin
test_proc(p_in);           -- test_proc's parameter is IN/OUT,
                          -- but p_in is an IN parameter of the calling procedure
end;

PLS-00363: expression 'P_IN' cannot be used as an assignment target

case #3

begin
test_proc(10);         -- test_proc's parameter is IN/OUT,
                      -- but you are passing a literal value
end;

ORA-06550: line 2, column 11:
PLS-00363: expression '10' cannot be used as an assignment target
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

The same error is raised in the previous situations if procedure's parameter is declared as OUT instead of IN/OUT.

case #4

declare
rtn_val   constant varchar2(10) := 'test';
begin
rtn_val := test_fn('silly case');  -- cannot assign function's result to a constant
end;

ORA-06550: line 4, column 3:
PLS-00363: expression 'RTN_VAL' cannot be used as an assignment target
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

however we could use the function to initialize the constant value inside the declaration:

declare
rtn_val   constant varchar2(10) := test_fn('silly case');
begin
dbms_output.put_line(rtn_val);
end;

Needless to say, assigning a value to a constant inside the program body is forbidden in all cases as it is a nonsense. Constants must be always initialized with some value in the declaration section.

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

ORA-01460: unimplemented or unreasonable conversion requested

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

I saw this somewhat cryptic error message while using my Oracle Regular Expression Workbench tool, before i restricted the size of the sample (multiline) string to 4000 bytes.

This error can be quickly simulated in the following fashion:

create table test_1460(string_col varchar2(4000));

declare
str1   varchar2(4001) := rpad(' ',4001);
str2  varchar2(4000);
begin
execute immediate 'select :sample_string from test_1460 ' into str2 using str1;
end;

Error report:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5

The problem is with the size of bind variable str1 that exceeds 4000 bytes.

You can also get the same error while using SQL Developer with a simple query containing a bind variable as follows:

select :sample_string as s
from dual;

when the pop-up window asking for the value to be passed to the bind variable appears, enter a large string, longer than 4000 bytes and you will get ORA-01460.


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

ORA-01489: result of string concatenation is too long

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

select rpad(' ',4000)||'A' as S
from dual

ORA-01489: result of string concatenation is too long

Whilst this error message is pretty clear in its meaning, the casual SQL user might not remember or even don't know that in Oracle, strings cannot exceed 4000 bytes inside SQL statements. This restriction however changes when we move from SQL to PL/SQL, where the limit for VARCHAR2 strings is somewhat bigger (32K bytes - 1).

declare
s   varchar2(32767);
begin
s := rpad(' ',4000)||'A'
end;

The string concatenation above will succeed without problems.
Note however that the restriction continues to apply to SQL inside PL/SQL:

declare
s   varchar2(32767);
begin
select rpad(' ',4000)||'A'
 into s
 from dual;
end;

Error report:
ORA-01489: result of string concatenation is too long
ORA-06512: at line 4

In conclusion, if you need to deal with strings larger than 4000 bytes in SQL, you should go for CLOBs, you can always split the string into smaller (4000 bytes) chunks later on, by means of CLOB functions like DBMS_LOB.SUBSTR.


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

PLS-00431: bulk SQL attributes must use a single index

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

PLS-00431: bulk SQL attributes must use a single index
ORA-06550: line 15, column 37:
PL/SQL: ORA-00904: : invalid identifier

If you got a message like this, perhaps you tried to execute or compile a
PL/SQL program containing a statement like the following:

DECLARE
TYPE NUM_TAB IS TABLE OF INTEGER INDEX BY PLS_INTEGER;

COLL_OF_FLAGGED NUM_TAB;
COLL_OF_ORDERS  NUM_TAB;
COLL_OF_PARTS   NUM_TAB;
BEGIN
...
FORALL x IN 1..COLL_OF_FLAGGED.COUNT
UPDATE order_parts
SET is_damaged = 'Y'
WHERE order_id = COLL_OF_ORDERS(COLL_OF_FLAGGED(x))
  AND part_id = COLL_OF_PARTS(COLL_OF_FLAGGED(x));
...
END;

I highlighted in red color the cause of problem: nested collection indexes.
FORALL cannot cope with nested collection indexes on 10GR2 and earlier (on 11G i didn't tried yet), which means that you will need to rewrite the statement as a non-bulk UPDATE loop structure:

...
FOR x IN 1..COLL_OF_FLAGGED.COUNT
LOOP
UPDATE order_parts
 SET is_damaged = 'Y'
WHERE order_id = COLL_OF_ORDERS(COLL_OF_FLAGGED(x))
 AND part_id = COLL_OF_PARTS(COLL_OF_FLAGGED(x));
END LOOP;
...

or alternatively you might want to modify the table structure and "demote" the pair (order_id, part_id) to a simple unique constraint, while creating a single column primary key based on a sequence number, which would probably allow you to rewrite the statement above in the following way (assuming that the collection COLL_OF_FLAGGED contains the new primary key values):

...
FORALL x IN 1..COLL_OF_FLAGGED.COUNT
UPDATE order_parts
 SET is_damaged = 'Y'
WHERE order_part_id = COLL_OF_FLAGGED(x);
...

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

Some examples of bulk changes to the apex dictionary tables

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

I thought it could be useful to show how to hack certain apex internal tables in order to perform bulk changes of certain elements, especially when such elements are repetitive ones, like region titles, item labels or move stuff around, for example relocating the content of a region into the page level help text.

Warning: these queries update apex internal tables, it is strongly recommended that you do not run them in a production environment and in any case it is highly advisable to take a backup of the whole database before attempting any of such methods!

You'll need either SYSDBA privileges or the possibility to logon as FLOWS_XXXXXX user (which normally isn't possible because the account is locked) where XXXXXX stands for the applicable release of Apex, i.e. 030100 for the currently available 3.1.
The scripts given below have been run on version 3.1.2, prior or future releases might be incompatible.
Needless to say, but just to be clear, i give no explicit warranty about the execution of these statements, therefore you are executing them entirely under your responsibility.

It is also strongly recommended that you perform the queries from SQL Developer, SQL*Plus or other analogous SQL client program and be sure to turn off autocommit, so that you have the time to check if the statement updated the expected records or not before committing the changes.

I find invaluable the help of virtual machines for development environments. The latest versions allow you take various snapshots and easily revert the VM to the state before an important change, just in case something went terribly wrong.

Last but not least, if the application being updated has been translated into other languages, it is recommended that you export XLIFF files beforehand, then seed all mapped applications, apply saved XLIFF files and publish.

So, let's begin with the bulk update of region titles, something extremely useful in case of an application containing many pages with the same region.
You can modify the sample statement below where I'm replacing the title of each and every region called "information" with "help" inside application number 11001.

(don't forget to change the application id, aka the flow_id for your application and
be sure to have read and understood the consequences of this query i gave above!):

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_PAGE_PLUGS
set PLUG_NAME='help'
where FLOW_ID = 11001
and PLUG_NAME='information';

Thereafter i suggest you to check out the result of the update by querying the corresponding apex dictionary view:

select application_id, page_id, region_name
from apex_application_page_regions
where application_id = 11001
and region_name = 'help'
order by page_id;

If everything is ok, you can proceed with the COMMIT;

A similar statement can be used to switch off a region without actually removing it from the repository:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_PAGE_PLUGS
set PLUG_DISPLAY_CONDITION_TYPE='NEVER'
where FLOW_ID = 11001
and PLUG_NAME='help';

A third example, slightly more elaborated, could be the relocation of the content of a region called "help" to the page level help attribute, as follows:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_STEPS p
set p.help_text = (
select r.plug_source
from FLOWS_030100.WWV_FLOW_PAGE_PLUGS r
where r.flow_id = p.flow_id
and r.page_id = p.id
and r.plug_name = 'help')
where flow_id = 11001
and ID > 0;

This technique can be useful to consolidate the help text that for some reason was initially entered in a page region and display it in the same place by creating a region in page zero. At that point it's possible to populate the region dynamically by retrieving the content from the apex dictionary using view APEX_APPLICATION_PAGES.
In this fashion with a single region definition, one can publish the help text across all the desired pages, excluding or including them conditionally.

The downside of this approach is in that currently apex dictionary doesn't support translated applications so, if the primary language is English, the text will always appear in English regardless of the target language. Hopefully this problem will be fixed in a future release.

Now, suppose you are happy with the new catch-all page zero based help region and you wish to get rid of the old disabled regions: how to delete them from the repository in one shot?

If you think about it for a moment, this is going to be not as trivial as the previous update process because a region can contain items and buttons, so you must decide if you want to drop the related items as well or not.

First of all, it can be a good idea to check out if any region items actually exist:

-- items (including button items)
select *
from flows_030100.wwv_flow_step_items i
where
flow_id = 11001
and flow_step_id > 0
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and plug_name = 'help');

-- (buttons)
select *
from flows_030100.wwv_flow_step_buttons b
where
flow_id = 11001
and flow_step_id > 0
and button_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = b.flow_id
and plug_name = 'help');

So, if items exist, you might want to relocate them first to other specific regions, but you can also update the column ITEM_PLUG_ID (for items and button items) or BUTTON_PLUG_ID (for buttons) to NULL, which changes the "status" of the item from region level item to page level item:

-- moves any items belonging to the 'help' region for any page > 0 to page level
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update flows_030100.wwv_flow_step_items i
set item_plug_id = null
where flow_id = 11001
and flow_step_id > 0
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and r.plug_name = 'help');

Relocating region buttons to page level:

-- moves any buttons belonging to the 'help' region for any page > 0 to page level
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update flows_030100.wwv_flow_step_buttons b
set button_plug_id = null
where flow_id = 11001
and flow_step_id > 0
and button_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = b.flow_id
and r.plug_name = 'help');

Deleting items from regions titled 'help'...

-- delete the items right away (but preserving button items)
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete from flows_030100.wwv_flow_step_items i
where flow_id = 11001
and flow_step_id > 0
and display_as != 'BUTTON'
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and r.plug_name = 'help');

Deleting buttons of all types from regions named 'help'...

-- delete the buttons right away
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete from flows_030100.wwv_flow_step_items i
where flow_id = 11001
and flow_step_id > 0
and display_as = 'BUTTON'
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and r.plug_name = 'help');

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete from flows_030100.wwv_flow_step_buttons b
where flow_id = 11001
and flow_step_id > 0
and button_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = b.flow_id
and r.plug_name = 'help');

finally, deleting 'help' regions from any page above zero:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete
from wwv_flow_page_plugs
where flow_id = 11001
and page_id > 0
and plug_name = 'help';

As an additional tip, here is the statement for changing a specific label template across an entire application. In this case i wanted to change from Optional Label with Help to Optional:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_STEP_ITEMS i
set i.ITEM_FIELD_TEMPLATE =
(select t.id
from   FLOWS_030100.wwv_flow_field_templates t
where  t.flow_id = i.flow_id
and    t.template_name = 'Optional')
where i.FLOW_ID = 11001
and  to_char(i.ITEM_FIELD_TEMPLATE) = (
select t.id
from FLOWS_030100.wwv_flow_field_templates t
where t.flow_id = i.flow_id
and t.template_name = 'Optional Label with Help');

Before committing the changes you may want to see if the result is what you expected:

select NAME, FLOW_STEP_ID,
(select t.template_name
from   FLOWS_030100.wwv_flow_field_templates t
where  t.id = to_char(i.ITEM_FIELD_TEMPLATE)
and    t.flow_id = i.flow_id) as TEMPLATE_NAME
from FLOWS_030100.WWV_FLOW_STEP_ITEMS i
where i.FLOW_ID = 11001
and  to_char(i.ITEM_FIELD_TEMPLATE) = (
select t.id
from FLOWS_030100.wwv_flow_field_templates t
where t.flow_id = i.flow_id
 and t.template_name = 'Optional')
order by flow_step_id;