ORA-00984 column not allowed here

This is one of those messages i love so much because it belongs to the category of those obscure errors that convinced me to embark myself in the oracle quirks saga.

I had to "debug" a procedure and i couldn't use the standard debugging tools, not even the traditional dbms_output.put_line so i was logging the content of some variables into a table for later review.

Initially, i wrote an exception handling block like this:

exception
when others then
insert into error_log (text) values(sqlerrm);
end;

When i attempted to compile the procedure i got the following error:

ORA-06550: line 50, column 41:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 50, column 3:

I was quite sure the name of the column was right, but given the message i went to the table to double check the column name, so, when i saw it was correct, i was puzzled.
I was running late, i had to get this stupid program to work and i was getting weird errors on a trivial statement, gosh!

Then i removed SQLERRM from the insert statement, just to give it a try and it compiled seamlessly.

Bingo.

Frankly speaking, although i've read many manuals and often more than once, i didn't remember of any particular limitation in the usage of SQLERRM. Moreover i was so convinced i've already used the function that way that i found difficult to believe to my eyes.

Nevertheless the limitation is clearly documented in the PL/SQL User's Guide and Reference.
You cannot use SQLERRM inside SQL statements.
If you want to do so, you must first store the value of SQLERRM in a variable.

So, the original code must be changed as follows:

exception
when others then
l_errmsg := SQLERRM;
insert into error_log (text) values(l_errmsg);
end;

And last but not least:
try to look for ORA-00984 in the official error message book for version 10.2 or 10.1.
You won't even find the entry.

It does show up in the 9.2 version and earlier versions with the following explanation:

ORA-00984 column not allowed here

Cause: A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement.

Action: Check the syntax of the statement and use column names only where appropriate.

So, at the end of the day we have to choose between an old documented misleading message and a new nonexisting message!

I love these quirks, but not when i am in a hurry.
:-D

See message translations for ORA-00984 and search additional resources

---

ORA-00984: colonna non consentita in questo caso
ORA-00984: columna no permitida aquí
ORA-00984: aquí la columna no és permesa
ORA-00984: Un nom de colonne n'est pas autorisé ici
ORA-00984: Spalte hier nicht zulässig
ORA-00984: στήλη δεν επιτρέπεται εδώ
ORA-00984: kolonne er ikke tilladt her
ORA-00984: kolumn inte tillåten här
ORA-00984: kolonne er ikke tillatt her
ORA-00984: tässä ei voi olla saraketta
ORA-00984: oszlopnév használata itt nem megengedett
ORA-00984: coloană nepermisă aici
ORA-00984: Kolom is hier niet toegestaan.
ORA-00984: coluna não permitida aqui
ORA-00984: coluna não é aqui permitida
ORA-00984: употребление столбца здесь недопустимо
ORA-00984: sloupec zde není povolen
ORA-00984: stĺpec tu nie je dovolený
ORA-00984: w tym miejscu, kolumna jest niedozwolona
ORA-00984: burada sütun kullanımına izin verilemez

Exporting and importing pages in Apex

If you see the following message:

This page was exported from a different application or
from an application in different workspace.
Page cannot be installed in this application.

while trying to install a single page in Oracle ApEx, then check if the following conditions apply to your case:

1. The workspace where the page was exported from, has got a different ID from the workspace where you want to install the page into.
In this case, you can manually change the ID in the export file, you can easily locate it by looking at the parameter p_security_group_id, contained in one of the first lines of the script, as in this case:

wwv_flow_api.set_security_group_id(p_security_group_id=>23111618468800456);

This is the source workspace ID (the number will be different of course...). You need to replace this number with your target workspace ID. If you don't know what is the target ID, export a page from your target environment and look for it in the file, as we already did above.

2. Your workspace ID is OK (either the workspace ID is the same for both environments or you just changed it as explained earlier) but you are still getting the message.

Check if the target application is currently selected or if the current application (assuming you have more than one in the same workspace) is a different one.
Let's say that in the Application builder environment you have two applications, one is application 12345 and the other one is 67890.
Now, if 12345 is currently selected (which means that you see this number in the upper right corner of the Application Builder environment, you will not be able to import a page for application 67890, even if the workspace ID is the same.

In order to successfully import a page, you need first to switch to application 67890 and reprocess the imported file. Note that you don't need to upload the file again in the repository, unless you deleted it, the file is still there and all you need to do is to click on the "Install" link.


Needless to say, your page components (underlying tables, LOVs, templates and so on) need to be the same in both environments, otherwise the page won't work correctly.



PS: I hereby decline any responsibility for messing up your environment! ;-)

PLS-00315 and PLS-00657

Have you ever tried to create an associative array with index by other than binary_integer?

In Oracle 8i, if i remember well, binary_integer was the only option available.

Starting from 9i, you could define a varchar2 indexed array, making oracle arrays very similar to the associative arrays found in other programming languages.
However there are still some limitations, for instance you cannot create a collection based on a date index and a declaration like the following would return a compilation error:

DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF VARCHAR INDEX BY DATE;
current_year_tab  bank_holiday_tab_type;
BEGIN
...
END;
PLS-00315 Implementation restriction:
unsupported table index type.

But not all is lost.
An index-by-date array could be easily simulated by converting the date value into a proper varchar2 type value using function TO_CHAR with the desired date format mask.

The drawback when using such associative arrays, is in that you cannot use BULK COLLECT or FORALL constructs, because they work only with numerical integer indices.

In case you attempt to use one of such statements, you'll get an exception at compile-time like the following:

PLS-00657: Implementation restriction:
bulk SQL with associative arrays with
VARCHAR2 key is not supported.

The reason is quite simple, it's impossible for Oracle to predict what the next VARCHAR2 subscript should be, whereas in the case of a binary integer subscript it is just automatically filling up the array with consecutive numbers, starting from 1.

In other words a normal array is something like:
A(1), A(2),... A(n)

Where the value of each element can be any of any type you like, including a user defined object type.

On the contrary an associative array is something like:

A(string₁), A(string₂),... A(string_n)

If you look at the syntax of BULK COLLECT, you can see that there is no way to specify a mapping between the value being returned from the table and its corresponding index.

For instance, let's take my original array definition and transform it into a varchar2(8) indexed array.
Suppose we need to fill up the aforementioned array of dates, where each value is a string indicating the bank holiday name.
There is no way of doing this with BULK COLLECT.

CREATE TABLE bank_holidays(
 bank_holiday DATE,
 bank_holiday_name VARCHAR2(50));

DECLARE
TYPE bank_holiday_tab_type IS
  TABLE OF VARCHAR2(50)
  INDEX BY VARCHAR2(8);
current_year_tab  bank_holiday_tab_type;
BEGIN
SELECT TO_CHAR(bank_holiday,'YYYYMMDD'),
     bank_holiday_name
BULK COLLECT INTO current_year_tab
FROM dates_of_year;
...
END;

As you see we would need to stuff a pair of table columns (the subscript and its value) as a single element of the array, but BULK COLLECT is not designed to do that.

For the sake of discussion, let's suppose it makes some sense to get rid of the column bank_holiday_name from the last sql statement above, just to see what happens when we try to compile this program in 10g.

Well, all we get is:

PLS-00657: Implementation restriction:
bulk SQL with associative arrays with
VARCHAR2 key is not supported

So, while we wait until oracle comes up with a statement syntax extension like:

SELECT BULK_PAIR(TO_CHAR(bank_holiday,'YYYYMMDD'), bank_holiday_name)
BULK COLLECT INTO current_year_tab
FROM bank_holidays;

we can stick to a classic and slower explicit cursor doing the job:

DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(8);
current_year_tab  bank_holiday_tab_type;
BEGIN
FOR each_record IN (
SELECT TO_CHAR(bank_holiday,'YYYYMMDD') bank_holiday$,
   bank_holiday_name
FROM bank_holidays)
LOOP
current_year_tab(each_record.bank_holiday$)
:= each_record.bank_holiday_name;
END LOOP;
END;

or perhaps define a custom type like this:

CREATE TYPE bank_holiday_type AS
OBJECT (bank_holiday DATE, bank_holiday_name VARCHAR2(50));

and then use BULK COLLECT on an array of bank_holiday_type, as follows:

DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF bank_holiday_type INDEX BY BINARY_INTEGER;
current_year_tab   bank_holiday_tab_type;
BEGIN

SELECT
bank_holiday_type(
  TRUNC(bank_holiday),
        bank_holiday_name)
BULK COLLECT INTO current_year_tab
FROM bank_holidays;
END;

Happy collecting.