Oracle Openworld 2011 Schedule Set

Yes!! I finally get to go to an Oracle Openworld. I have been to many IOUG users weeks before and after the IOUG conference split from Oracle in 1995 along with a couple ODTUG conferences, but not to an Oracle Openworld conferences out in San Francisco.

I got my schedule all set. Here is the process I went through to get it my schedule loaded on my phone via my Google calendar. (I know there is an Oracle Openworld application, but I have an old Windows Mobile phone and the Android port for my phone eats my battery and I have no cellular data service...)

1. In the Openworld schedule content catalog I went through and populated my interest list.
2. I copied (cut and paste) the 69 events to my PC in Excel (date, time, location and event title).
3. In the Openworld schedule builder I selected my first choices for each time slot. I have 36 first choices. I prefixed my primary event names with "1)".
   
4. Next I selected 33 secondary choices left on my interest list for each time slot. I prefixed my secondary event names with "2)".
5. I saved all the events to a CVS file.
6. I imported the CVS file into my Google calendar.
7. I synced my old Windows Mobile phone with my Google calendar.
8. Now I have to fit in time for the exhibit hall..
   

It was a long painful process, but I now have all the events I want go to in my phone.

Here are my preferred events for each time slot. Unfortunately it shows the session ID and not the session title.

Oracle Forms Builder will not start

Well I ran into an error that I found posted out on the Internet with no solution listed. I just wanted to post my solution in case someone else is running into this.

Problem: "All of sudden" today when I started Oracle Forms Builder 9i (yes we are using 1996 technology) I got Microsoft Visual C++ Runtime Library error Assertion failed! FRM-10039:



Cause: A new Java Virtual Machine (JVM.DLL) was pushed out to our PCs. Users were having problems with it. While working out the user's JVM problems, I made a copy of JVM.DLL in the directory where I start Oracle Forms Builder. When Oracle Forms Builder was starting it was using the local copy of JVM.DLL and not the copy in the directory where I installed Oracle Forms.

Fix: I deleted the copy of the JVM.DLL that was in my local directory. Now Oracle Forms Builder is using the JVM.DLL that was provided by the Forms installation.

Summary: If you get the Assertion Error when starting Oracle Forms Builder, check to see what JVM.DLL file is being used by Oracle Forms.

How Oracle got to where it is today

Here is a great presentation about the history of Oracle got to where it is today.

Oracle Forms - 2011 Conference Missing In Action (MIA)

I was doing some 2011 training planning and was looking at some Oracle-related conferences for this year. I noticed there are no Oracle Forms sessions at the IOUG Collaborate 11 and one "migrating off of Oracle Forms" session at ODTUG KSCOPE11. Not a good indication for the future of Oracle Forms.

If you know of any please post them as comments to the blog entry.

Apex 4.0 Upgrade, Not Possible

Well I started looking at my 2011 projects and was getting excited about upgrading of our Oracle Apex version 3.1 to version 4.0 until I found out that Apex version 4.0 requires 10.2.0.3 or higher. Unfortunately the main Apex repository we have is Oracle 9i!! Barring moving the Apex repository (applications) to a different 10g database and setting up database links to "bridge" back to the data in the 9i database (ugly and messy), I am stuck with Apex 3.1 until the 9i database is upgraded to 10g. Unfortunately that is dependent on a lot of non-Apex applications completely out of my control getting modified/upgraded. Oh well.

Oracle A.I.M. Retired

This morning I started doing some reading on Oracle Application Implementation Method (A.I.M.) 3.0. I ran across the multitude of documents associated with it. I remember back reading books on Oracle's CASE*Method. Well while surfing for more information about A.I.M. I noticed that Oracle is retiring A.I.M. as of the end of this month (January). Now I need to see if I can dig up information about ORACLE^® UNIFIED METHOD (OUM)

Oracle's Database History

I have been working with Oracle since version 5 (1986). It is always interesting to try to remember when certain feature was added to the database. I found a link to an Oracle Magazine article which lists Oracle's database history and more:

