ORA-01882: timezone region not found

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

This is one of those funny messages where the lucky user is invited to contact Oracle support.
I don't know exactly how many calls Oracle is receiving reporting this, but in most cases it doesn't really make sense.
Anyway, here is an example, run on Oracle XE (10.2):

SELECT to_timestamp_tz('Fri, 28 Aug 2009 10:16:58 PDT',
'Dy, DD Mon YYYY HH24:MI:SS TZR') AT LOCAL as t
FROM DUAL;

ORA-01882: timezone region  not found

*Cause:    The specified region name was not found.
*Action:   Please contact Oracle Customer Support.

Now, as per oracle documentation PDT is not a time zone region, it's a daylight savings specifier.
Unfortunately you may easily get data in this format from an external data provider.
The problem is in that until you play inside the boundaries of your company, you can "easily" amend the input data, but when the data comes from outside is not so easy to convince the others that they are wrong, in the end they might be working with other partners who do not complain about the problem you report, so why bother?

To add more fun to the situation, consider the following query, bearing in mind that Oracle accepts TZD as timestamp format mask element to deal with daylight savings specifiers:

SELECT to_timestamp_tz('Fri, 28 Aug 2009 10:16:58 PDT',
'Dy, DD Mon YYYY HH24:MI:SS TZD') AT LOCAL as t
FROM DUAL;

T
--------------------------------------
28-AUG-09 10:16:58.000000000 AM +02:00

As soon as i removed the time zone region specifier, the query runs normally.
Now, i am no expert of time zones, but the result of this query doesn't look very good, even if i am open for discussion on this aspect.
I specified AT LOCAL, meaning to convert the timestamp value into the local session time zone, that is "+02:00" (use functions CURRENT_TIMESTAMP to retrieve the current time in the local time zone or SESSIONTIMEZONE to retrieve the time zone value only).

As i am in central europe time zone, I'd rather expect to get something like:

T
--------------------------------------
28-AUG-09 07:16:58.000000000 PM +02:00

If you remove AT LOCAL from the query, you get the same result.
I argue this is a consistent result.
Clearly one may object that since i (the others...) didn't specify the corresponding time zone region, the daylight savings specifier was ignored or interpreted in the wrong way.

According to the documentation TZD should always come with TZR and now i understand why:
there is a risk of misinterpreting the source time if i convert it into the local time zone.

In conclusion, if you are dealing with timestamps generated from external data sources, watch out for the correct time zone region format because PDT, for Oracle, doesn't seem to be a valid value.

May the root cause of the problem reside in a view called V$TIMEZONE_NAMES?

This view contains both the time zone name and its abbreviation.
Whereas time zone region PST (TZNAME column in the view) has got a corresponding abbreviation (TZABBREV) with the same value, PDT shows up only as an abbreviation of multiple time zone regions, without a corresponding value, indeed you may see it associated to PST for instance.

So, how did i work around the problem?
I try replacing PDT with PST PDT whenever i encounter ORA-01882.
Horrible practice, but i could not find a better one.

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

ORA-12008: error in materialized view refresh path

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

You may incur in this exception when attempting to refresh a materialized view using procedure DBMS_VIEW.REFRESH, but i guess that also autorefreshing snapshots may suffer the same problem, although i could not verify this. ORA-12008 is a catch-all exception that is followed in the error stack by the real error message that was raised during the execution of the underlying query, in the following example ORA-01476.

begin
dbms_mview.refresh('VIEW_NAME','C');
end;

ORA-12008: error in materialized view refresh path
ORA-01476: divisor is equal to zero

Typically materialized views are not trivial queries so, in order to find out the exact spot that is giving troubles, you might have to run the query interactively. In my case the problem must be caused by a division expression, so i identified all the expressions containing a division and then i selectively commented them out, until i found the "culprit".
Thereafter i had to understand why an expression that is never zero became zero, but this was an entirely different exercise.

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

ORA-04044: procedure, function, package, or type is not allowed here

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

This exception occurs when you specify a custom function (or packaged function as shown below) as default value for a table column.

create table test4044 (
code varchar2(10) default dbms_random.string('x',10));

Error at Command Line:2 Column:26
Error report:
SQL Error: ORA-04044: procedure, function, package, or type is not allowed here

The workaround is to create a BEFORE INSERT trigger:

create or replace
TRIGGER BI_test4044
before insert on test4044
for each row
begin
:new.code := dbms_random.string('x',10));
end;

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

ORA-39094: Parallel execution not supported in this database edition

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

This morning i was trying out a demo of DBMS_DATAPUMP found at psoug.org and initially i wasn't getting the expected file dump, so i slightly modified the procedure in order to understand where it was stopping. The problem with this nice demo is in that it must have been written for Oracle 10g Enterprise Edition (i was testing this on a 10gR2 SE) because, after a couple of misses, i eventually isolated the failing statement:

DBMS_DATAPUMP.SET_PARALLEL(dph, 2);

After surrounding this statement with an error trapping block:

 ...
 -- set parallelism
 begin
   dbms_datapump.set_parallel(dph, 2);
 exception
 when xcp_39002 then null;
 end;
 ...

i could successfully complete the demo and here is what i found at beginning of the DBMS_OUTPUT message trail:

ORA-39002: invalid operation
ORA-39094: Parallel execution not supported in this database edition.

This is expected behavior as per the official documentation.

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

ORA-22908: reference to NULL table value

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

This exception is raised when an attempt of updating the rows of an atomically null nested table column is made.
For instance:

create type complex_number as (real_part number, imaginary_part number);

create or replace type complex_tab as table of complex_number;

create table complex_sets (
set_id     number,
set_name   varchar2(50),
set_values complex_tab)
nested table set_values store as nested_complex_set_values;

insert into COMPLEX_SETS (SET_ID,SET_NAME,SET_VALUES)
values (1,'first set',null);

update table(select set_values
             from complex_sets
             where set_id = 1)
 set real_part = real_part + 1
where real_part = 0;

ORA-22908: reference to NULL table value

The nested table underlying the column set_values for row having set_id = 1 has not been initialized, in other words it is said to be atomically null.
Let's repeat the last steps in order to initialize the nested table, ORA-22908 will disappear:

delete from complex_sets where set_id = 1;

insert into COMPLEX_SETS (SET_ID,SET_NAME,SET_VALUES)
values (1,'first set',complex_tab(complex_number(0,0), complex_number(0,1)));

update table(select set_values
              from complex_sets
              where set_id = 1)
  set real_part = real_part + 1
where real_part = 0;
--

2 rows updated

select a.set_id, rownum, b.real_part, b.imaginary_part
 from complex_sets a, table(a.set_values) b
where set_id = 1;

SET_ID   ROWNUM   REAL_PART   IMAGINARY_PART
------   ------   ---------   --------------
1        1        1           0
1        2        1           1

I initialized the nested table with two rows, each containing a complex_number (user-defined) type, note however that it is possible to initialize the nested table to an empty set by specifying the nested table type constructor:

delete from complex_sets where set_id = 1;

insert into COMPLEX_SETS (SET_ID,SET_NAME,SET_VALUES)
values (1,'first set',complex_tab());

update table(select set_values
              from complex_sets
              where set_id = 1)
  set real_part = real_part + 1
where real_part = 0;

--
0 rows updated

Whether it is preferable to have atomically null nested tables or nested tables initialized to an empty set depends on your application. If most nested table columns are going to remain null, then it can make sense to keep them atomically null, otherwise you might consider initializing the nested table to an empty set either using the default clause for the column or inside a before insert trigger or handle ORA-22908 at the application code level, depending on performance considerations.

-- initialize the nested table to an empty set using the DEFAULT column clause

drop table complex_sets cascade constraints;

create table complex_sets (
 set_id     number,
 set_name   varchar2(50),
 set_values complex_tab default complex_tab())
 nested table set_values store as nested_complex_set_values;

insert into COMPLEX_SETS (SET_ID,SET_NAME)
values (1,'first set');

