Extending supported languages for "since" date formats in Apex

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

Although Apex (3.0.1) comes with 9 optional languages (German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese) that you can install to localize Apex on-line help plus some locale information like the "SINCE" date format mask, the developer can freely create applications for languages not comprised in this set (see the list of all supported language codes).

The package APEX_UTIL contains several useful procedures and functions and one of these packaged functions is:

APEX_UTIL.GET_SINCE(date);

This function returns a string representing in words the elapsed time since a certain date (<= SYSDATE) specified as a parameter. A typical usage is for indicating how old a certain entry is, for instance news, blog postings, forum messages, emails and so on. Note that Apex's SINCE date format mask embeds the function at the report attributes level, so you don't need to explicitly include it in the query source normally. Although APEX_UTIL.GET_SINCE is a globalized function, translated versions of the text require to install apex in target language(s), so it might come in handy the following apocryphal version that makes use of Apex's globalization techniques, that is by means of translatable text messages. This can be especially useful if you need to translate the strings in one of the languages that are not directly supported by Apex.
Bear in mind however that a separate copy of each translated message will be required for each installed application.

Download get_since.sql source file.

Another difference is in that i adopted a somewhat relaxed concept of time to describe how old is a certain date, but you can change the function to suit your needs by increasing, reducing or modifying the time ranges.

See an example of Apex's standard function compared with the custom version.

Besides English, I've loaded Spanish and Italian translations in the sample application, but you must have the browser language set accordingly in order to see them.
All other languages will display messages in English.

You can use the linked script to initialize the English text messages for a certain application or modify it for entering additional translations, just change the language code and the text.

These messages can be edited individually from the Text Messages page in the globalization section of the shared components within Apex.
Note also that you don't need to create a translated version of the application for displaying the text in the desired language.

Finally, a quick tip for dealing with a report query that includes this custom function, as the most typical situation is a news report sorted by descending publication date (see the picture below):

1. the query must contain both the column containing the date value as well as the result of get_since (in the picture columns PUBDATE and AGE respectively).
   
2. Check the sort option for the date column and leave unchecked the sort option for get_since result (as pictured above). This setting is necessary to sort items properly because get_since returns a string that would result in a meaningless alphabetical sort.
   
3. You can sort the report basing on the real date column without displaying it in the report. In this fashion you'll get a report displaying just how old are the entries, starting from the most recent to the oldest.
4. Remember that you can combine column values as you prefer, you can even group different columns inside the HTML Expression property of the report column attribute page (see picture below), the current column value can be manipulated using the #COLUMN_NAME# syntax (see #AGE# in the example below), in this way you can create reports formatted exactly as you want.
   

ORA-00907: missing right parenthesis

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

There are at least a couple of situations where you may come across this syntax error message (see update at the bottom):

1. a trivial mistake, something that you could easily avoid by using SQLDeveloper's editor, that comes with a cool matching parentheses visual checking feature;
2. as a result of an elusive forbidden syntax form that is not clearly documented in the official books (through version 11.1 at time of writing).

Let's forget the former case and go straight to the latter case.

Don't get my example wrong, i know this is not the best way of doing this, but I'll talk about that later on:

select object_name, object_type
from user_objects
where object_type in (
select column_value
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1);

ORA-00907: missing right parenthesis

Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don't use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn't make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn't care about the row order inside an IN clause:

select object_name, object_type
from user_objects
where object_type in ('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE');

is perfectly equivalent to:

select object_name, object_type
from user_objects
where object_type in ('FUNCTION,TABLE,SYNONYM,PROCEDURE,VIEW');

Oracle may or may not process the rows in the same order, probably it will depend on which blocks it finds in the buffer cache, which can vary between the first execution and the second execution, so don't rely on swapping items in the IN clause if you want to change the order in which they are processed.

Let's go back to the original query: in the example above i used the custom function csv_to_table to convey the idea of a query with a parametric IN clause, that is a clause that is not made up of literal values but that could accept a string parameter (a comma separated string list) that could be set somewhere else.

So, if the purpose of this query was to process the rows in the USER_OBJECTS view in the specified object type order, then we would have to rewrite the query completely:

select a.object_name, a.object_type
from user_objects a, (
select column_value object_type, rownum as n
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by n) b
where a.object_type = b.object_type
order by b.n;

Note also that there are at least two ways of solving the syntax problem without touching the ORDER BY clause:

creating a view as follows:

create or replace my_object_types_v as
select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1;

and then issue:

SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

or alternatively use the WITH clause:

WITH my_object_types_v as (select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1)
SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

but as already remarked, neither of the two will force Oracle to process the rows of USER_OBJECTS in the given order.

Updated february 29, 2008:
This error is also returned when calling a user-defined (PL/SQL) function with named parameters inside a SQL statement:

SELECT my_function(p_input_value => 0) AS my_fn
FROM DUAL;

ORA-00907: missing right parenthesis

Named parameters are only allowed in PL/SQL programs (but not in SQL statements inside PL/SQL programs), therefore the solution is to pass parameters in positional form.