http://www.oracle.com/technology/oramag/oracle/07-jul/o4730.html

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:

"When All Else Fails..."

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

Our "Friend" ORA-01555

Have you ever had your Oracle program ever get the dreaded ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small error, you rerun the program, and it runs to completion with no errors? No changes to the code. No changes to the program parameters. No changes to the database. You just rerun the program and it works. As an application developer I would just say that the DBA needs to increase the amount of snapshot space and leave it up to the DBA to handle it. But since it is not a re-creatable event, the DBA could not do anything because we could not tell how much to increase the rollback space...

Well I ran into this sporadically at work recently and was able to overcome the problem. I overcame the problem with a programming change and not a database change. Basically the application when it was complete was deleting data out of a number of tables. We had some deletes inside of a cursor loop at the program's end.

FOR info IN cursor_x LOOP
DELETE FROM table1 WHERE ...
DELETE FROM table2 WHERE ...
...
END LOOP.

I finally determined that the problem was one iteration through the loop was deleting so much data that the database was cycling through the rollback space and when the cursor got to the top of the loop and attempted to get the next row of "info" the error would appear. So it was not the delete, but the "get" of the the row. The snap shot was too old. The database could not locate the next row of data in the cursor loop.

What was causing the sporadic nature of this problem was that we would have up to three of these programs running simultaneously. Some runs deleted small amounts of data and others deleted large amounts of data. If the programs were single threaded there was no problems. So the rollback space was large enough for one program to run, but multiple runs with at least one deleting a large amount of data would not work. In my option single threading the programs was not a fix. It was just a work around.

So since the data used for the cursor loop was not affected by the DELETEs inside the loop I did a BULK COLLECT and iterated through the array like:

OPEN cursor_x;
FETCH cursor_x INTO info;
CLOSE cursor_x;

FOR i IN 1 .. info.COUNT LOOP
DELETE FROM table1 WHERE ...
DELETE FROM table2 WHERE ...
...
END LOOP.

I figured I would share this in case you run into it. Usually I see the issue discussed from a DBA's perspective and not the programmer's perspective.

We have had no problems since. I had better not say that too loudly, the Oracle kernel may hear me;)

Diving into Java

Well I finally wrote my first professional Java program. It was professional in the sense that it did more than "Hello World" and was for work. There was data made available on an external Internet web page. To get the data the users had to: select from 4 drop down lists, press a button, copy the data out of MS Notepad, paste the data into Excel. The kicker was to get all the data they were going to have to do this process 3,591 times. My challenge was to automate the download of all this data off the web page directly into our Oracle database. This is the story of my two week "Dive into Java".

I first started looking at automated web page testing software to see if I could script the series of clicks. It became apparent quite quickly that I would be able to handle the clicks, but not the copy and paste of the data out of MS Notepad into Excel.

I then started looking at the external web page. I "inspected" the source code using Firebug to see all the values for the 4 drop down lists and got to thinking that if I could simulate these actions in Java maybe there would be a way of capturing the web page results to a text file that could be loaded into the database. So I started looking into Java. Keep in mind that I had read Mastering Java 2 5 years ago and only written HelloWorld.java. All of the people that got formal Java and J2EE training in our department have left the company, so I had "hack" my way through this.

I discussed the issue with a colleague of mine. He gave me a small snippet of Java code which connected to a web page and wrote the results to standard output. If I could take this code, loop through the permutations of the 4 drop down lists (arrays), and write the results out to a text file, I would have the data.

I first had to get into a Java IDE, Oracle JDeveloper. I was slightly familiar with it. I had used it for the Entity Relationship diagrammer. I had to get used to how projects mapped over to directories. That was not too bad.

