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:

"When All Else Fails..."

This is on a shirt that I received as a present at work today.