Michael A. Rife's Blog

Oracle Openworld 2011 Schedule Set

2011-09-28T10:35:00.000-07:00

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

In the Openworld schedule content catalog I went through and populated my interest list.
I copied (cut and paste) the 69 events to my PC in Excel (date, time, location and event title).
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)".
Next I selected 33 secondary choices left on my interest list for each time slot. I prefixed my secondary event names with "2)".
I saved all the events to a CVS file.
I imported the CVS file into my Google calendar.
I synced my old Windows Mobile phone with my Google calendar.
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

2011-08-17T10:27:00.000-07:00

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

2011-02-14T08:56:00.000-08:00

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

Oracle Forms - 2011 Conference Missing In Action (MIA)

2011-02-04T10:52:00.000-08:00

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

2011-02-02T10:43:00.000-08:00

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

2011-01-18T07:01:00.000-08:00

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

2010-04-27T09:10:00.000-07:00

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

2009-12-18T12:03:00.000-08:00

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

2009-09-22T08:09:00.000-07:00

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.

2009-08-26T10:42:00.000-07:00

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

2009-05-13T12:18:00.000-07:00

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

2009-02-23T12:56:00.000-08:00

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

2009-02-17T12:10:00.001-08:00

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

2009-02-03T09:40:00.000-08:00

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..."

2008-11-25T10:53:00.000-08:00

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

Our "Friend" ORA-01555

2008-11-10T06:42:00.001-08:00

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

2008-09-25T08:05:00.000-07:00

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"

2008-09-01T07:02:00.000-07:00

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"

2008-08-27T06:02:00.000-07:00

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?

2008-08-26T12:54:00.000-07:00

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

"Spring Cleaning" in the Summer

2008-08-20T09:41:00.000-07:00

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

Is Data Normalization and Modeling on the decrease?

2008-08-12T12:37:00.000-07:00

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

Check your iDS (Oracle Forms) version and support

2008-07-30T10:23:00.000-07:00

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

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

2008-07-29T11:07:00.000-07:00

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

2008-07-29T07:12:00.000-07:00

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

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

2008-07-11T10:51:00.000-07:00

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

Modeling in SQL Developer

2008-06-17T13:08:00.000-07:00

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

Locate New SQL Developer Features/Fixes

2008-05-02T07:51:00.001-07:00

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

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

2008-04-09T08:56:00.000-07:00

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.

2008-04-09T08:24:00.000-07:00

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

OOW Envy Week

2007-11-12T07:58:00.000-08:00

Well, this is the week that I am envious of all those attendees of Oracle Open World conference. I read all the blogs of attendees of the conferences and evening activities and wish I were there. I was unable to attend based on my work schedule and priorities, so reading the blogs is a great way to get some insight into what's new at Oracle.

This morning reading the OOW blogs caused me to recall that I have been to good number of Oracle-related conferences. I have not been to any Oracle Open Worlds but have been to one ODTUG conference and 10 IOUG conferences. I attended IOUG conferences when they were a combined effort between Oracle and the International Oracle User's Group (IOUG). Around 1995 the conference split into the Oracle Open World conference and the IOUG conference. I also did a couple presentations at the IOUG conference in 2000 in Anaheim.

So I dug out me list of Oracle conferences and here it is. It is not complete. It may not be completely correct. I do not have all the ODTUG conferences. I may add them at some point in time. I have a star in front of the ones that I attended.

Oracle Conferences

1985 IOUW San Diego, CA
1986 IOUW San Francisco, CA
1987 IOUW Washington, DC Oct
1988 IOUW Orlando, FL
*1989 IOUW Dallas, TX
1990 IOUW Anaheim, CA
*1991 IOUW Miami, FL
1992 IOUW San Francisco, CA
*1993 IOUW Orlando, FL Sep 26-Oct 1
1994 IOUW San Francisco, CA
*1995 IOUW Philadelphia, PA * Last conference with Oracle and IOUG combined
1996 IOUW Dallas, TX Sep 15-20 was cancelled
1996 OOW San Franciso, CA Nov 3-8
*1997 IOUG Dallas, TX Apr 27-May 2
1997 OOW Los Angles, CA Nov 14-18
*1998 IOUG Orlando, FL May
1998 OOW San Francisco, CA Nov 8-12
*1999 IOUG Denver, CO Apr 11-16
1999 OOW Los Angles, CA Nov 14-18
*2000 IOUG Anaheim, CA May 6-11 (I presented at this conference.)
2000 OOW San Franciso, CA Oct 2-5
*2001 IOUG Orlando, FL Apr 29-May 3
2001 OOW San Franciso, CA Oct 2-8
2002 IOUG San Diego, CA Apr 14-19
2002 OOW San Franciso, CA
2003 IOUG Orlando, FL Apr 28-May 1
2003 OOW San Franciso, CA Sep 8-11
*2004 IOUG Toronto, ON Apr 19-22
2004 OOW San Franciso, CA Dec 6-9
2005 IOUG Orlando, FL May 1-5
2005 OOW San Franciso, CA Sep 19-22
2006 IOUG Nashville, Tennessee Apr 23-27
2006 OOW San Franciso, CA Oct 21-24
2007 IOUG Las Vegas, NV Apr 16-19
* 2007 ODTUG Daytona, FL Jun 18-20
2007 OOW San Franciso, CA Nov 12-15

In ApEx, Upload/Dowload BLOB/CLOB stored in another database

2007-09-18T06:25:00.000-07:00

Scenario: I have a database named BLOBS_DB which has a table (REPORTS) containing a primary key (PK) column, a BLOB column (BLOB_COL) , and a CLOB column (CLOB_COL). I have HTML DB (predecessor to Oracle Application Express) running in another database named APP_DB. (The names of the databases and objects have been changed to protect the innocent;) I want to be able to upload and download either LOB columns into the BLOBS_DB database from within an HTML DB application running on the APP_DB database.

Problem: For downloading you cannot issue a SELECT of a LOB column over a database link. You get the "ORA-22992: cannot use LOB locators selected from remote tables" error message.

The Trick: You can get LOBs over a database link using an INSERT statement.

Upload Trick: The HTML DB form inserts into a global temporary table (UPLOAD_REPORTS) in the APP_DB with an INSTEAD-OF trigger on it which moves a copy of the LOBs to the REPORT_DB database via a INSERT INTO/SELECT FROM statement:

INSERT INTO reports@BLOBS_DB
(pk, blob_col, clob_col)
VALUES
(:new.pk, :new.blob_col, :new.clob_col);

Download Work Around: When the user selects a LOB to download, I move a copy of the row in the BLOBS_DB database to the APP_DB database with an INSERT INTO/SELECT FROM statement:

INSERT INTO download_reports
SELECT * FROM reports@BLOBS_DB;

Since the UPLOAD_REPORTS and DOWNLOAD_REPORTS tables are a global temporary tables in the APP_DB database when the HTML DB access to the APP_DB database is finished, the global temporary tables are emptied.

Preserve Leading Zeros in ApEx CSV Export

2007-08-27T06:44:00.000-07:00

I located a method of preserving leading zeros on values when exporting a report region to CSV in HTML DB (ApEx).

When Excel opens up a CSV file it defaults all cells to "General" formatting and attempts to "help the user" by removing leading zeros on cell values and converting them from strings to numbers. That can be a problem for values like product codes (SKUs) that begin with a zero.

The trick is to SELECT the value such that Excel will see it as a string and not a number. So prefix an equals sign and double quote on to the value and post fix a double quote. So

SELECT your_column
FROM your_table

becomes

SELECT '="'||your_column||'"'
FROM your_table

That is great except the users now see the additional characters in the report region. To get a round this use the following SELECT statement:

SELECT your_column, '="'||your_column||'"' quoted_column
FROM your_table

and put a "PL/SQL Express" condition (based on Sergio's blog entry) on each column. The conditional display express for YOUR_COLUMN would be:

INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL') = 0

The conditional display express for QUOTED_COLUMN would be:

INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL') != 0

To get even more customized CSV output look at Scott's blog entry.

SQL Developer's "SQL History"

2007-08-24T10:38:00.000-07:00

I love the SQL History button (F8) in SQL Developer. Let me tell you what happened to me yesterday. I using SQL Developer to work on a difficult SELECT statement when the electricity at work went out on me. Poof my SELECT statement was gone, right? Not in SQL Developer. When everything came up I started SQL Developer, opened a SQL Worksheet, and clicked on the SQL History button . There was my SELECT statement.

Thanks Oracle for the button.

Table Rows to One String in SQL

2007-08-23T07:15:00.000-07:00

I came across of very cool feature yesterday that I had not seen before. It was the ability to take values of a column in multiple rows and concatenate them into one string. This is useful for building a TO distribution list for an email. Here is an example of concatenating all the names of the employees in the EMP table:


SQL> SELECT *
2    FROM emp;

EMPNO ENAME      JOB             MGR  HIREDATE  SAL        COMM       DEPTNO
----- ---------- --------------- ---- --------- ---------- ---------- ----------
7839 KING        PRESIDENT            17-NOV-81       5000                    10
7698 BLAKE       MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK       MANAGER         7839 09-JUN-81       2450                    10
7566 JONES       MANAGER         7839 02-APR-81       2975                    20
7788 SCOTT       ANALYST         7566 09-DEC-82       3000                    20
7902 FORD        ANALYST         7566 03-DEC-81       3000                    20
7369 SMITH       CLERK           7902 17-DEC-80        800                    20
7499 ALLEN       SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD        SALESMAN        7698 22-FEB-81       1250        500         30
7654 MARTIN      SALESMAN        7698 28-SEP-81       1250       1400         30
7844 TURNER      SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS       CLERK           7788 12-JAN-83       1100                    20
7900 JAMES       CLERK           7698 03-DEC-81        950                    30
7934 MILLER      CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
2    FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
3            FROM (SELECT ename myvalues
4                    FROM emp
5                 )
6         ) data
7   WHERE rn = cnt
8   START WITH rn = 1
9   CONNECT BY PRIOR rn = rn-1;

CATVALUES
-----------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

Here is the SELECT statement:


SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
FROM (SELECT ename myvalues
      FROM emp
   )
) data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn-1;

To figure this out let us look at the inner SELECT:


SQL>
SQL> SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
2    FROM (SELECT ename myvalues
3            FROM emp
4         );

MYVALUES           RN        CNT
---------- ---------- ----------
ADAMS               1         14
ALLEN               2         14
BLAKE               3         14
CLARK               4         14
FORD                5         14
JAMES               6         14
JONES               7         14
KING                8         14
MARTIN              9         14
MILLER             10         14
SCOTT              11         14
SMITH              12         14
TURNER             13         14
WARD               14         14

14 rows selected.

Oracle starts at the top where

rn = 1

which is ADAMS. It connects each node of the tree to the prior element where the prior's RN value is one less

rn = rn -1

until WARD. Now the SYS_CONNECT_BY_PATH function in a CONNECT BY query takes the tree and concatenates the values together putting a comma ',' between the nodes in the tree.

Updated GUI Intensive ApEx Application post

2007-08-21T09:17:00.000-07:00

I will keep adding issues to my GUI intensive ApEx Application blog as I encounter them. This will keep them all together in one place.

ODTUG Kalidescope Conference

2007-06-17T19:07:00.000-07:00

I attended the Advanced APEXposed 2007 training and the ODTUG Kaleidoscope conference. Both the beginning and advanced Oracle Application Express (ApEx) training were sold out.
On the conference web page there is a listing of all the conference’s technical sessions. I copies of these papers on CD if you are interested.
For each training or technical session I attended, I have listed below the abstract of the session along with what we did or special things of note from the session.

ODTUG ApExposed (Advanced Oracle Application Express Training)

Welcome & Keynote
Mike Hichwa, Oracle Corporation

Hands-On Ajax and JavaScript
Scott Spendolini, Sumner Technologies, LLC

Web applications are quickly maturing. More client/server-like functionality is appearing on the Web each day, making sites easier to use and navigate. APEX itself included several libraries which make adding these types of features much easier. This session will outline when and when not to use Ajax and JavaScript in your APEX applications. It will provide an overview of the built-in APEX JavaScript libraries. You will build a number of different Ajax and JavaScript components, from instantaneous validations to dynamic select lists, and learn how to deploy them in your own APEX applications.
• Added “is required” validation to an application so the user does not have to submit the whole page to the server just to get a “field … is required” error message. The user get the error message as they attempt to exit the field.
• Added automatic uppercasing of fields which fires when the user moves out of a field.
• Added hiding and showing of page items based on the value of other items on the page.
• Added hiding and showing of a field and its label.
• Added “check all” option for checkboxes in a multiple row region.
• Added a pop-up window.
• Added a confirm message (OK or Cancel).
• Added an on-demand process so when a value is changed on the page its description is automatically updated without requiring the user to submit the page to the server.
• Added an on-demand process so when a value is changed on the page a select list’s list of values is automatically updated without requiring the user to submit the page to the server.
• Added logic to refresh a report region without having to rerender the whole page.
• NOTE: User ApEx validation processes in connection with Javascript validations because users can turn Javascript off in their browser.

UI and Design
Scott Spendolini, Sumner Technologies, LLC

Most developers are not graphic designers, and most graphic designers are not developers. And with good reason! Despite this, the expectations to create a well-designed site often lie squarely on the developer’s shoulders. This session will demonstrate the entire process of replicating an existing user interface in APEX. It will discuss the methodology used when creating the templates, discuss the different tools available, and cover Section 508 concerns.
• To create a new theme start with a copy of the built-in theme #4.
• Use subscriptions when standardizing themes across applications.
• Do not load theme images in the /i/ directory. Create your own virtual directory off of the /i/ directory.
• Use TABINDEX to control proper ordering of tabbing from item to item in a page.

Security
Raj Mattamal, Sumner Technologies, LLC

As important as it is, security is almost always added to an application as a last step, if at all. Designing your APEX application with security in mind is critical and should not be overlooked. This session will discuss how to build secure APEX applications from the ground up so that a hacker cannot exploit them. Time will also be spent on discussing how to segment functionality inside of an APEX application so that only authorized users can perform specific tasks. It will examine what can be done to secure the underlying data of an APEX application. Finally, it will outline a list of things that need to be secured in an installation of APEX, as well as the associated infrastructure components.
• For testing use “Open Door” authentication. It allows you to login and any user ID.
• Turn on session state protection.
• Check for SQL Injection on regions which refer to search field(s) or regions based on a SELECT statement generated by PL/SQL.
• Check for what I call “Javascript Injection”. User enters Javascript into a data entry field and submits the page. When the page is rendered, the Javascript he entered is functional. To counter this in PL/SQL there is the UTL_URL.ESCAPE function or the Javascript escape and encode functions.

High Fidelity Reporting: Hands-On PL/PDF
Josh Millinger, Sumner Technologies, LLC

With APEX 3.0, creating PDF reports will be as simple as just a couple of clicks. However, there are some limitations to the mechanism used in APEX 3.0 which will limit what it can be used for. PL/PDF, a third-party product, is a PL/SQL-based solution that enables you to generate PDF documents from the Oracle Database. PL/PDF, like APEX, is PL/SQL based, does not require any additional hardware, and is extremely flexible. This hands-on session will outline when to use PL/PDF vs. the built-in PDF engine in APEX 3.0. It will also provide a quick overview on how to install PL/PDF, and will then walk you through the creation and deployment of a PL/PDF report integrated with APEX.
• PDF reports are created in PL/SQL procedures.
• There is no visual editor of the report.
• Can start with PDF and overlay information on it for filling in printable forms.
• PL/PDF costs $500 per database

Managing APEX
Joel Kallman, Oracle Corporation

You’ve installed APEX at your organization, people have started to build and deploy applications with it, and before you know it, you have another mission-critical component that needs to be managed. In this session, the Oracle APEX team will discuss the architecture of APEX, how it was designed with scalability in mind, and how to use features of the database and APEX itself to assist in managing it. It will also discuss how to manage the APEX e-mail queue, identify and enhance the performance of slow queries, and prevent poorly written ones from degrading the performance of your system.

APEX APIs
Raj Mattamal, Sumner Technologies, LLC

The APIs provided with APEX are quite powerful. They extend the functionality of APEX so that it can be integrated into named PL/SQL procedures and functions, create data-driven dynamic forms, allow for custom authentication functions, and integrate with LDAP, to name a few. This session will examine the most popular features of the APEX APIs and provide working examples to illustrate how to invoke them. It will also discuss the benefits of and when to use data-driven forms in your applications.

2007 ODTUG Kaleidoscope Conference Presentations I Attended

Database Tasks with Oracle SQL Developer
Sue Harper, Oracle Corporation

Oracle SQL Developer provides the database developer with a convenient way to perform basic database tasks. Connected to an Oracle database, users can browse, create, edit, and delete database objects; create, edit, and debug SQL and PL/SQL code; manipulate and export data; and create reports. This demonstration-rich session briefly positions the tool, and then quickly walks through a series of activities that a user might do in any day-to-day activity; from creating a connection, to creating basic objects and then writing and debugging PL/SQL code. With particular focus on new features in SQL Developer 1.1, this talk will also address more complex aspects, such as extending the tool, and a few plans for the future.

APEX and Project Management
Patrick Cimolini, Cayman Islands Government

Application Express (APEX) is a robust and productive development tool, however, even APEX projects can be late, over budget, and of dubious quality if they are not well managed. Looking at APEX from a project manager's perspective, the following questions are addressed: How long will a project take? What tasks needs to be done? How long will a single task take? When should the tasks be done and in what order? Who performs the tasks? What skills are required? How are source code and versions managed? What quality can be promised and delivered to the customer? Quantitative metrics and a production application will be used to illustrate the points.

Polishing Below the Surface with Ajax
David Schleis, Wisconsin State Laboratory of Hygiene

It has been suggested by the world’s premier Oracle development users group that decisions concerning hardware and operating system are secondary, and now “it’s all about the web.” The technology that allows for the creation of sophisticated Web-based applications that behave like desktop applications is Ajax, or Asynchronous JavaScript and XML. Ajax allows for some very flashy user interface features to be included on a Web page, but it can also be used to perform less glamorous duties. This presentation provides an introduction to Ajax, including a brief overview of its component technologies, as well as examples of under-the-covers functionality that can be designed into new pages or used to enhance existing applications.

Getting XML Data into and out of Oracle with PL/SQL
Ken Atkins, Creative Design Associates

XML is very powerful as a method of transporting data between different applications or databases. Because of this, PL/SQL developers are often faced with the task of creating XML documents from data in normalized Oracle tables, or of storing the data in XML documents back into normalized Oracle tables. This session will present various tools and techniques that are available to the PL/SQL developer to read, write, and manipulate XML documents with the express purpose of getting the XML information into and out of standard Oracle tables.

Flex Your APEX: Implementing Oracle E-Business Suite Flexfields in Application Express
Shane Bentz, InterVarsity Christian Fellowship/USA

With every passing day, the functional boundaries between traditional client-server applications and Web applications decrease. New technologies such as SOAP and Ajax enable Web applications to function and perform like traditional fat-client software. In this session, I will show how to integrate the E-Business Suite flexfield functionality into Application Express. By leveraging the power of Ajax, Application Express can use descriptive flexfields to create rich Web applications—applications that can change shape and purpose on-the-fly, enhancing the user experience by not reloading the page and minimizing future coding by developers.

APEX by Example: Shared Components
Dimitri Gielis, Ordina Belgium

Oracle Application Express (APEX) is a quickly growing development environment. The initial goal of APEX was to have something 'simple', a wizard-driven development environment to compete with MS Access or to replace Excel files. People still use the wizard a lot, but if you would really like to use the force of APEX, you can use Shared Components. This session covers all available Shared Components; it tells you what it is for, why to use it, and how to use it in a real environment with the "DG Tournament" application.

Advanced Database Development with Oracle SQL Developer
Sue Harper, Oracle Corporation

SQL Developer provides a convenient way to perform basic database tasks. Here we look at some of the activities you might perform, running complex scripts, normalizing data, and remote debugging. Advanced reporting capabilities include graphical displays and master-detail reports. We demonstrate a few existing extensions to SQL Developer and show you how to build and include your own extensions.

Advanced Application Express Programming Techniques
Michael Hichwa, Oracle Corporation

Learn Michael Hichwa's perspective on how to develop Application Express (APEX) applications from data model to user interface. Get recommendations for source code management, upgrades, and customizations. See how to exploit new Oracle APEX 3.0 features. See techniques to improve and test performance.

Building Interactive Web Reports with Application Express
Jeff Hohman, Oracle Corporation

This presentation will show the reporting abilities of Applications Express, will demonstrate how to have multiple SQL statements displayed on one page going to different data sources, and will demonstrate the drilling and manner of which variables can be passed around to other reports. It will also show other Applications Express components (ex. Graphs, Forms, etc.) integrated into the same dashboard.

APEX Lessons from the Trenches
Bill Holtzman, National Air Traffic Controllers Association

Application Express enables rapid and efficient development of secure and enterprise-level Web applications. Like any platform, there are numerous tricks of the trade that one learns only after getting waist-deep in the coding. The National Air Traffic Controllers Association (NATCA) has been digging successfully in the APEX gold mine for three years and has a wide assortment of coding techniques to share with those undertaking or considering expanding their use of APEX.

Managing Large Application Development Using Oracle Application Express
David Peake, Oracle Corporation

This presentation will outline developing large applications using Oracle Application Express (APEX). It will be based on personal experience managing a diverse team of developers and personally developing APEX Applications on a comprehensive system with close to 1000 individual applications. Development on this application started over four years ago and has successfully been implemented as the primary enterprise-wide OLTP system for a large law enforcement agency with thousands of users.

A GUI intensive ApEx Application

2007-06-12T06:03:00.000-07:00

This blog will start out as a laundry list of GUI-type issues that I run into while developing a very GUI intensive. My initial impression is that I should be able to address these issues with either normal HTML DB page design or JavaScript. We are running HTML DB version 2. My background in screen development is Oracle Forms in which one can develop very “client-heavy” GUI applications.

I will briefly describe the application. I cannot specify exactly what type of data the application is based on, but I can describe the application at a high enough level that the topics can apply to many other sets of data.

The application is basically a “user reporting criteria gathering” application. The user assembles a study which is a collection of one or more types of reports, specifies the criteria for the reports, and clicks the run button. The user then receives one or more emails with the reports in Excel format as attachments. The HTML DB application handles the study definitions, report criteria, and run button. The data gathering is done as a DMBS_JOB in PL/SQL. It takes data from the large source tables, summarizes it, and saves it to results tables. An Excel process is alerted. Excel places the summarized data into a worksheet, attaches the worksheet to an email, and sends the email to the requester. This is the second application I have done using this same design. I worked on another application a couple years ago that was based on Oracle Forms.

Issue 1: A Generic User Prompter

As part of the application the user will have the ability to copy a study or a report in a study. So the screen will have to prompt the user for the new name of the study or report. I decided to utilize the JavaScript prompt method to prompt the user. I created a function that you can pass prompt text to display and the name of the page item that should receive the user's response as well as a default value.