update table(select set_values
              from complex_sets
              where set_id = 1)
  set real_part = real_part + 1
where real_part = 0;
--

0 rows updated

-- handling ORA-22908 inside the application
declare
 l_set  number := 1;
 null_table exception;
 pragma exception_init(null_table, -22908);
begin
 update table(select set_values
              from complex_sets
              where set_id = l_set)
    set real_part = real_part + 1
 where real_part = 0;
exception
when null_table then
 update complex_sets
    set set_values = complex_tab()
  where set_id = l_set;
end;
/

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

ORA-01741: illegal zero-length identifier

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

This is another curious error message that i presume is rare to be seen twice in your life.

alter session set tracefile_identifier="";

ORA-01741: illegal zero-length identifier

Oracle accepts the double quotes as string delimiter, but not when there is nothing in between.
In fact it is perfectly possible to execute:

alter session set tracefile_identifier="xyz";

select value
from v$parameter
where name = 'tracefile_identifier';

VALUE
-----
xyz

In order to set the session parameter to a null value, single quotes must be used:

alter session set tracefile_identifier='';

Note also that ALTER SESSION, unlike ALTER SYSTEM, doesn't come with an ALTER...RESET... clause.

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

ORA-06553: PLS-221: 'name' is not a procedure or is undefined

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

This type of parsing error is quickly explained:

select dbms_db_version.version from dual;

SQL Error: ORA-06553: PLS-221: 'VERSION' is not a procedure or is undefined

In the example above dbms_db_version.version is not a packaged function, but a packaged constant.
Packaged constants can be used inside PL/SQL procedures but not inside ordinary SQL statements, however you can specify such constants in SQL DML statements inside programs:

declare
  v number;
begin
  select dbms_db_version.version into v
    from dual;
  dbms_output.put_line(v);
end;
/

If you need to specify constants inside DML statements such as SELECT/DELETE/INSERT/UPDATE statements, then you can create wrapping functions as follows:

create or replace function db_version
return integer deterministic
is
begin
return dbms_db_version.version;
end;

select db_version from dual;

DB_VERSION
-------------
10

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

ORA-31181: PL/SQL DOM handle accesses node that is no longer available

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

This morning I'm going for an easy one.

...
 l_doc      := DBMS_XMLDOM.newDomDocument(cl => l_clob);
 l_rootElem := DBMS_XMLDOM.getDocumentElement(doc => l_doc);
 DBMS_XMLDOM.freeDocument(l_doc);
 l_rootNode := DBMS_XMLDOM.makeNode(elem => l_rootElem);  -- this is line 436


 DBMS_XMLDOM.freeNode(l_rootNode);
...
end;
/
ORA-31181: PL/SQL DOM handle accesses node that is no longer available
ORA-06512: at "XDB.DBMS_XMLDOM", line 2554
ORA-06512: at "XDB.DBMS_XMLDOM", line 2572
ORA-06512: at "TAMEME.TAMEME", line 436
ORA-06512: at line 6

This error is easily explained: you cannot free the object containing the DOM document until you have finished processing all the objects derived from it.
In the code snippet above, procedure DBMS_XMLDOM.freeDocument must be moved after DBMS_XMLDOM.freeNode.
In other words, the call to DBMS_XMLDOM.makeNode is out of scope.

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

ORA-27040: file create error, unable to create file

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

It's not the first time i write about the stack of errors returned by data pump operations and ORA-31641 in particular, however the error messages may vary slightly with the operating system. For instance the following error stack is returned on XE running on top of Ubuntu, whereas the previously linked page refers to an error condition occurring on XE for Windows.

expdp user/pwd directory=EXPDIR dumpfile=dump.dp tables=emp,dept nologfile=Y

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/flavio/documents/dump.dp"
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

The problem is caused by the missing privileges of user oracle on the directory pointed to by EXPDIR.
EXPDIR is pointing to /home/flavio/documents.

ls -ld /home/flavio/documents

drwxr-xr-x 4 flavio flavio 4096 2009-04-10 19:00 /home/flavio/documents/