My next challenge was that the Java program program was unable to get out through the our firewall at work. I could only run it at home where I had a direct connect to the Internet. Abstract class URLConnection (wow I sound like a Java developer) could not get through the firewall. I got to thinking, Java applications like JDeveloper connect out through the firewall by prompted for my firewall ID and password. So I did some searching on the Internet and found Authenticator.setDefault. With this I was able to get through the firewall out to the external web page, but I had to hard code an ID and password into the Java program. That had to change, so now I had to dig into "prompting" the user for the information.

So I did yet more Internet surfing (see a pattern here) and came up with DataExchangeTest.java from the Core Java 2 Fundamentals. I now was able to prompt for my firewall ID and password and got rid of all hard coding. (Trust me it is all gone.) This got me into Java Swing/AWT (more Java alphabet soup).

Now I could run the Java program from computer at work. The program created a text file on my computer which I had to FTP to my database server, SQL*Load the file into a database staging table, and run a data parsing database procedure to populate the final destination tables. Could I write the data into the staging table in the database, taking SQL*Load out of the picture, and "kick off" the data parsing procedure from Java? Here comes JDBC. More surfing gave me JDBC basics so I could "write" the data directly into the database and populate the destination table.

My next step is to run the Java as a regularly scheduled batch job. It could get the latest released data and publish what the differences are. This is not a user requirement yet. Maybe I will take that on in my "spare time".

Wow, what a two-week long struggle with no training, but I did it. I can now put Java on my resume, not yet.

Mike

Last day of "Spring Cleaning"

Tomorrow is my last day of spring cleaning at work. Since I received no suggestions as to what to do with the old Oracle software and documentation, I am going to through it away in the junk tomorrow.

Mike

More "Spring Cleaning"

Well I continued my "Spring Cleaning" at work. I dug into the Oracle software and documentation drawers. After having developed Oracle-based software since 1987, I get sentimental about older versions of software documentation that I spent a lot of time reading and working with. It is always nice to see where you have been so you can hopefully appreciate where are. (See my next blog entry for a complete list of Oracle software and documentation I found.)

Oracle used to actually publish bound documentation book sets. That was before CD ROMs and the Internet. I remember these were prized possessions. It was very expensive to buy extra sets, well expensive for the places that I have worked. So if you had a set of books for SQL*Forms for example, you were popular with all the other developers who were developing Forms applications.

Back in 1987 I started with SQL*Forms v2.0 and Oracle 5. Prior to v2.0 you had FastForms where to develop a screen you had to answer tons of questions. It was a question and answer session to develop a Form. I found SQL*Forms v2.0 documentation but known for Oracle 5. I did find Oracle 6 RDBMS manuals though. Here are some that I found that have sentimental value to me. Hopefully they bring back good memories for you.


SQL*Forms Designer Quick Reference from 1987


IBM DOS v3.30 docs and software 3.5" and 5.25" media from April 1987



IBM OS/2 v1.10 from 1988


Oracle Office Demo Disk from September 1993

Mike

Where does old Oracle software and documentation go?

As indicated in my previous post, I did more "spring" cleaning at work and got hold of some old Oracle software and documentation. Some old documentation like SQL*Forms v3.0 and Oracle7 (RDBMS) Server I actually had to leave in the cabinet because we still have production applications written in them believe it or not. They are running on an old DEC VAX/VMS.

So , where does all that old Oracle software and documentation go when it is superseded by a newer version? Is there an Oracle recycling center out at Redwood Shore? Is there an Oracle historical society or museum that would want this "stuff"?

What should I do with the software and documentation? Is it of use to anyone out there? Should I just throw it all out? Please post your serious and funny suggestions. Oh ya, let me know if you may want this "stuff", too.

Here is what I got hold of:

Oracle Software (all media is CD ROM except where noted)