Read more about the different parameter passing options in the Oracle 10G documentation.

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

Finding your way out of SQLDeveloper's DBMS_OUTPUT pane

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

SQL Developer 1.2.1 build 32-13:
have you ever used the DBMS_OUTPUT pane inside Oracle SQL Developer?

Unlike my friend John Scott, i like to open several SQL Worksheet against the same database connection.
This must be the reason as to why i noticed one curious fact about the DBMS_OUTPUT pane that he wasn't apparently aware of, according to a recent message interchange.

In brief, if you open multiple worksheets against the same connection, you'll notice that each one comes with an associated series of tabs, among which there is the DBMS_OUTPUT pane (see the center of the picture in the screenshots below).
According to my personal experience, the pane associated with the first worksheet where you enabled the DBMS_OUTPUT will drive all the DBMS_OUTPUT of the session, as illustrated by the following series of pictures.

The first worksheet belongs to the first session. After enabling server output and after executing the procedure, it will display the content of the buffer:

The second connection has got its own DBMS_OUTPUT pane also displaying some output:

The third worksheet however is created against the first connection and it won't display anything.


However, if we switch back to the first worksheet, we find the output of the third worksheet...


This can be explained by looking at the active oracle sessions opened by SQL Developer, where it becomes clear that multiple SQLDeveloper worksheets against the same database connection will be mapped to the same oracle session:

select sid, serial#, username, module, action
from v$session
where program = 'SQL Developer';

SID     SERIAL#   USERNAME  MODULE                      ACTION
------- --------- --------- --------------------------- -------------
116     38309     SYS       SQL Developer            
117     8352      SYS       Oracle SQL Developer        Code Insight
121     13186     YOCOYA    Oracle SQL Developer        Code Insight
125     26877     YOCOYA    SQL Developer            

4 rows selected

SQL Developer uses two distinct sessions for each connection, one is marked as "Code Insight" and is for handling the object tree, the other one is for executing the SQL scripts. I opened two db connections and this accounts for the 4 rows that you see in the report above.

Although this makes perfectly sense from a technical point of view, it can result in puzzling results, as far as DBMS_OUTPUT is concerned.

Note also that if you set server output off and on again from in the third pane, then you will redirect the output to the active tab and the other one will stop being updated.

Not a big deal, but something to be aware of.

Creating an ISO image from a zip file

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

Quick link to the original posting, my thanks to Gary for sharing this useful tip for MAC OS X.

Some days ago i was working via SSH/VNC on my Mac OS X server where i needed to install Oracle 11G for linux 64-bit.

One of the great features of VMWare Fusion (present also in other VMWare products) is the possibility of attaching an ISO disk image as if it were a physical CD or DVD, avoiding the physical burning of CD or DVD media.
Unfortunately i hadn't such ISO image available, i had instead the source files in a zip file downloaded on the Mac.

I could have copied the files from the host to the guest operating system or executed from a shared folder, but i preferred to see if i could find a way of stuffing the content of a directory into an ISO image file using plain OS X commands.

Fortunately, Google came to the rescue and i stumbled upon Gary's blog, where i found such great tip, that i easily adapted for my case:

First of all unzip the archive:

unzip linux.x64_11gR1_database.zip

This will create a subdirectory called "database" into the current one.
Finally, create the ISO disk image:

hdiutil makehybrid -o oracle11g.iso -iso -joliet -hfs -udf database

Now you can mount the resulting ISO file through VMWare Fusion.
If for some reason you cannot run the installer, e.g. you get a "Permission denied" error, it can be that the "cdrom" is not mounted properly. By mounted properly i mean that a line like the following must be present in the /etc/fstab file:

/dev/cdrom   /mnt/cdrom  auto  exec,noauto,owner,ro 0 0

For some reason this line was present in a Xubuntu Linux VM i've created, but was not present in Oracle Linux and i don't really know if it is a "quirk" of the auto-mount feature or a normal behavior.

ORA-25176: storage specification not permitted for primary key

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

This error message can be returned when an unexpected clause is added in a primary constraint specification for an index organized table:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
PRIMARY KEY (key_a, key_b, key_c) USING INDEX COMPRESS
) ORGANIZATION INDEX
/

ORA-25176: storage specification not permitted for primary key

Two facts must be noted here:
the "USING INDEX" clause above becomes perfectly legitimate if we remove the COMPRESS keyword or if we move it after the ORGANIZATION INDEX clause, as already explained in a similar situation occurring in Oracle XE, where a different error message is returned (see ORA-14071).
Likewise, if you remove ORGANIZATION INDEX, the COMPRESS clause will be accepted without problems:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
constraint pk_test_table primary key(key_a, key_b, key_c) USING INDEX COMPRESS 2
)

In conclusion, you just picked a wrong syntax, if you want to enable compression on an index organized table, then change the statement as follows:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
PRIMARY KEY (key_a, key_b, key_c) USING INDEX
) ORGANIZATION INDEX
COMPRESS -- see the note below
/