As you see the directory belongs to user flavio, group flavio, moreover no write privileges are granted other than to the directory owner. Now, as Oracle processes are running under the user oracle, in order to be able to dump a file in this directory we have two possibilities:

1. to change the other group permissions on the directory i.e. by executing chmod o+w documents.
   
2. to add user oracle to group flavio and grant write permissions on documents to group flavio by executing sudo usermod -a -G flavio oracle; chmod g+w documents.

Note that option #1 will work instantly while option #2 requires a db restart. If you do not restart the db, you will still get the permission denied error. In either case, the db user must have READ and WRITE permissions on EXPDIR or hold CREATE ANY DIRECTORY privilege.

Whether the optimal solution is either of the two options above it really depends on the application security requirements.
In the end may be it's better to create a public directory where everybody can dump files freely or create a shared directory EXPDIR where only oracle can create files and users belonging to a datapump group can read them. This approach avoids tampering with the existing permissions on user's private folders and it doesn't require a db restart.

Final notes: while looking for the Optimal Flexible Architecture document, i realized that dpdump is nowhere to be found in the installation documents through version 11gR1. This directory is created at time of installation and it is an OFA directory, no matter what the books say or don't. Directory object DATA_PUMP_DIR, which is the default directory for data pump operations, indeed maps to dpdump.
If i remember correctly in 10gR2 the directory object is automatically created at installation time whilst in 10gR1 you need to create it manually.

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

ORA-01791: not a SELECTed expression

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

You may get the following error when you combine DISTINCT and ORDER BY clauses in a query as follows:

select distinct
agent,
application_id
from apex_workspace_activity_log
order by view_date;

ORA-01791: not a SELECTed expression

The problem is caused by the presence of column view_date in the ORDER BY clause that is the expression mentioned in the error message. The term expression is probably used because one can specify expressions in the ORDER BY, not just simple columns.

The query indeed will run without problems if the ORDER BY clause contains SELECTed columns or expressions:

select distinct
agent,
application_id
from apex_workspace_activity_log
order by application_id;

or

select distinct
agent,
application_id,
trunc(view_date)
from apex_workspace_activity_log
order by application_id, trunc(view_date);

Note that it's also possible to specify expressions based on the SELECTed columns that are not explicitly calculated in the SELECT list:

select distinct
agent,
application_id,
application_name,
trunc(view_date)
from apex_workspace_activity_log
order by upper(application_name), application_id, trunc(view_date);

But in the end, is either of these alternate queries returning an acceptable result as far as your application is concerned?
How can i turn the original (illegal) query into an acceptable form?

Over the years I've seen people using the DISTINCT clause quite arbitrarily and most of them had an MS-Access background...
Don't ask me if the two things were correlated ;-)
It looks like they just want to be sure that there are no duplicates in the resulting recordset, so they add DISTINCT even if they don't really need to do that. If that is the case, then the solution is easy, just get rid of this keyword.
However the query above seems to be an illegal representation of a legitimate (albeit strange) requirement:
i'd like to retrieve unique pairs of application IDs and user agents in order of appearance in the log.
If this is the correct interpretation, then DISTINCT is useless and GROUP BY is the answer.

select agent, application_id
from (select
       agent,
       application_id,
       min(view_date) d
       from apex_workspace_activity_log
       group by agent, application_id)
order by d;

In conclusion I'd say that ORA-01791 is either the symptom of a trivial error or the ringing bell of an ill-designed query.

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

ORA-22922: nonexistent LOB value

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

As mentioned in a previous posting about ORA-22275, a temporary LOB can be turned into a persistent LOB by moving it into a table, however one thing is persistence and a different thing is LOB consistency.

Funny things happen when one gets fancy in the use of SAVEPOINT/ROLLBACK in conjunction with LOBs.

The following PL/SQL block represents a simple scenario of an ill-designed procedure that is rolling back under a certain condition thus invalidating the LOB pointer.

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
  rollback;
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at "SYS.DBMS_LOB", line 560
ORA-06512: at line 19

The procedure can be modified as follows:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
 savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
  rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