• Oracle Office Demo Disk September 1993 (3.5" media)
  
• OTN IOUG-A Live 2000 Software Kit
• -Oracle WebDB v2.2 Linux
• -Oracle8i Enterprise Edition 8.1.5 Linux
• -Oracle8i Enterprice Ed. R2 MS Win NT/2000 V8.1.6
• -Oracle WebDB v2.2 MS Win NT
• -Oracle JDeveloper 3.1 MS Win NT
• Oracle Discover v4.1.27
• Oracle JDeveloper R3.2.3
• Oracle8i Lite v4.0.0.2.0
• Oracle Tools CD Pack MS Windows
• -Oracle Designer R6.0
• -Oracle8i Lite v4.0.0.2.0
• -Oracle8i Personal Edition v8.1.5
• -Oracle JDeveloper R3.2.3
• -Oracle Forms and Reports 6i R2
• -Oracle Discoverer 3i
• -Oracle JDeveloper R3.1
• -Oracle Application Server Enterprise Edition v4.0.8.1
• Oracle9i Application Server v1.0.2.2.2 CD Pack Sun SPARC Solaris
• Oracle OpenWorld 2000 Software Kit
• -Oracle Internet Application Server 8i Linux and MS Windows NT
• -Oracle Warehouse Builder v2.0.4.78.0 MS Windows NT
• -Oracle Workflow R2.5.1
• -Oracle WebDB v2.2 Linux and MS Windows NT
• -Oracle Internet File Server R1.0 MS Windows NT
• -Oracle8i Enterprise Edition R2 Linux and MS Windows NT
• -Oracle Portal-to-Go v1.0.2.1
• Oracle Database 8i R2 Sun SPARC Solaris
• -Oracle8i Standard Edition R2
• -Oracle8i Enterprise Edition R2
• -Oracle Internet File Server
• -Oracle Enterprise Manger
• -Oracle Workflow
• -Oracle eMail Server
• -Oracle Darwin Data Mining Suite
• -Oracle Express Server
• -Oracle Transparent Gateways (many)
• -Oracle Warehouse Builder
• -Oracle Geocode
• -Oracle Pure Name and Address
• -Oracle WebD v2.2
• Oracle9i Application Server R1 MS Windows NT
• ODTUG Kaleidoscope 2007 (4 copies)
• Oracle JDeveloper v2.0 (4 copies)
• Oracle Application Server v4.0.7 MS Windows NT
• Oracle Application Server v3.0.0 MS Windows NT
• Oracle Webserver v2.1.1 Sun SPARC Solaris
• Oracle Designer R6 MS Windows 95/98/NT
• Oracle Designer R2.1.2 MS Windows 95/98/NT
• Developer/2000 v2.0 MS Windows NT
• Oracle Developer v6.0 MS Windows 95/98/NT
• Oracle Developer/2000 R2.1 MS Windows NT
• Oracle Developer Server v2.0 MS Windows NT Patch 1 (2 copies)
• Oracle8 Personal Edition MS Windows NT
• Oracle Server Enterprise Edition v7.3.4.0.1 Sun SPARC Solaris
  

Oracle Documentation

• Oracle Procedure Builder
• Oracle Forms 4.5
• Oracle Reports 2.5
• Oracle Graphics 2.5
• Oracle Glue
  
• Oracle8 Personal Edition
• Oracle Designer Handbook
• Develop Oracle Forms Applications
• Oracle V6.0 SQL Reference Manual
• Oracle V6.0 Error Codes
• Oracle V6.0 DQC VAX/VMS Installation Guide
• Oracle V6.0 OCI
• Oracle7 Concepts
• Oracle7 User's Guide
• Oracle7 SQL Language (2 copies)
• Oracle7 Utility User's Guide
• Oracle7 Application Developer's Guide
• Oracle PL/SQL v2.0 User's Guide and Reference
• Oracle Call Interface Guide v7.0
• SQL*Forms v2.0 Designer's Quick Reference
• SQL*Forms v2.3 Designer's Quick Reference
• SQL*Forms v2.3 Operator's Quick Reference
• SQL*Plus v2.0 Quick Reference
• Oracle Keyboard Layout Templates for SQL*Forms Operator and Designer for the PC, VT100, ...
  

Mike