Interesting change in the XML export format of Oracle SQLDeveloper

Byte64 | Jul 7, 2008 05:50 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

In the last days I've been playing with XML files using SQL Developer and other tools and i noticed the following interesting change in the behavior of SQL Developer when it comes to exporting the data from a table in XML format:

until version 1.5.0 build 5338, the elements in the XML

ORA-22289: cannot perform LOADFROMFILE operation on an unopened file or LOB

Byte64 | Jul 3, 2008 06:40 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may see this error when invoking procedure DBMS_LOB.LOADCLOBFROMFILE as follows:
create or replace
FUNCTION LoadTempClobFromFile (
p_Dir IN VARCHAR2,
p_FileName IN VARCHAR2,
p_csid IN INTEGER)
RETURN CLOB IS

l_srcFile BFILE := BFILENAME(p_Dir, p_FileName);
l_tmpClob

ORA-22929: invalid or missing directory name and ORA-06564: object Xyz does not exist

Byte64 | Jul 2, 2008 07:40 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You can see this error when executing a command like:

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY 'IMPORT_DIR';

ORA-22929: invalid or missing directory name
The problem is in the single quotes surrounding the directory object name.
The same error is also returned when using a CREATE

ORA-29400: data cartridge error

Byte64 | Jul 2, 2008 04:40 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

The pair ORA-29913/ORA-29400 is a sort of catch-all exception embedding KUP-XXXXX error codes that further specify the type of problem encountered with the definition of an external table.
The type of errors encountered spans from syntax errors to missing files or privileges.

For

ORA-02315: incorrect number of arguments for default constructorAnnals of Oracle's Improbable Errors

Byte64 | Jun 26, 2008 09:20 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This is a rather trivial error that is raised when you forget to specify some attribute in the list of values that makes up the object constructor:
drop table test_tab cascade constraints;
drop type tab_column_type;
drop type column_type;

create or replace
type column_type as object (

ORA-22913: must specify table name for nested table column or attribute

Byte64 | Jun 25, 2008 10:40 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error is returned when you omit the NESTED TABLE clause from a CREATE TABLE or ALTER TABLE statement and you specified a nested table column, as in the following case:

create or replace
type column_type as object (
col_name varchar2(30),
col_comment varchar2(4000)
);
/

create

Are you ready for Oracle Open World 2008?

Byte64 | Jun 25, 2008 02:20 -0600
I don't know if you have plans to attend Oracle Open World this year (San Francisco, September 21-25), but here is the session i proposed on Oracle Mix, a cool website where everybody dealing with Oracle can share ideas and information.

In short, I'd be glad to show a presentation about analyzing PL/SQL procedures with an Apex application, however this is going to happen only if the proposed

ORA-02303: cannot drop or replace a type with type or table dependents

Byte64 | Jun 24, 2008 08:00 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

In spite of all enhancements that oracle database underwent over the years, when it comes to replacing an existing user defined object type referenced by other objects with a newer version containing different attributes, the developer or the DBA is basically left to his own fate, so he/

ORA-24344: success with compilation error

Byte64 | Jun 20, 2008 15:40 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This in a rather curious error message that i got when i attempted to perform the following operation against a 9iR2 instance (i didn't check if the same happens on newer versions, but it should be the same):
create or replace force view test_view
as select a,b from dual2;

declare

ORA-01775: looping chain of synonyms

Byte64 | Jun 10, 2008 02:00 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-01775: looping chain of synonymsThis error is very similar to ORA-00980, this specific error is returned when the underlying object, bearing the same name as the synonym, is either renamed or dropped. At that point an attempt to resolve the name pointed to by the synonym only returns

ORA-00980: synonym translation is no longer valid

Byte64 | Jun 9, 2008 09:40 -0600
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-00980: synonym translation is no longer validThis error can be seen when the object pointed to by a synonym has been dropped or renamed and the name of the synonym is different from the real object's name.

If the underlying object is restored later using the FLASHBACK TO BEFORE DROP