As already mentioned in a previous posting, you can also specify COMPRESS n, where n is a number lesser than the number of key columns and in this specific case you could specify either COMPRESS 1 or COMPRESS 2, because the key is made up of three columns.
See message translations for ORA-25176 and search additional resources

ORA-14071: invalid option for an index used to enforce a constraint

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

This error seems to be the Oracle XE equivalent of ORA-25176 that is apparently returned on Oracle standard editions when you execute a DDL statement like:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
PRIMARY KEY (key_a, key_b, key_c) USING INDEX COMPRESS
) ORGANIZATION INDEX
/

ORA-14071: invalid option for an index used to enforce a constraint

As explained in a previous posting about ORA-00439, it's perfectly possibile to specify the COMPRESS option for an Index Organized Table (IOT), however you must specify this clause in the right place:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key(key_a, key_b, key_c)
) ORGANIZATION INDEX
COMPRESS
/

See message translations for ORA-14071 and search additional resources

ORA-25194: invalid COMPRESS prefix length value

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

While executing a DDL statement like:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key (key_a, key_b, key_c)
) ORGANIZATION INDEX COMPRESS 3

or:

CREATE INDEX test_idx ON test_table(key_a, key_b, key_c) COMPRESS 3

You've got:

ORA-25194: invalid COMPRESS prefix length value

You need to remove the compression prefix (3 in the example above) after the COMPRESS clause or specify a compression prefix number that is less or equal than 2:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key (key_a, key_b, key_c)
) ORGANIZATION INDEX COMPRESS -- this is equivalent to COMPRESS 2 in this case

or:

CREATE INDEX test_idx ON test_table(key_a, key_b, key_c) COMPRESS 1

More details about index compression and applicable cases available in a previous posting.

See message translations for ORA-25194 and search additional resources

ORA-00439: feature not enabled: Table compression

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

If you are getting ORA-00439 for a different feature, you may want to check out other postings concerning this family of error messages.

If you are wondering why you get the error below upon executing a CREATE TABLE like this:

CREATE TABLE test_table (
col_a varchar2(10),
col_b number(5,0)
) COMPRESS
/

ORA-00439: feature not enabled: Table compression

The error can show up with a slightly different text (for version earlier than 10R2 perhaps):

ORA-00439: feature not enabled: Heap segment compression

the reason is very simple: the RDBMS version is not an Enterprise Edition.
In my case it was XE, but the same would happen with a Standard Edition.

But not all is lost: index compression is available across all editions, since version 9i.

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_c varchar2(30)
)
/
CREATE UNIQUE INDEX test_idx ON test_table(key_a, key_b, key_c)
COMPRESS
/

By specifying COMPRESS without a compression prefix, i am actually saying to Oracle to compress the index as much as possible (in this case 2 columns out of 3).
The compression prefix is the number of columns belonging to the index that we want to factor out. This number must be less than the total number of columns that make up the index otherwise you'll get the error ORA-25194, explained in another posting.

If i specify "COMPRESS 1" then Oracle will factor out only the first column of the index. With COMPRESS 2, the first two columns will be packed. This process can be very efficient (from a block usage perspective) when there are many repeating combinations of the columns being compressed, in other words, an ideal situation is when the first two columns tend to be equal across multiple rows (that is the third column is the component varying more frequently) :

key_a	key_b	key_c
AAA	111	1
AAA	111	2
AAA	111	4
AAA	111	100
AAA	112	2
AAA	112	5
AAA	112	7

whereas a far-from-optimal situation is represented below:

key_a	key_b	key_c
AAA	111	1
BBB	121	2
ABC	131	4
CDA	131	100
DDD	142	2
ABD	142	5
CDF	152	7

As you see, in the latter case, values in the first two columns are varying from row to row, thus making the index compression useless if not negative at all.

This is to say that in order to build an efficient database, you must know in advance the data that is going to be stored in the tables. This may seem obvious, but there can be situations where you cannot predict easily how the data will look like, so you must accept (or make you customer accept...) some degree of iterative refinement process if they cannot supply meaningful sample data during the database design phase.

But let's get back to the point: to give an idea using a real case, i had a table with an index made up of 3 columns (with a value distribution similar to the optimal case shown above) and before compressing it, the index was taking up roughly 20Mb (2560 blocks).
With COMPRESS 1, the index size dropped to 14.5 Mb (1792 blocks) and with COMPRESS 2 it shrinked further to 12.2 Mb (1536).
Needless to say, a reduced index size means an increasing probability of having its blocks cached in the buffer, thus improving I/O at the expense of some more CPU time.

Last but not least, COMPRESS can be specified on IOTs as well, just watch out for the correct syntax:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key (key_a, key_b, key_c)
)
ORGANIZATION INDEX
COMPRESS n -- n must be less or equal than 2 because there are 3 columns in this index

otherwise you'll get one of the errors (ORA-25176 or ORA-14071) explained in the related postings.

See message translations for ORA-00439 and search additional resources

ORA-00951: cluster not empty

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

A cluster can be dropped only if the participating tables have been dropped first, otherwise you'll get:

ORA-00951: cluster not empty

Alternatively, if you are absolutely sure, you can add the INCLUDING TABLES clause:

drop CLUSTER dummy_cluster INCLUDING TABLES
/

If there are any tables whose constraints point to the cluster table and are preventing you from dropping the cluster and the tables, you can also add the CASCADE CONSTRAINTS clause to the statement:

drop CLUSTER dummy_cluster INCLUDING TABLES CASCADE CONSTRAINTS
/

See message translations for ORA-00951 and search additional resources

ORA-00943: cluster does not exist

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

This is the first of a series of articles regarding Oracle cluster tables.
Index clustered tables and hash clustered tables are covered in detail in the Concepts book and the full syntax of the related commands can be found in the SQL Reference.

There is nothing intriguing in this error, for instance, suppose to execute:

create table dummy_table (
code  number(5,0),
lang  varchar2(30),
descr  varchar2(1024)
) cluster dummy_cluster (code)
/
ORA-00943: cluster does not exist

drop CLUSTER dummy_cluster
/
ORA-00943: cluster does not exist

Evidently, in both cases the dummy cluster did not exist.

The same error is also returned attempting to drop a cluster in another user's schema:

drop CLUSTER user2.dummy_cluster
/
ORA-00943: cluster does not exist

In this case it is necessary to hold DROP ANY CLUSTER privilege.


You can check for the existence of a cluster by querying the following dictionary views:

select * from user_clusters;
select * from all_clusters;
select * from dba_clusters; -- requires DBA or SELECT_CATALOG_ROLE role

See message translations for ORA-00943 and search additional resources

Harry Potter and the scary secret of the five cursed error numbers

Will Mrs. Rowling ever forgive me for borrowing a couple of paragraphs from one of her books and stuff them with double (or triple) meanings?

"DBMS_OUTPUT messages were still lashing the windows, which were now minimized, but inside something looked wrong and awful. The monitor glowed over the countless office chairs where programmers sat typing, talking, doing their work or, in the case of Fred and Barney, trying to find out what would happen if you fed a handful of bizarre numbers to UTL_LMS.GET_MESSAGE.
Fred had "rescued" the enigmatic, five-parameters function from an Elements of Almost Undocumented Oracle Packages class and it was now testing it intensively on a table surrounded by a knot of anonymous bloggers."

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_LMS", line 4
ORA-06512: at line 9
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:
*Action:

A couple of days ago i wrote a little program using the UTL_LMS.GET_MESSAGE packaged function on Oracle 10G R1 and i came across this weird situation:
looping on all error numbers between 1 and 50000, the function always returns zero (success) as return code, even for non-existent error messages, but there are 5 numbers (the magical ones) that will cause the function to blow up with an ORA-06512 exception and they are:

33422 35982 36188 36906 36976

Oddly enough, the documentation says that when the procedure fails it should return -1, but i could never get such value for any error numbers in the aforementioned range, given 'rdbms' as product and 'ora' as facility.

33422, 35982 and 36906 do not appear in the official documentation, but 36188 and 36976 do.

However, while reading the description for ORA-36976 and to my utter dismay, i realized soon that the reason of the failure of UTL_LMS.GET_MESSAGE was less than mysterious:
i had simply undersized the receiving variable for the error message description, that in just these 5 cases can be longer than 255 characters!

To my discharge i must say that i sized the variable just a little bit bigger than what suggests the example section of the function (by the way, there is also some syntax problem in the sample code for the other packaged function called FORMAT_MESSAGE, written with triple S....), but the "stress-testing" of this function showed that a no-brainer value like 1024 will make it work in all cases.

In conclusion, i learned a couple of lessons:

1. never rely on the sizing of variables given in the package examples
2. never forget that ORA-06512 may be a symptom of an undersized string variable passed as parameter.

Too bad, even for Harry Potter ;-)

PS: In case someone takes this posting too seriously, please note that these error numbers are showing up on 10G R1 (Windows) only, on 11G (Windows) they become six and they are 19151, 19193, 33422, 35280, 35982 and 36188...

8 crazy things? i'm game!

As i've been tagged by my friend John Scott, i cannot escape from the fate.

Before saying anything else, let me summon just a few acquaintances in this crazy chain, David Peake, Joel Kallman, Patrick Wolf and Bernhard Fischer-Wasels as most of the others I know have been already tagged.

In order of (descending?) craziness:

1. I had to learn RPG IV in my thirties.
2. I was used to play tennis until a broke a meniscus, otherwise i'd be playing at Wimbledon by now.
3. One night in London, at a restaurant, i met the pop-star Lionel Richie but i was tempted to ask his girlfriend for the autograph.
4. I am probably the only Italian who cultivates Mexican green tomatoes and authentic Mexican chillies and, for this reason, i hope one day I'll receive an honorary Mexican citizenship.
5. Some years ago i was seriously thinking of taking lessons of Nahuatl during summer time.
   
6. I was an HP calculator addict and i spent many night writing assembly routines instead of hanging out with friends and girls (but later i caught up...). Now i am in the Apple Mac phase.
   
7. I love cooking, including prepare home made pasta and Mexican recipes and i publish recipes and photos of the results on a different blog.
8. I haven't set up yet an Oracle Apex site dedicated to Mexican chillies.

There would be more to add, but we said just eight... :-D

PLS-00382: expression is of wrong type

There are at least two distinct situations where this error can be returned, the former scenario involves collections and the latter is a relatively "simple" implicit conversion problem.

PLS-00382: expression is of wrong type

Note that PLS-00382 can be raised when you try assign a value that cannot be converted implicitly into the target data type, as in the following example:

declare
a   integer;
b   timestamp := systimestamp;
begin
a := b;
dbms_output.put_line(b);
end;

Error report:
ORA-06550: line 5, column 8:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

See the following Oracle 10G document about allowed implicit conversions, where, for some reason, the implicit conversion between timestamps and dates is not mentioned and the new 11G document doesn't include it either, but is indeed perfectly working:

DECLARE
a   DATE;
b   TIMESTAMP WITH TIME ZONE := systimestamp;
begin
a := b;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;

09-JAN-08
09-JAN-08 02.37.02.339176 PM +01:00

However you should always avoid using implicit conversions as they are affected by the server and client locale settings and a program that works in your environment may stop working altogether when executed from another client.

PLS-00642: local collection types not allowed in SQL statements

This error is easily explained:

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a','b');
LLISTA := WARRAY('c','d','e');
LLISTA := VALS;
number := VALS.COUNT;

select column_value
bulk collect into LLISTA
from table(cast (VALS as string_table_type));

dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;

Error report:
ORA-06550: line 13, column 21:
PLS-00642: local collection types not allowed in SQL statements
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Collection VALS is not a SQL object type (a user-defined type anyway), but it's a PL/SQL local type, that is a type defined inside the program.
Casting the local type to an external object type, string_table_type in the sample code above, is not sufficient to make it work, you need to change the collection type altogether, as shown in the PL/SQL snippet of the related article.

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

PLS-00707: unsupported construct or internal error

A reader asked me to investigate why he was getting the error being discussed whenever he tried to execute the following PL/SQL block:

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
number pls_integer := 1;
begin
--VALS := WARRAY(null, null);
--LLISTA := WARRAY(null);
--LLISTA := VALS;
dbms_output.put_line(number);
loop
 exit when LLISTA(number)%NOTFOUND;
LLISTA(number) := VALS(number);
Number:= Number + 1;
end loop;
end;

Error report:
ORA-06550: line 0, column 0:
PLS-00707: unsupported construct or internal error [2704]
ORA-06550: line 12, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

There are several things worth noting in this tiny PL/SQL snippet, but let's start off with the main issue.
PLS-00707 is raised because Oracle doesn't support the %NOTFOUND construct with collections as it is meant to be used with cursors. I must guess that the user got confused with the EXISTS method that allows to check for the presence of non-initiatilized elements in collections (not to be confused with null elements!).

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd');
--LLISTA := VALS;
loop
--exit when LLISTA(number)%NOTFOUND;
exit when not LLISTA.EXISTS(number);
LLISTA(number) := VALS(number);
number:= number + 1;
end loop;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number  - 1));
end;

If the intention of this procedure was to copy the elements of collection VALS into LLISTA, then it must be noted that if both varrays are exactly of the same type and dimension, then we could get rid of the loop altogether:

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;

Entire collections can be copied as if they were simple variables when the aforementioned conditions are met.
Note also that the third element of LLISTA ('e') has disappeared after the copy, so, don't expect that Oracle copies only the subset of the collection containing the initialized elements, you'll need to do it yourself (see later on).
Let's see what happens when the type looks equal but is not, as in the following example where i duplicated they custom type WARRAY:

declare
type WARRAY is varray(324) OF varchar2(200);
type WARRAY2 is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY2;
number pls_integer := 1;
begin
VALS := WARRAY2('a', 'b');
LLISTA := WARRAY('c', 'd');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;

Error report:
ORA-06550: line 10, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The collection assignment doesn't work anymore because even if WARRAY and WARRAY2 appear to be perfectly equivalent, they are not the same type, strictly speaking.

Finally, let's go back to the previous step, the working one, and assume that we want to copy only a subset of the elements of VALS. I guess that for some reason VALS contains either updated or saved values that at a certain point we want to restore in LLISTA, but without erasing the elements of LLISTA that have no corresponding subscript in VALS:

declare
LLISTA string_table_type;
VALS   string_table_type;
BKP    string_table_type;
n      pls_integer := 1;
begin
VALS := string_table_type('a','b');
LLISTA := string_table_type('c','d','e','f');
BKP    := LLISTA;
n      := VALS.count;
select c
bulk collect into LLISTA
from (
 select column_value as c
   from table(VALS)
 union all
 select c
   from (select rownum r, column_value as c
                 from table(BKP))
   where r > n
);
dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
dbms_output.put_line(LLISTA(i));
end loop;
end;