This time no ORA-22922 will be raised and apparently we managed to transform a temporary LOB locator into a persistent one without inserting any rows, because the insert statement was rolled back.

Interestingly enough on my Oracle XE instance, if i further modify the program as follows, i'm eventually getting ORA-00600:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
 insert into files(document) values(l_clob);
 commit;
end;
/

ORA-00600: internal error code, arguments: [7999], [300], [], [], [], [], [], []
ORA-06512: at line 21

If i insert a COMMIT statement before the final INSERT, we're back to the initial situation:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
 rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
 commit;
insert into files(document) values(l_clob);
commit;
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at line 22

In conclusion ORA-22922 is a symptom of a program that is incorrectly handling LOB locators across transactions.

For a complete review of weird effects achievable by tampering with LOB locators and partial LOB manipulation procedures, see the linked paragraph in the Oracle Application Developer's Guide for Large Objects - Advanced design considerations chapter.

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

ORA-22275: invalid LOB locator specified

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

You may get ORA-22275 in the following situation:

declare
l_clob clob;
begin
l_clob := dbms_xmlgen.getxml('select * from user_tables');
 dbms_lob.freetemporary(l_clob);
insert into files(document) values(l_clob);
end;
/

ORA-22275: invalid LOB locator specified
ORA-06512: at line 6

The problem here is in the premature call of procedure DBMS_LOB.FREETEMPORARY, although the error is raised in the subsequent line.
This particular instance of DBMS_XMLGEN.GETXML is returning a temporary LOB that must be freed after use, therefore DBMS_LOB.FREETEMPORARY must be called after moving the LOB object into its "final" position inside the table.
When you insert a temporary LOB into a table, it becomes persistent.
See also see entry for another occurrence of ORA-22275.

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

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

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

There are oracle errors that can be easily explained.
Sometimes one of these trivial error messages pops up in an unexpected situation and it's really tough to get to the bottom of it.
And that is exactly what happened yesterday, but i am still doing some tests today.

Let's see first the simplest form of ORA-01841:

select to_date('0000-03-10','YYYY-MM-DD') as d
from dual;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Gregorian calendar starts with year 1 and there is very little to say about it when you get this error in a query: just fix the input value.

When the input value comes from an external source and it contains all zeroes, something that I've seen happening quite often when you receive a file from a third party meaning a NULL date value, then you'll have to deal with it using one of those special init_spec clauses like NULLIF or DEFAULTIF (both in SQL*Loader and external tables using the SQL*Loader driver).

However there are less common situations like that i came across today, where i found this message in the log of the SQL*Loader driver invoked by an external table definition in a program that was running since months.

 LOG file opened at 03/10/09 11:13:10

Field Definitions for table IMP_BAD_BOXES
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Reject rows with all null fields

 Fields in Data Source:

   CREATED                         CHAR (14)
     Date datatype DATE, date mask YYYYMMDDHH24MISS
     Record position (1, 14)
     Trim whitespace same as SQL Loader
   BOX_ID                          CHAR (8)
     Record position (15, 22)
     Trim whitespace same as SQL Loader
   DEPT                            CHAR (2)
     Record position (23, 24)
     Trim whitespace same as SQL Loader
error processing column CREATED in row 5 for datafile BOXES.dat
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Date Cache Statistics for table IMP_BAD_BOXES
 Max Size:      1000
 Entries :         4
 Hits    :         0
 Misses  :         0

A batch script invoked by a Windows service running as System was failing to load a file whereas the same script run manually as Administrator was running just fine and the only difference between the two log files was in the aforementioned error on the last imported line of the file.

The only known difference with the past is in that the input file is now generated by an export procedure whereas it was previously exported manually from an excel spreadsheet.
Practically speaking the old files did not contain the trailing newline at the bottom.

Given the following external table definition:

CREATE TABLE "IMP_BOXES"
( "BOX_ID" NUMBER(8,0),
 "DEPT" VARCHAR2(2 BYTE),
 "CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
(
CREATED POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
BOX_ID  POSITION(15:22) CHAR,
DEPT    POSITION(23:24) CHAR
)
   )
