No code changed, but now ORA-01841. Mistery Solved

A report that is coded in PL/SQL that has existed for years "all of sudden" started generating a "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" error. Why and why all of sudden?

I discovered that a data problem has existed for years. When reports run they save results to a shared database table. Later on in the reports, the reports delete some of the data that does not meet a given time period. The table has both YEAR and MONTH columns which are both numeric along with a unique primary key value generated from a sequence. You guessed it, the YEAR column for a different type of report for some reason had a zero in it. A DELETE statement in the report assembles a date value from the YEAR and MONTH values. With a value of zero, this caused the error.

What made it worse is I would then rerun the report and it would run through fine with no errors giving the correct expected values. What was happening behind the scenes was the report with the bad data had finished and deleted all its zero year rows out of the table by the time I reran the report. So the rerun went through fine with no errors.

That explained why the error was occurring, but not why it had never appeared before. The answer to this is our friend the Cost Based Optimizer (CBO). I had computed new statistics on the report table. With the prior statistics the CBO was applying a primary key WHERE clause criteria before assembling the date value from the YEAR and MONTH columns. So the report never hit the "zero year" rows from the other reports running at the same time. When I recomputed statistics on the report table it caused the CBO to change it access path and assemble the date value before applying the primary key restriction causing the Oracle error to appear. The old CBO statistics were masking the fact that there was a data error in the table i.e. a year value of 0.

ODTUG 2010

Is this correct? I found a link with a session schedule for the ODTUG Kaleidoscope 2010 conference in Washington, DC June 27 to July 1, 2010. Is this accurate? I do not see anything on the ODTUG web page.


http://www.technicalconferencesolutions.com/pls/caat/caat_abstract_reports.schedule?conference_id=68

NULLs in subqueries.

I ran into a basic query today that perplexed me. I wanted to list all the values in one table (TABLE_A) that were not in another table (TABLE_B).

SELECT value
FROM table_a a
WHERE a.value NOT IN (SELECT DISTINCT b.value
FROM table_b b);

TABLE_A had the value '82' in it. TABLE_B did not have '82'. The query listed no rows. It should have listed '82' right? So thinking I was wrong and TABLE_B did have '82' in it, I tried:

SELECT value
FROM table_a a
WHERE a.value IN (SELECT DISTINCT b.value
FROM table_b b);

and it did not list '82'. '82' is in TABLE_A and it is either 'IN' or 'NOT IN' TABLE_B. Why doesn't either query list '82'? So I rewrote the query to:

SELECT *
FROM table_a a
WHERE NOT EXISTS (SELECT *
FROM table_b b
WHERE a.value = b.value);

and '82' was listed. So I looked at the EXPLAIN PLANs for both queries and noticed that the 'NOT IN' query was using the LNNVL function. So I looked at the rows being returned by my subquery. One of the rows had a NULL value. The LNNVL was making the '82' equal to the NULL value causing it to not list. So I changed my query to:

SELECT value
FROM table_a a
WHERE a.value NOT IN (SELECT DISTINCT b.value
FROM table_b b
WHERE b.value IS NOT NULL);

That fixed it. The moral of the story is, 'Always be aware of NULL values'.

Reserved Words as Oracle Column Names

I saw a post on the web asking if one could create an Oracle table in which a column name was an Oracle reserved word like "TO" or "FROM". Now, not that I would do this, but it intrigued me, so I tried:

SQL> create table bogus (to varchar2(2));
create table bogus (to varchar2(2))
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> CREATE TABLE bogus AS
2 SELECT dummy "TO", dummy "FROM" FROM DUAL;

Table created.

SQL> desc bogus
Name Null? Type
----- ----- --------------------------
TO VARCHAR2(1)
FROM VARCHAR2(1)

SQL> select * from bogus;

TO FROM
-- ----
X X

SQL>

I started up SQL Developer to see what the CREATE TABLE statement would be for the BOGUS table:

CREATE TABLE "BOGUS"
("TO" VARCHAR2(1),
"FROM" VARCHAR2(1));

To select from the BOGUS table:

SQL> select "TO", "FROM" from bogus;

TO FROM
-- ----
X X

SQL>

There it is.

APEX SQL Workshop Insufficient Privileges Error

An APEX user was attempting to create a table in the SQL Workshop and received the "ORA-01031 insufficient privileges" error message. I went into SQL Developer and was able to create the table. So why did it not work in APEX SQL Workshop?

After some "data dictionary" digging comparing this schema to one that works, I realized that the CREATE TABLE privilege was granted to the schema via a role and not a direct grant. Since APEX runs within PL/SQL you have to have the CREATE TABLE grant issued directly to the schema to be able to create a table in APEX. You cannot grant the privilege via a database role.

I just thought that I would post this in case someone else runs into this problem and so I will have the answer when the problem comes up again.

Oracle Reserved word as a Table name

I ran into a table today that was named GROUP, so I attempted to do a

SELECT * FROM GROUP;

Since GROUP is a reserved word in Oracle, I got an "ORA-00903: invalid table name" error. So how do I query the table? I went into SQL Developer and was able to view the data in the table via the "Data" tab. So how was that possible? It ended up SQL Developer was doing a

SELECT * FROM "GROUP";

I knew that SQL Developer placed double quotes around the schema, table, and column names in the CREATE TABLE statements in the "SQL" tab because those names could be mixed case. But the double quotes also allow those names to be reserved words as well.

NOTE: In my humble opinion naming tables or columns a reserved word is not a good thing to do.

ORDER BY Clause in "Filter" field In SQL Developer

I was in SQL Developer today and discovered something by accident. I double clicked on a table name in the "Connections" tab and went to the "Data" tab to view rows of data. In the "Filter:" field I entered a WHERE clause but accidentally also included an ORDER BY clause, too. The rows of data displayed in the order I requested. This got me to thinking. The "Sort" button provides a basic column by column sorting order, but if you need more complicated sorting logic you could just type it into the "Filter:" field.

This is useful if you want to edit a set of rows in a particular order. For example in our friend the EMP table, we could list all the rows where their salary is greater than 2000 listing the president first, analysts second, and all others third: