Tuesday, November 25, 2008

"When All Else Fails..."

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

Monday, November 10, 2008

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;)

Thursday, September 25, 2008

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

Monday, September 01, 2008

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

Wednesday, August 27, 2008

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

Tuesday, August 26, 2008

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

Wednesday, August 20, 2008

"Spring Cleaning" in the Summer

I started doing some "Spring Cleaning" at work. I am looking into my cabinets and drawers and checking to see what is needed and what is not. What an undertaking. I found some interesting older Oracle/database related things:

  • A 1983 article that explains 5th Normal Form. It was ironic because just the other day I was talking with our team lead about application developers not knowing data normalization.
  • Copies of Oracle 7.2 Server manuals (I started with Oracle 5)
  • Oracle Forms (V4) training manuals (I started with Forms 2.0)
  • PL/SQL (Version 2.0) training manuals
  • Developer/2000 Forms 4.5 training manuals
  • Developer/2000 Oracle Graphics 2.5 Reference Manual
  • Oracle CDE2 Oracle Reports 2.5 manuals
Boy the days of getting the box sets of Oracle manuals is over. It is time to put these in the dumpster. The only thing that is useful now is the normalization document. I will still dump it because it is on the Internet.

Mike

Tuesday, August 12, 2008

Is Data Normalization and Modeling on the decrease?

Because of recent staff turnover in our department at work, we created a list of technical skills that we need to support our department's in-house developed database applications. These were skills like SQL, PL/SQL, Oracle Forms, J2EE, AJAX, Javascript, Unix shell scripting, ... Each person in our department assessed their individual level of expertise in each skill. We then assigned a weight as to which skills we needed to fill immediately and which skills we could wait on for a while.

After two passes of looking at the skills list and assigning weights, I noticed that there were no analysis and design skills like Data Modeling and Data Normalization or modeling tools or methodologies. We had completely forgot to include these, so I suggested adding them. All of us were thinking of application development skills and not analysis and design skills.

That led me the question of, "Is it just my particular situation or is it a general trend in the IT industry that Data Normalization and Data Modeling skills/methodologies like ER diagrams are not being used as much as they used to like back in the 1990's?" Or is Data Normalization such a common skill nowadays that people creating tables just automatically do it? Is it now being taught in college?

Mike

Wednesday, July 30, 2008

Check your iDS (Oracle Forms) version and support

Since I am now temporarily working in Oracle Forms, I looked at our version of the Forms Builder, 9.0.2.9.0. I found out it was just desupported by Oracle on 01-JUL-08 (Oracle Document ID: 283326.1). Ok I looked at the next version 9.0.4. Oracle is desupporting it on 31-DEC-08 (Oracle Document ID: 298788.1). Well I guess 10g R2 (10.1.2.2) is what we should go to. The company I work does not like to go to first releases of software. I am glad they do not do that for our in-house developed software.



Mike

Tuesday, July 29, 2008

Simulate set_item_property('Block.item', intial_value, 'VALUE'); in Oracle Forms

Have you ever wanted to programmatically set and change the initial value of Oracle Form item at run time? Have you to do something like this in your Oracle Form?
set_item_property('Block.item', intial_value, 'VALUE')

and received the following error when you attempt to compile your form:
Error 201 at line .., column .. identifier 'INITIAL_VALUE' must be declared'


Well I have found a way to emulate having a dynamic value for the "Initial Value" property of an item.

1) Add a non-database item to your block. Call the item something like 'your_item_initial_value'

2) Set the "Copy Value from Item" property on your item to be your_block.your_item_initial_value

3) Whenever you want to change the initial value of your item just assign a value to the your_item_initial_value item.

4) Whenever you want to remove an initial value from your item just assign NULL to the your_item_initial_value item.

Note: I am using Oracle9iDS Release 2. Newer versions of iDS may permit you to programmatically change the initial value.

Mike

Multiple PL/SQL Editors Open in Oracle Forms Builder

This blog entry is for my own purpose. It is a little thing that I seem to frequently forget in Oracle Forms Builder. Nowadays I am still sporadicly working in Forms. Actually I am the only Forms developer left in our shop and we have quite a few production Forms applications. I actually love Oracle Forms, but our department has on its long term plan to move off of Forms to either J2EE or ApEx, but that is a topic for another future blog entry.

While getting back into the Forms Builder, I frequently want to have multiple PL/SQL Editor windows open editing different program units at the same time. A couple years ago I found out that if you hold the SHIFT key and double click on a program unit's icon in the Object Navigator pane, Forms Builder opens a new PL/SQL Editor window open while keeping all current PL/SQL Edit windows open!!

Mike

Friday, July 11, 2008

SP2-0552: Bind variable "..." not declared.

I was creating a SQL*Plus script for testing the results of a new report. The script prompted the me for some values and then ran some queries. I then was going to compare those query results with the results of the new report. Pretty basic. I have done this type of thing many, many times before.

The script issues the ACCEPT command to prompt and receive my test values. A SELECT statement runs to query the database. Here is a sample SQL*Plus script based on our friend the EMP table:
ACCEPT p_emp_no char PROMPT "Enter employee ID: "

select *
from emp
WHERE empno = &p_emp_no;

I would run the script in SQL*Plus (Oracle 9.2.0.4.0) and get:

SP2-0552: Bind variable "P_EMP_NO" not declared.
Ok, what the heck is going on? Well it ends up an hour or so earlier I had ran another SQL*Plus script which set SCAN off (SET SCAN OFF) because the prior script had an ampersand (&) in one of the remarks. Well with SCAN set OFF, SQL*Plus was somehow converting '= &p_emp_no" into "= :p_emp_no" and looking at p_emp_no as if it were an undefined bind variable. I discovered this when I finally exited SQL*Plus and reentered and reran the script and it worked. So I added SET SCAN ON to my script and it now works!!

Mike

Tuesday, June 17, 2008

Modeling in SQL Developer

I noticed on Lewis Cunningham's blog that at ODTUG Sue Harper said that SQL Developer will be getting modeling in its next version!! Sounds like Oracle is listening to users in the OTN Forums.

Mike

Friday, May 02, 2008

Locate New SQL Developer Features/Fixes

One thing that I found was an easy way to locate new features and fixes in SQL Developer. If you go to SQL Developer Exchange and sort it by "status" you can see a lot of the features and fixes for a particular version. Note that this is not a comprehensive list of the new features and fixes, but I have found some really neat new things I did not know that are in release 1.5

Mike

Wednesday, April 09, 2008

Tom Kyte and Steven Feuerstein Event in Cleveland, Ohio May 22nd and 23rd

Just wanted to let everyone know that the Northeast Ohio Oracle users group is having a 2-Day event with Tom Kyte and Steven Feuerstein.

Feel free to pass this information along.

Mike

I can't get away from Oracle, even on vacation.



My family and I went to Washington, DC for part of our Easter break in March. When we went to get on the subway (sorry I mean the "Metro"), low and behold what should I find but advertisements for Oracle on the walls and the ground.



Mike