4
a
b
e
f

Again, some remarks: first of all i had to use an externally defined collection type (string_table_type), not a locally defined collection type because PL/SQL doesn't allow me to use local collections in SQL statements:

create or replace TYPE "STRING_TABLE_TYPE"                                                                                                                                                                                                                                                                                                     as table
 of varchar2(200)

Secondly, i had to create a third collection named BKP where i staged the data of LLISTA because otherwise i'd get an empty subset. Oracle is implicitly erasing LLISTA before performing BULK COLLECT so i cannot access its elements while performing the query.

Is this memory wasting approach faster than manually copying each and every element from VALS to LLISTA?

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
n      pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e', 'f');
loop
exit when not VALS.EXISTS(n);
LLISTA(n) := VALS(n);
n:= n + 1;
end loop;

dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
 dbms_output.put_line(LLISTA(i));
end loop;
end;

4
a
b
e
f

The performance comparison test is left to the reader as exercise, as some professors say when they are running late for lunch... ;-)

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

ORA-06532: Subscript outside of limit

This error is returned if, for some reason, a subscript value is lower than 1 (one) or greater than the declared upper bound of a varray.
A negative or zero value subscript will also cause an error when working with nested tables.
Do not confuse the upper bound with the actual number of initialized elements in the varray or table. A varray may contain 5 elements out of a maximum of 10, so if you specify 6 as subscript, the error returned will be different, as explained in a previous article.

Let's look at a couple of simple situations:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type(null, null, null);
BEGIN
my_array(0) := 'a';
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
if necessary.

Another case being:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type(null, null, null);
BEGIN
my_array(11) := 'a';
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
if necessary.

It should be clear that in both situations the subscript is out of range.
A slightly different situation is the following one:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
my_array.EXTEND(11);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
  or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
  if necessary.

In this case it's easy to spot that we tried to initialize the collection to a larger number of elements that it can hold, but there can be subtler situations as follows:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type(null);
BEGIN
my_array.EXTEND(10);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

It would be fine to extend the varray by 10 elements if we hadn't already initialized one element at declaration time (the null element above), indeed if you remove the null from the type constructor, the program will run without errors.

If you are populating the collection by means of some iterative process where you extend (that is initialize) the elements one at a time, then you must ensure that you do not extend the varray beyond its limits.
As already explained, you can check the limits with the collection method LIMIT, provided you have initialized the collection.
-----------------------------------------------
ORA-06532: Indice inferiore fuori dal limite
ORA-06532: Subíndice fuera del límite
ORA-06532: Subscript fora de límit
ORA-06532: Indice hors limites
ORA-06532: Index außerhalb der Grenzen
ORA-06532: Δείκτης εκτός ορίου
ORA-06532: Subscript uden for begrænsning
ORA-06532: Indexvariabel utanför gränsvärdet
ORA-06532: Subskript utenfor grense
ORA-06532: Alikomento ylitti rajan
ORA-06532: Határon kívüli index
ORA-06532: Indicele este în afara limitei
ORA-06532: Subscript ligt buiten limiet.
ORA-06532: Subscrito além do limite
ORA-06532: Subscrito fora do limite
ORA-06532: Индекс превышает пределы
ORA-06532: Dolní index přesahuje limit
ORA-06532: Dolný index mimo limitu
ORA-06532: Indeks (współrzędna elementu tablicy) spoza zakresu
ORA-06532: İndis sınırın dışında
ORA-06532: Subscript outside of limit

See message translations for ORA-06532 and search additional resources

ORA-06533: Subscript beyond count

This error is easily explained with the help of a few examples:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type(null, null, null);
BEGIN
my_array(1) := 'a';
my_array(2) := 'b';
my_array(3) := 'c';
my_array(4) := 'd';
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06533: Subscript beyond count
ORA-06512: at line 8
06533. 00000 -  "Subscript beyond count"
*Cause:    An in-limit subscript was greater than the count of a varray
or too large for a nested table.
*Action:   Check the program logic and explicitly extend if necessary.

my_array has been initialized with three elements (out of a maximum of 10), but at line 8 we are trying to set the value of a fourth element.
You cannot set a non-existent varray or table element if you haven't properly initialized the varray as shown in a previous posting.

Another typical situation involves nested tables:

DECLARE
TYPE table_type IS TABLE OF VARCHAR2(200);
my_table        table_type := table_type();
BEGIN
my_table.EXTEND(5);
my_table.TRIM(5);
my_table(3) := 'c';
dbms_output.enable;
dbms_output.put_line('total:'||my_table.COUNT);
END;

Error report:
ORA-06533: Subscript beyond count
ORA-06512: at line 7
06533. 00000 -  "Subscript beyond count"
*Cause:    An in-limit subscript was greater than the count of a varray
or too large for a nested table.
*Action:   Check the program logic and explicitly extend if necessary.

By trimming 5 elements starting from the end of a collection consisting of 5 elements, you are shrinking its size to zero and as a consequence you cannot set the third element to a value, because it's like having unitialized all the elements.