LOCATION
( "IMPORT_DIR":'BOXES.dat'
)
);

a source file of 4 records containing a newline (0D0A) in the last record position would cause ORA-01841 to be raised on the 5th record when launched automatically from the Windows service. The same source file did not cause any error when executed as a user with administrator rights.
This problem was reproduced on two different 10GR2 instances running on top of Windows 2003 SE, so, at least, the good news is this is a consistent pattern.
Oracle NLS session parameters were looking the same (i spooled NLS_SESSION_PARAMETERS before invoking the script), so i'd rule them out as a possible cause, i wonder if the system user is reading a different parameter set from the registry.
Note that adding any combination of MISSING FIELD VALUES ARE NULL and/or REJECT ROWS WITH ALL NULL FIELDS did not affect the result.

So, in the end, i don't really know yet what's going on.

I failed to find the exact reason (or oracle bug?) but at least i could find a workaround, consisting in changing the external table definition as follows (inserted clause in green color):

CREATE TABLE "IMP_BOXES"
( "BOX_ID" NUMBER(8,0),
 "DEPT" VARCHAR2(2 BYTE),
 "CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
LOAD WHEN((1:1) != NULLS)
FIELDS
(
CREATED POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
BOX_ID  POSITION(15:22) CHAR,
DEPT    POSITION(23:24) CHAR
)
   )
LOCATION
( "IMPORT_DIR":'BOXES.dat'
)
);

The advantage of checking a character range using the syntax (n:m) is in that i can discard the record before the parser fails to read the last record.
While i was looking for a solution i also incidentally found out that all Oracle Utilities manual through version 11G, where they talk about the so-called condition specifier, are wrong when they say that you can use the keyword NULL in the comparison.
The correct keyword is NULLS.

Another interesting finding is in that the .bad file generated by Oracle contained a weird 1A character preceding the rejected newline character, a character that was nowhere to be found in the source file. This file is only generated when the script is launched from the service.

Curious, isn't it?

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

ORA-06553: PLS-382: expression is of wrong type

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

If you are a skilled developer, feel free to skip reading this entry aimed to Oracle newbies ;-)

SELECT apex_util.is_username_unique('BOB')
FROM dual;

SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Packaged function APEX_UTIL.IS_USERNAME_UNIQUE returns a BOOLEAN type value.
Booleans are not allowed inside SQL expressions, they are only allowed within PL/SQL expressions.

If you need to call a function returning a boolean value inside a SQL query, you'll need to create a wrapper function returning either a number or a character value.
The following sample function wraps APEX_UTIL.IS_USERNAME_UNIQUE returning a single byte character value (either Y or N, corresponding to TRUE and FALSE respectively):

CREATE OR REPLACE
FUNCTION IS_USERNAME_UNIQUE_YN(
p_username IN VARCHAR2
) RETURN CHAR
AS
BEGIN
RETURN CASE WHEN APEX_UTIL.IS_USERNAME_UNIQUE(p_username)
THEN 'Y'
ELSE 'N' END;
END;
/

SELECT is_username_unique_yn('BOB') as "UNIQUE"
FROM dual;

UNIQUE
------
N

Note also that booleans parameter passing is forbidden even in nested functions inside SQL queries, so, imagine to have two functions, one returning a boolean (boolean_fn) value and another one accepting a boolean value (boolean2yn):

create or replace
function boolean_fn
return boolean
is
begin
 return true;
end;
/

create or replace
function boolean2yn(p in boolean)
return char
is
begin
 return case when p then 'Y' else 'N' end;
end;
/

select boolean2yn(boolean_fn) from dual;

SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Oracle Application Express allows the developer to choose from SQL expressions or PL/SQL expressions when writing validation rules or computations. This flexibility is a time saver because there is no need to create such wrapper functions in most cases.
You must use SQL expressions when you reference functions like NVL2 and DECODE. PL/SQL expressions are mandatory when boolean return values or boolean input parameters are involved.

See message translations for ORA-06553 and PLS-00382 and search additional resources.

ORA-31600: invalid input value 200000001 for parameter HANDLE in function FETCH_CLOB

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

Further to my last posting about DBMS_METADATA related ORA-31607, here is a slightly modified version of an Oracle supplied sample of procedure performing the "dump" of all the objects belonging to the current user. In red color, i highlighted the cause of ORA-31600.
The original program used a table to store all the CLOBs, but i commented out the relevant lines and i merely print out the initial 255 characters of the DDL along with a counter.

PROCEDURE get_schema_md IS

-- Define local variables.
h       NUMBER;         -- handle returned by OPEN
th      NUMBER;         -- handle returned by ADD_TRANSFORM
doc     CLOB;           -- metadata is returned in a CLOB
objs    INTEGER := 0;   -- object counter
BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA', user);

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

-- Fetch the objects.
LOOP
                            -- the correct handle is h, not th
doc := DBMS_METADATA.FETCH_CLOB(th, FALSE, DBMS_LOB.CALL);

-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
objs := objs + 1;
-- Store the metadata in the table.
-- INSERT INTO my_metadata(md) VALUES (doc);
-- COMMIT;
dbms_output.put_line(dbms_lob.substr(doc,255,1));
dbms_lob.freetemporary(doc);

END LOOP;

-- Release resources.
DBMS_METADATA.CLOSE(h);
dbms_output.put_line(objs);
END;

ORA-31600: invalid input value 200000001 for parameter HANDLE in function FETCH_CLOB
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 555
ORA-06512: at "SYS.DBMS_METADATA", line 3801
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "TOOLS.GET_SCHEMA_MD", line 21

This is a rather trivial error, it is sufficient to replace the wrong handle name (th), with the correct one (h) to get the program to work. Each of these opaque handlers returned by the DBMS_METADATA functions serves as input to other procedures or functions and this assortment of variables can lead to some confusion, i.e. the exception illustrated in this posting.
The scheme below summarizes the pattern of usage of these handlers, where the number returned by the function on the left hand side can be used as input for the function on the right hand side:

OPEN -> SET_FILTER
    -> SET_COUNT
    -> SET_PARSE_ITEM
    -> ADD_TRANSFORM
    -> FETCH_xxx
    -> GET_QUERY
    -> CLOSE

OPENW -> CONVERT
     -> PUT
     -> SET_PARSE_ITEM
     -> ADD_TRANSFORM
     -> CLOSE

ADD_TRANSFORM -> SET_TRANSFORM_PARAM
             -> SET_REMAP_PARAM

Note also that if you comment out the line in green color, you'll get an XML formatted output instead of plain text DDLs.

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

ORA-31607: function FETCH_DDL is inconsistent with transform

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

If you wonder why you are getting the following error message attempting to use DBMS_METADATA.FETCH_DDL:

ORA-31607: function FETCH_DDL is inconsistent with transform.

then you might want to ensure that your program contains the line in green color call prior to calling FETCH_DDL.

declare
...
begin
exp_handle := DBMS_METADATA.OPEN('SCHEMA_EXPORT','COMPATIBLE', 'ORACLE');
trans_handle := DBMS_METADATA.ADD_TRANSFORM(exp_handle, 'DDL');
DBMS_METADATA.SET_COUNT(exp_handle, 1);
loop
  ddls.DELETE;
  ddls := DBMS_METADATA.FETCH_DDL(exp_handle);
...
end;

Interestingly enough, after fixing the problem above, i found out that DBMS_METADATA.FETCH_DDL function is almost useless when SCHEMA_EXPORT is specified because i can get only a limited subset of the total objects in the schema.
Changing the number of retrieved objects with SET_COUNT or forcing certain object types with SET_FILTER seems to be irrelevant.
I ignore if this problem affects only Oracle XE and/or earlier/subsequent releases of Oracle.

The problem however can be avoided turning to DBMS_METADATA.FETCH_CLOB function and processing one object at a time. This approach seems to be encouraged by the official documentation.

Am i doing something wrong when processing the results?
Or is FETCH_DDL plain buggy?

I hope to be able to answer these questions one day or another.

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

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.