function prompter(p_prompt, p_destination, p_default) {
var reply = prompt(p_prompt, p_default);
if (reply != ' ' || reply != null) {
html_GetElement(p_destination).value = reply;
}
else {
html_GetElement(p_destination).value = null;
}
}

Issue 2: Cascading Select Lists in a From Region

There are two Select Lists in a form (data entry page) region. The values in the second Select List are dynamically based on the value that the user selects in the first Select List. The first Select List is the type of report the user wants. The second Select List is the format in which the report should be rendered. You could have two rendering formats for the same report. One format could be a graphical chart. The other could be just a numerical dump of the detail data.

I did not want to force a page submit to populate the second Select List because it would fire the Automatic Row Processing which would attempt to commit to the database. I tried playing with the request values upon submit of the page, but could not get that to work. So after playing around with a couple other options I decided to implement an AJAX JavaScript solution provided by Carl Backstrom. It is pretty slick.

Issue 3: Cascading Select Lists in a Tabular From Region

This is a little harder because you have multiple Select Lists in the region. In the development process I ended coding this region 4 different ways before deciding on one way. I used both wizard generated tabular form regions and manually created form regions. In trying to implement an AJAX solution I read a method that Vikas demonstrates of being able to access particular items in particular rows in a tabular form. I was able to get AJAX solution to work, but I decided to “silently” submit the page for the user once they made a selection in the first Select List. The rendering of the page then populated the dependent Select List with the appropriate values.

Issue 4: Show Parent (Study) in a Tree of Reports

I wanted to display the structure of the study and its reports in one tree structure. I was easily able to display a tree of parent and child reports, but wanted the root of the tree to be the study name. So I created a view which UNIONed in the study name as the root of the tree and all the parent reports and their children as children of the root. The tree item in the page region then listed the study as the root with the reports underneath it like:

Issue 5: Allow User to Save Partial Studies

We needed the ability to allow the users to partially save a study. In other words allow the user to define part of a study and at a later time come back and edit the report. I used normal wizard generated HTML DB screens. There was nothing difficult here. I allowed screens to save the data to the database. The tricky part was not allowing the user to run partially entered study. Also we needed a way to communicate to the user what parts of the study need to be fixed or corrected. I handled this with a Study Validater function and a Report Validater function.

At the top of the “edit study” page I put a region that displays which reports in the study the user needs to correct or do something to. This region calls the Study Validator passing in the study ID of the study the user is editing. The function returns a table of records. Each record indicates which report in the study the user needs to modify.
We created a table type which allows us to display the rows returned by the function. So our region SELECT statement was as simple as:

SELECT ROW_NUMBER() OVER (ORDER BY NULL) task, text description
FROM TABLE(check_study(:P3_STUDY_ID))

See technical details

The run study button calls the Study Validater. If the validater returns no rows, then the study is submitted to be run. If the validator returns rows then a message pops up indicating that the user has report modifications to make.

Similarly I have a Report Validator. It provides the details of what the user has to do for a given report before they can submit the study to run.

Issue 6: Checkbox in a Tabular Form

The tabular form was wizard generated. I wanted the last item in each row of data to be a checkbox resulting in a “Y” for yes or an “N” for no to be inserted into the column in the database. I could have done this as a checkbox and coded an extra page process to assign the column’s value to each row in the database, but I decided to use a Select List based on a Yes/No list of values. It is not as fancy or flashy as a checkbox, but I was able to leave all the inserts, updates, and deletes to the wizard generated code.

I will add more as I encounter them.

Blogging Topics

2007-06-09T05:39:00.000-07:00

I have two Oracle-related topics tbal I decided to blog about.
1) I will be attending the ODTUG Kalidescope conference this year in Daytona, FL. I will also be attending the Advanced ApEx training.
2) I am about to start 5th Oracle Application Express (ApEx) application. Being the 5th is not significant. Being very GUI itensive is. There is going to be more client side functionality then any Apex I have done so far. Being from an Orale Forms background I tbought it would cool to do a "This is how I would do it in Forms and how I did it in ApEx" blog.