Unfortunately DELETE and TRIM can easily lead to some degree of confusion, because their behavior is not so consistent as it could be:

DECLARE
TYPE table_type IS TABLE OF VARCHAR2(200);
my_table        table_type := table_type();
BEGIN
my_table.EXTEND(5);
my_table.DELETE(1,5);
my_table(3) := 'c';
dbms_output.enable;
dbms_output.put_line('total:'||my_table.COUNT);
dbms_output.put_line('last subscript:'||my_table.LAST);
dbms_output.put_line('first subscript:'||my_table.FIRST);
END;

total:1
last subscript:3
first subscript:3

However, if instead of selectively deleting the elements from 1 to 5, you omit the parameters altogether:

DECLARE
TYPE table_type IS TABLE OF VARCHAR2(200);
my_table        table_type := table_type();
BEGIN
my_table.EXTEND(5);
my_table.DELETE;
my_table(3) := 'a';
dbms_output.enable;
dbms_output.put_line('total:'||my_table.COUNT);
dbms_output.put_line('last subscript:'||my_table.LAST);
dbms_output.put_line('first subscript:'||my_table.FIRST);
END;

Error report:
ORA-06533: Subscript beyond count
ORA-06512: at line 7
06533. 00000 -  "Subscript beyond count"
*Cause:    An in-limit subscript was greater than the count of a varray
   or too large for a nested table.
*Action:   Check the program logic and explicitly extend if necessary.

This happens because the DELETE method without arguments physically removes the collection elements, whereas DELETE(m,n) or DELETE(n) simply erase the contents, making the element null.

See more examples and situations involving DELETE, TRIM and related errors.
-----------------------------------
ORA-06533: Indice inferiore oltre il conteggio
ORA-06533: Subíndice mayor que el recuento
ORA-06533: Subscript més enllà del comptador
ORA-06533: Valeur de l'indice trop grande
ORA-06533: Index oberhalb der Grenze
ORA-06533: Δείκτης εκτός μέτρησης τιμών
ORA-06533: Subscript uden for antal
ORA-06533: Indexvariabel större än faktiskt antal
ORA-06533: Subskript over antall
ORA-06533: Alikomento ylitti määrän
ORA-06533: Számlálón kívüli index érték
ORA-06533: Indicele este mai mare decât dimensiunea tabloului
ORA-06533: Subscript is te hoog.
ORA-06533: Subscrito acima da contagem
ORA-06533: Subscrito para além da contagem
ORA-06533: Индекс выходит за пределы счетчика массива
ORA-06533: Dolní index přesahuje čítač
ORA-06533: Dolný index presahuje počet
ORA-06533: Indeks (współrzędna elementu tablicy) przekracza licznik
ORA-06533: İndis sayımın ötesinde
ORA-06533: Subscript beyond count

See message translations for ORA-06533 and search additional resources

PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter

ORA-06550: line 3, column 42:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter

See a typical scenario described in the linked article.

PLS-00306: wrong number or types of arguments in call to 'DELETE'

This compilation error occurs when you specify a parameter for the DELETE method, as follows:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
my_array.EXTEND(10);
my_array.DELETE(4);
dbms_output.enable;
dbms_output.put_line('total:'||my_array.COUNT);
END;

Error report:
ORA-06550: line 6, column 1:
PLS-00306: wrong number or types of arguments in call to 'DELETE'
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The DELETE method doesn't accept parameters when it's applied to varrays.
When dealing with varrays you can only clear the whole array by specifying the DELETE method without any parameters.
If you want to remove the last n elements, use the TRIM(n) method instead.

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
my_array.EXTEND(10);
my_array.TRIM(4);
dbms_output.enable;
dbms_output.put_line('total:'||my_array.COUNT);
END;

total:6

One or two numeric parameters are allowed in DELETE when the collection is defined as TABLE, as in this example of sparse nested table:

DECLARE
TYPE table_type IS TABLE OF VARCHAR2(200);
my_table        table_type := table_type();
BEGIN
my_table.EXTEND(10);
my_table.DELETE(1,10);
my_table(5) := 'a';
dbms_output.enable;
dbms_output.put_line('total:'||my_table.COUNT);
dbms_output.put_line('first subscript:'||my_table.FIRST);
END;

total:1
first subscript:5

See other articles on collection tips and techniques.

ORA-06531: Reference to uninitialized collection

A recent comment of a reader about an obscure PL/SQL compiler error suggested me to begin writing a few postings about errors that you may come across when working with collections, so this is the first of a series, that i don't know yet how short or long will be, not counting the errors already described in previous articles.

Let's have a look at one of the most common ones as it is reported by SQLDeveloper:

ORA-06531: Reference to uninitialized collection
ORA-06512: at line 10
06531. 00000 -  "Reference to uninitialized collection"
*Cause:    An element or member function of a nested table or varray
was referenced (where an initialized collection is needed)
without the collection having been initialized.
*Action:   Initialize the collection with an appropriate constructor
or whole-object assignment.

What does this mean?
It's easy to explain, let's take the following PL/SQL block:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type;
BEGIN
dbms_output.enable;
 dbms_output.put_line(my_array.COUNT);
END;

ORA-06531: Reference to uninitialized collection
ORA-06512: at line 6

You cannot use the COUNT method without first initializing the collection my_array.
Likewise, you cannot use the LIMIT method either, even if LIMIT refers to the upper bound that was specified in the declaration and theoretically has little to do with the actual varray content (see below for an example).
If you need to store the varray size in a variable for easier referencing for instance or you don't want to clutter the source with such literal values, you'll need to initialize the array first, as explained later on.

my_array has been declared of type array_type, but it has not been initialized and in this situation the collection is said to be atomically NULL. Atomically null means that there are no items whatsoever in the collection that is why you cannot count them.

In order to initialize a collection you must use the type constructor, that strange beast that is named after the collection type (array_type in my example):

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type;
BEGIN
my_array := array_type();
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Alternatively and according to a better programming practice, you can initialize the collection at declaration time:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

both programs will return the value 0 (zero) in the dbms_output buffer.

So now we have a VARRAY with zero elements, but we declared it to hold up to 10 items.
Let's have a look at how to initialize this collection with a given number of elements.

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type('a','b');
BEGIN
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

In dbms_output you'll find now the number 2 because we initialized the varray with two elements: a and b.

Imagine however that you have a large number of elements, say 32000, clearly you cannot type all of them in the constructor, so, how do you proceed?

If you are tempted to initialize the last element of the collection, see the next posting, so that is not an option.

How do you fully initialize a 32000 elements varray?

Before adding anything else, let me just suggest to ask yourself whether this is really necessary.
If the answer is yes, then read on, otherwise try to implement some other algorithm that doesn't consume db's resources so savagely...
Are you absolutely sure that this tiny program will not end up being used by dozen of concurrent users?

All right, so here comes into play the EXTEND collection method that allows us to initialize the varray by the desired number of null elements:

DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
my_array.EXTEND(32000);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Last note: interestingly enough, the official documentation says that this form of EXTEND cannot be used when you impose a NOT NULL constraint on the array (or table) type, but, at least on Oracle XE, this is not true:

DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200) NOT NULL;
my_array        array_type := array_type();
BEGIN
dbms_output.enable;
my_array.EXTEND(31000);
dbms_output.put_line(my_array.COUNT);
dbms_output.put_line(nvl(my_array(31000),'31000th is null!'));
END;

Indeed if you try to initialize the array using a non empty constructor containing nulls, Oracle will complain at parse time:

DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200) NOT NULL;
my_array        array_type := array_type(null,null);
BEGIN
dbms_output.enable;
my_array.EXTEND(31000);
dbms_output.put_line(my_array.COUNT);
dbms_output.put_line(nvl(my_array(31000),'31000th is null'));
END;

Error report:
ORA-06550: line 3, column 42:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter

So, either i got it wrong or this is a bug...

Last but not least, let's peek at the most sensible and probably useful way of initializing a collection that is by using bulk SQL:

DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200);
my_array        array_type  := array_type();
upper_bound     pls_integer := my_array.LIMIT;
BEGIN
dbms_output.enable;

select 'item_' || n
bulk collect into my_array
from (
select level n
from dual
connect by level <= upper_bound);

dbms_output.put_line(my_array.COUNT);
dbms_output.put_line(nvl(my_array(31000),'31000th is null'));
END;

Please note that i had to explicitly initialize the array because i used LIMIT for retrieving the array upper bound as i don't wanted to hardcode the literal 32000 inside the query, but if you don't use this kind of approach, you can omit the array initialization, it will be performed automatically when BULK COLLECT is performed.

------------------------------------------------
ORA-06531: Riferimento a collection non inizializzata
ORA-06531: Referencia a una recopilación no inicializada
ORA-06531: Referència a recollida no inicialitzada
ORA-06531: Référence à un ensemble non initialisé
ORA-06531: Nicht initialisierte Zusammenstellung referenziert
ORA-06531: Αναφορά σε μη αρχικοποιημένη συλλογή
ORA-06531: Reference til ikke-initialiseret samling
ORA-06531: Referens till ej initierad insamling
ORA-06531: Referanse til uinitialisert samling
ORA-06531: Viittaus alustamattomaan kokoelma
ORA-06531: Inicializálatlan gyűjtőre való hivatkozás
ORA-06531: Referinţă la o colecţie neiniţializată
ORA-06531: Verwijzing naar niet-geïnitialiseerde verzameling.
ORA-06531: Referência para coleta não-inicializada
ORA-06531: Referência a uma recolha não inicializada
ORA-06531: Ссылка на неинициализированный набор
ORA-06531: Odkaz na neinicializovanou skupinu
ORA-06531: Odkaz na neiniciovanú kolekciu
ORA-06531: Odwołanie do nie zainicjowanej kolekcji
ORA-06531: Başlatılmamış koleksiyona başvuru
ORA-06531: Reference to uninitialized collection

See message translations for ORA-06531 and search additional resources