Talk2Gerd

The Future of Oracle Forms is .... Forms

2012-01-26T10:37:00.003+01:00

Many thanks to Mia. She gave me the tip to her blog

where she pointed to Grant's presentation:

That's the first time, that we directly hear from Oracle, that the best technology to where we should migrate to, is .... Oracle Forms

and not Java, APEX, ADF or .NET !

that's a good start into 2012

Save Oracle Reports

2011-12-01T10:34:00.000+01:00

Many thanks to all of you. I got many emails in the first days of my "Save Oracle Reports" campaign.

I forwarded all emails last week to Oracle Product Management.

I'm interested if we get response... and what kind of response, if we get one.

We need

Campaign Save Oracle Reports

2011-11-21T09:47:00.000+01:00

I started last thursday the campaign "Save Oracle Reports" and I get more and more EMails. All of them have company-wide applications running with forms and reports and cannot live without reports.

One customer (thanks to Markus) pointed me on the actual OTN-Forms-Forum-Poll named "How many Oracle Reports do you have" :

I think, it is important, that we all attend at this poll.

Oracle Reports is dying?

2011-11-17T11:30:00.000+01:00

At the DOAG Development Panel yesterday was told about the future of Oracle Reports. That did not sound good

Next week there will be an important internal meeting at Oracle, where they speak over the future of Oracle Reports in the Oracle Fusion Middleware 12c. It looks like, that the BI Publisher will be the replacement for Reports and Oracle Reports 11g would be the last version.

That's not good... And not only because the price of the BI Publisher, I do not even speak about ...

And here is my idea to save Oracle Reports:

I need emails from all customers, who have an invest in Oracle Reports and want to save it. Write down your arguments, why you need Oracle Reports in Fusion Middleware 12c. The emailaddress is

I will bundle all answers a.s.a.p. and forward them to Oracle Development directly. They agreed with me yesterday about that. They need and want this feedback !

Very important: We don't have time and have to react very fast. Spread this idea to other customers, your colleagues, etc. They should write me in the next days.

I hope we have a chance to get our

let's try it
Gerd

Oracle Forms 12c

2011-11-16T13:00:00.002+01:00

"Oracle Forms 12c" is the new official name in the Fusion Middleware 12 universe for our good old forms!

Heard on the German Oracle User Group Conference from the development team.

So it looks like all products are getting the c (for cloud) in their name.

Let's hope, that Fusion Middleware 12 starts in 2012 !

Result of the 2011 Oracle Forms poll - part 9

2011-08-30T10:23:00.001+02:00

Summary

When I take a look at the poll results, I recognize as a major concern for many users, the fear of an uncertain future. Oracle should give a clear commitment to Forms and combat this insecurity so active.

There is a big risk that the majority of those who went away from Oracle Forms and go to .NET. That was not aware before my survey and is one of the biggest surprises for me in this poll. This product-change will have far-reaching consequences. Anyone who has tried .NET, perhaps thinks in a second step, that Microsoft SQL Server might be a good alternative to the Oracle database. In this way, Oracle not only loses its loyal customers in the forms front-end, but also in the core business.

I conclude this article with the knowledge that we must have no fear in the next 10-15 years with our good old Oracle Forms! I look forward to 2012 to participate in the beta testing of Forms 12, if Oracle starts a new big betatest campground in Redwood Shores as in 2007.

Just use Forms
Gerd

Here starts the poll

Back to part 8 of the article

Result of the 2011 Oracle Forms poll - part 8

2011-08-29T09:43:00.003+02:00

What will the future bring?

What is the roadmap of Oracle Forms? All features that are mentioned below, are subject to Oracle's safe harbour statement

In other words, we may look forward to most features with reasonable certainty. If one of them now should not be installed, it will (hopefully) in the next version.

New Features Forms 11g Release 2:
- Integration of the forms runtime in Oracle Access Management - an alternative Single Sign On
- Easier and better installation of Forms 11g on the Web Logic Server

New Features Forms 12:
- Oracle Real-User-Experience-Integration for better testing environment
- Enhancements in the Forms Builder
- Better integration in the Oracle Enterprise Manager
- Better support of new OS versions (32+64 bit)

To be continued with the summary

Back to part 7 of the article

Result of the 2011 Oracle Forms poll - part 7

2011-08-26T09:14:00.002+02:00

Question 6: Which new Features did you wish for Forms 12?

Most named topics were:

- reintroduction Client/Server
- integration of a modern pl/sql editor
-- with auto-suggest, formatter and code completion
-- enhanced syntax highlighting
- integration of pl/sql-refactoring
- easier installation
- forms-blocks build on data grids
-- access to all records at the same time
-- switching columns
-- increase and decrease column width
- better integration of reports and java beans
- creating objects at runtime
- drag and drop
- zooming formlayout

To be continued with What will the future bring?

Back to part 6 of the article

Result of the 2011 Oracle Forms poll - part 6

2011-08-25T08:58:00.002+02:00

Question 5: What did you think about the future of Oracle Forms?

That was not a question with an easy Yes or No. But 70% of all answers gave positive feedback.

Some answers were extreme positive or negative: For example: "More stable that all java-frameworks", "Forms Web is bad, as Client/Server full of grandeur", "There are no clever alternatives" or "Forms will die, whatever we do in the next 5, 10 or 20 years".

In most answers they repeated the appeal to Oracle, to develop Forms further: "It must be more done for Forms! Then we have a future", "Forms is our most important development-tool for dialog-programs and we hope, that Oracle will maintain it for a long time".

In many cases they said, that Oracle Forms is the most productive development environment for Oracle Databases: "In our opinion we think, that there is no other product, were you can develop in comparable time such a good output"

To be continued with Question 6: Which new Features did you wish for Forms 12?

Back to part 5 of the article

Result of the 2011 Oracle Forms poll - part 5

2011-08-24T09:24:00.002+02:00

Question 4: Are you thinking about a replacement?

One-third of all forms-applications will be replaced with Java, ADF and .NET.

Very interesting: 40% of all customers, who leave Forms, went to Microssofts .NET. Oracle's ADF is only on rank 3.

To be continued with Question 5: What did you think about the future of Oracle Forms?

Back to part 4 of the article

Result of the 2011 Oracle Forms poll - part 4

2011-08-23T11:15:00.001+02:00

Question 3: Are you thinking about a migration?

More than two-thirds of all forms-applications will be migrated to Forms 10g or 11g in the next years.

That's good news. It means, that all those unsupported old forms-versions went to a modern three tier architecture.

But what about the 20 percent of the applications, which are still running under Forms 6i Client/Server? Maybe the next question is the answer:

To be continued with Question 4: Are you thinking about a replacement?

Back to part 3 of the article

Result of the 2011 Oracle Forms poll - part 3

2011-08-22T09:43:00.001+02:00

Question 2: Since how many years did you work with Forms?

Some answers like "I work with Forms since 2.3" are matched to "24 years" :-)

The average of all forms-developers work sind 15 years with the toolset. That is an amazing amount of time.

To be continued with Question 3: Are you thinking about a migration?

Back to part 2 of the article

Result of the 2011 Oracle Forms poll - part 2

2011-08-19T10:48:00.005+02:00

Question 1: With which forms-version did you work today?

Nearly 40% of all customers are working with the good old Forms 6i Client/Server. They love their Client/Server and don't want to go to the expensive web-servers of Forms 10g and 11g.

Less than 10% are working with the new Weblogic Server and Forms 11g. That's quite few. I hope I get more infos about that topic, when I start the next poll in 2012.

To be continued with Question 2: Since how many years did you work with Forms?

Back to part 1 of the article

Result of the 2011 Oracle Forms poll - part 1

2011-08-17T09:52:00.003+02:00

I realized the last months, that it is not that easy to find some hours in one piece to write down my results of the Oracle Forms poll.

So I decided to split it into parts and here is the first one:

Why did I start the Oracle Forms poll ?

Ten years ago I heard the first rumors about "Forms is dead and modern software engineers should use Java". It was the time of Forms 6i Client/Server and Forms 6i Web on the Internet Application Servers.

Some years later we got Forms 9i with a lot of new features and 2006 came Forms 10g, which was the stablest version based on OC4J in the Oracle Application Server. But the rumors didn't end.

Oracle launched in 2007 a huge 5-day-betatest in Redwood Shores. I got an invitation and had a close look at the newest versions of all products including the new Oracle Forms 11g. That was amazing!

The new Forms 11g worked so stable in the betatest, that I thought, the production version cannot be far away. One year later Oracle purchased BEA. The new Weblogic Application Server was now strategic and all products had to run on it. This was the reason, why Oracle Forms needed two more years to get ready.

In those years, Oracle was pushing Java and ADF as the best way for developing applications.

That was the point, to hear, how the mood of the forms-developer and forms-projects today is. I started a poll and invited nearly 1000 customers to help me and find out, what they think about the future of Oracle Forms.

To be continued with Question 1: With which forms-version did you work today?

SQL Developer Data Modeler is now for free

2011-05-02T11:49:00.000+02:00

The old SQL Developer Data Modeler was not for free. It was expensive and this was a big problem for customers.

SQL Developer Data Modeler is now a free data modeling and design tool.

Read this

This is another good point for us Oracle Designer Fans to migrate one day to a new Data Modeling Environment from Oracle.

Thank you Oracle

SQL Developer 3.0 includes Data Modeler

2011-04-19T16:31:00.004+02:00

The new version of the SQL Developer is a big step. The data modeler is now integrated part of the toolset!

The first screenshot shows the differences between the new and old object navigator:

New topics are :
- Editioning Views
- Crossedition-Trigger
- Scheduler

A little enhancement in the user interface is for example the Close-Button in the tabs. They are permanent visible, not like in the old version, where you had to do a mouse-over, to see, where the buttons are.

The tables are automatically filtered, so that the recycle bin is excluded.

In the menus are new menuitems for the data-modeler and the new data-mining:

To start data modeling you didn't have to start another tool like in version 2.1. Only click on View->Data Modeler->Browser and start working.

You can open an existing model through File->Data Modeler->Open or create a new model from scratch via import from the data dictionary. Models are stored in their own directory plus a configuration-file with the extension DMD.

My next tests will be:
- generating scripts
- creating packages and sourcecodes in the data modeler

I hope, that all of these topics are big steps too!
Gerd

SQL Developer 3.0 is available

2011-03-30T16:27:00.002+02:00

Oracle's breaking news from yesterday :

Breaking News: Oracle SQL Developer 3.0 is now available for download (29 March 2011)

I'll test it the next days and I hope, that the Data Modeler now is integrated.

Oracle Forms poll "Forms 12 and beyond"

2011-02-28T14:46:00.001+01:00

6 weeks ago I wrote a posting about the big german poll I wanted to start.

5 weeks ago I invited nearly thousand companies in germany, austria and switzerland to attend my forms-poll.

The first results returned 2 minutes after starting the poll.

The last 2 weeks I used for reviewing and collecting the results, writing my article for the DOAG News and now the article is ready and goes into printing.

The result of the poll is very interesting. I cannot write here about it, because the DOAG News should publish the article first.

So we have to wait another 4 weeks till the April edition is published. Then I'll discuss the poll in detail here in my blog.

Gerd

Versioning forms and comparing sourcecode

2011-01-17T14:04:00.000+01:00

Comparing sourcecodes of different versions of the same FMB is very important while developing an application or during maintenance.

If you want to store all versions of your FMB's, then they are two ways:

Manual versioning

Each version of all files is stored under its name plus the versionnumber. E.g. emp_27.fmb, emp_28.fmb, ... In this case "emp" + "_27" + ".fmb".

Parallel to each version you store the textversion of the FMB, emp_27.txt, emp_28.txt and so on. You get the textversion in forms through Edit-Administration-"Object list report".

For viewing the changes between two versions you can use tools like Beyond Compare, my favorite compare tool.

Using Subversion, Git or another VCS

Using a version control software is the other approach. But here we have the problem, that storing the binary file in tools like Subversion didn't solve our origin problem, finding sourcecode differences between two versions. Storing the textversion of the binary FMB plus the forms FMB is here the best practice for the check-in.

If we now want to see the changes between two version, we can easily use the builtin features of the versioning tool:

just use it
Gerd

The future of Oracle Forms

2011-01-11T16:00:00.000+01:00

In the last days of 2010 the German Oracle User Group asked me to write an article for the april edition of their magazin.

First I thought, that I write a normal deep technique article like all the others in the last years.

But then I reconsidered and found a better topic: "The future of Oracle Forms"

This article has two main parts. The first one gives an outlook, what Oracle integrates in the new Oracle Forms 12 release and the second part is result of a german wide poll, which I start this week.

The poll should give us an overview, what the german forms developer think about their development tool, which version they actually use, how long they work with Forms and what they think about the future of Oracle Forms

I'm very interested in the results of this poll
Gerd

Upgrading IE in the local development environment

2010-09-27T09:17:00.000+02:00

If you ever have a stable running IE 6.0 or 7.0 for your Oracle Forms development and you think: Let's use the IE 8.0 or 9.0.

Then you may run into these problems:

After the IE 8 Installation you run your application through the Run-Button in the Forms-Builder and see this:

oh no... what an ugly URL you think...

Now we have to find the problem:

First point is, that the JInitiator isn't running against the IE 8 and we have to switch to an alternative. OK let's use the sun-plugin.

Second pitfall may be, that switching to the new "config=my_sunconfig" didn't run with the local forms developer, although you can run the main application on the Application Server.

This problem occurs, because you may forgot to check the formsweb.cfg on your local machine.

After changing the environmant to sun-plugin-parameter-values there might be another problem, but only in seldom cases: Under edit-preferences you can set the path for your Internet Explorer. Check, if this is correct, or change it to the new IE 8 destination.

And now have fun with your IE 8
Gerd

New Layout

2010-07-09T21:32:00.000+02:00

I updated the templates a little bit of my talk2gerd. These are the new features:

Followers

- all followers of my blog are welcome here

Labels

- here you can see all the different labels I used in my blog. Click on a label and you see only the postings with that label on it.

have fun
Gerd

New Statement of Direction, May 2010

2010-07-02T11:28:00.004+02:00

This is the new Statement of Direction from May 2010. (My last SoD-Post)

Here are some links to older Statement of Directions:

SoD Oracle Fusion 2010 / 05
SoD Forms 2009 / 07
SoD Forms 2008 / 07
SoD Forms 2007 / 11
SoD Forms 2005 / 09
SoD Forms 2005 / 05
SoD Forms 2005 / 03
SoD Forms 2004 / 06

I just submitted "Forms 11g and the iPhone" for the DOAG Conference 2010

2010-06-09T15:53:00.001+02:00

In some weeks the Call for Papers for the German Oracle User Group (DOAG) Conference 2010 ends.

My plan is to hold a presentation about Forms 11g and the iPhone.

The main topic of the presentation is, how you can extract modules from big Oracle Forms Applications and recreate them in a mobile version on the iPhone.

The presentation-testcase shows, how much more efficient it is to have a time-reporting on your mobile device versus the old technique, where this app was part of a big legacy application.

Another topic is to show, how you can communicate bidirectional between Forms 11g and the iPhone App. Using Advanced Queueing in Forms and Push Notifications in the App is the solution.

5 months to go and I hope I get a slot in the conference.
Gerd

Migrate your charset to UTF8, annotation to step 3

2010-05-05T17:12:00.004+02:00

After migrating to UTF8 you may have problems with your report-server. This is, when your reports cannot be displayed and internally the server throws a "Canceled as server is shutting down"

With UTF8 you need a bigger cache-size in "report server-parameter". Easiest way, when you run into problems, double up the value of the parameter.

Try it
Gerd

Back to part 3 of the article

Migrate your charset to UTF8, step 3

2010-04-28T10:13:00.010+02:00

Applications without Reports are useless :-)

So we have to change some things in the Reports Environment to enable UTF8 in PDF-Output.

First

you have to edit in your OAS Report-Server the report-config:

Add one "environment id" per section. A section is in this example development, test and production. After that, each section has defined it's own NLS_LANG and report-path.

Second

You can use the new sections through Forms. Before starting the report you add another parameter to the paramlist:

Add_Parameter (V_ParamListe, 'ENVID', TEXT_PARAMETER, 'NLS-PROD');

Now you can control the NLS_LANG of your reportserver.

Third

Last checkpoint for your reports is the correct mapping between your fonts and your TrueType-Fonts (in this case we have a windows-system)

Edit the file "uifont.ali" in the directory \tools\common and add the following lines in the section [ PDF:Subset ]

Arial..Italic.Bold..             = "c:\windows\fonts\arialbi.ttf"

Arial...Bold..                   = "c:\windows\fonts\arialbd.ttf"

Arial..Italic...                 = "c:\windows\fonts\ariali.ttf"

Arial.....                       = "c:\windows\fonts\arial.ttf"



"Courier New"..Italic.Bold..     = "c:\windows\fonts\courbi.ttf"

"Courier New"...Bold..           = "c:\windows\fonts\courbd.ttf"

"Courier New"..Italic...         = "c:\windows\fonts\couri.ttf"

"Courier New"                    = "c:\windows\fonts\cour.ttf"



"Times New Roman"..Italic.Bold.. = "c:\windows\fonts\timesbi.ttf"

"Times New Roman"...Bold..       = "c:\windows\fonts\timesbd.ttf"

"Times New Roman"..Italic...     = "c:\windows\fonts\timesi.ttf"

"Times New Roman"                = "c:\windows\fonts\times.ttf"

In this example windows has its fonts in the directory c:\windows\fonts\

have fun
Gerd

To be continued with Migrate your charset to UTF8, annotation to step 3

Back to part 2 of the article

Migrate your charset to UTF8, step 2

2010-04-07T12:25:00.005+02:00

Now, after changing the NLS to UTF8 we may have problems with hotkeys.

That's because the nls-change to UTF8 will now use a different resource-file. Instead of fmrweb.res the fmrweb_utf8.res is used. The resource-files are located in the forms-home-directory.

If you have used "term" in your formsweb.cfg - otherparams, then you point hardcoded to a specified file. Here you have to change the value of the parameter or you have to exchange the content of the file.

e.g. changing the value of the parameter:
old: otherParams=term=\fmrweb.res
new: otherParams=term=\fmrweb_utf8.res

After this changes you can use the same hotkeys like before

To be continued with Migrate your charset to UTF8, step 3

Back to part 1 of the article

Migrate your charset to UTF8 in 3 steps

2010-03-31T09:41:00.006+02:00

What do we have to do in our forms application, when the database in the backend is upgraded from a single-byte charset to a UTF8-charset.

First Step

Make a backup of your forms-sources, your local forms installation and your application server configs.

After that change the NLS_LANG in your registry. You can create a backup of the parameter, through creating a new parameter named "NLS_old" with the value of the old NLS_LANG-value. The new value of NLS_LANG is for example "GERMAN_GERMANY.UTF8". This change is for your local forms builder, when you recompile your forms sources.

Now we can edit our local default.env. If there is a line with the NLS_LANG, then change the value to the UTF8-value (see above) or create a new line with the NLS_LANG. This change is for our local OC4J instance only.

Do the same change on the application server (use the OEM for edits)

These changes were the basic environment-changes we need for UTF8.

To be continued with Migrate your charset to UTF8, step 2

The iPhone Developer Conference was great

2009-12-03T08:50:00.003+01:00

The last two days, when I visited the conference, I got tons of new infos and ideas around the iPhone and how to create applications on it.

I think, I'll create a showcase for iPhones, where you can manipulate data through an App and parallel you see those updates automatically in a forms application. Using the newest version Forms 11 with AQ's is the basis of the showcase.

Gerd

iPhone Developer Conference in Cologne

2009-11-30T16:28:00.003+01:00

Tomorrow starts the german's iPhone Developer Conference in Cologne.

I'll have a close look, if this is the new way of creating apps. And maybe I create the first app which can interact with Oracle Forms :-)

Let's see
Gerd

Blogging via Google Wave - First Test through email

2009-10-19T13:49:00.001+02:00

Google wave is cool. Each day I find new things in this platform.

And now is the time to test the connectivity between google wave and my blog.

In this first test I write an automated email to the blogger-account. And the second test afterwards is from within the wave

I need luck
Gerd

The presentation for the DOAG conference 2009 is finished

2009-09-15T18:03:00.005+02:00

The title is: "Shout out: Oracle Designer vs. SQL Developer Data Modeler".

I'll demonstrate in the presentation the advantages and disadvantages of both tools. I base the demonstration on an EMP / DEPT example, which I design live.

Chapters of the white paper are

- Domains
- ER-Design
- Transfer ER to DataModel
- Data Modeling
- Generating DDL-Scripts
- Reverse Engineering
- Guaranteed Future

The day after the presentation I'll upload the paper to my download-portal on google-code

Gerd

Forms Debugger - hidden Feature

2009-08-31T08:18:00.008+02:00

The Forms 10g debugger is much better than the Forms 6i debugger. He is completly new written and has the ability to debug remote. So you can look into a running session of an user and search for errors. This is in the new web-environments a powerful method to find problems.

And that's only one aspect of the new debugger.

The debugger works in two different modes: The stop-mode is well-known, because you can use there all functions of the debugger. He is active, while you investigate the code. You can work in this work, through a breakpoint e.g.

The run-mode is the other one. He can be found only, while the form is running in the foreground.

And at that point you can press the break-button, which is one of the most interesting features in the debugger. It creates a breakpoint dynamically in the next line of code, which will be executed.

So, if you press the button, nothing happens. If you then return from the Forms Builder back to the runform, the next event in the form starts the debugging-process. And then the debugger shows you the first line of code in that trigger, which will be executed next. So you don't have to think to much, where to set breakpoints and so on... just use the break-button.

That's my tip of the day
Gerd

Forms 11g is available on OTN

2009-07-04T10:04:00.005+02:00

Go to Oracle Fusion Middleware 11g and click on Downloads

In the list of available tools, search for "Portal, Forms, Reports and Discoverer"

then relax, while downloading 2.1 GB and enjoy the new Oracle Forms 11g R1.

Available on Windows and Linux. Solaris, HP and IBM is "Coming soon"
Have fun
Gerd

Oracle Fusion Middleware 11g launched

2009-07-03T14:16:00.002+02:00

Charles Phillips launched the Oracle Fusion Middleware 11g two days ago in Washington D.C.

So now the Fusion stack is out and more and more products can use the new middleware. That is a good info for us, where we were waiting for the new platform for our Forms 11g.

Charles Phillips spoke about Key Partners, which helped him during the development of Fusion with beta testing and trainings. And here are two images from his presentation where we found our OPITZ CONSULTING logo on his main presentations:

Let's hope, that Forms 11g is launched in the next weeks
Gerd

Disabling a Trigger

2009-06-29T08:30:00.004+02:00

If you have a trigger and you want to quickly disable him, then just use the "_"-technique :

In this case we have a Key-Create-Record-Trigger which should be disabled. Rename the "-" to "_" in the name of the trigger and from that point on the trigger is a user-named-trigger, which won't fire through an event.

Important: You can't rename the trigger to "KEY-CREREC2" or something else. The hyphen is in forms reserved for event-triggers.

To activate the trigger rename it back to "-" and that's all.

Have fun
Gerd

AdSense test finished

2009-06-18T12:30:00.003+02:00

These two weeks with Googles AdSense were very interesting. You see each day your income in thousands of dollars (oh, I mean cents... and not thousands :-) ) and there are many tools to show up reports, when somewhere clicked on the Ads, and so on...

But, as I said, this is only a short test for myself and from now on the blog is Ads-Free, as before :-)

have fun
Gerd

AdSense in my blog (only for a short time)

2009-06-04T09:46:00.004+02:00

Today I found a new tab on the blogger-UI of this google-blog. It's named "Monetize" and so they want, that all the bloggers think, they get rich, when they start Google's AdSense program. I know, that I not get rich, because I didn't have 2 million clicks the day :-)

But I start this AdSense for a while to get infos about this technique and how Google implemented the Ads into the blogs.

Please don't think, that I want to nerve you. My blog will go on and after the test I give you a little feedback, how this Ads work and how they have impact on the user-frequency.

Let's start the test!
Gerd

And the winners are:

2009-05-29T15:55:00.003+02:00

In adaption of Grants Posting I copy here a list of the 5 best Oracle Forms Java Beans of the April 2009 Contest:

18,8% Md. Mehbub Islam: Scanner Bean
17,5% Hafed Benteftifa: FRITE (Forms Rich Text HTML Editor)
17,5% Vijaynath Nair: Autocad Integration Bean
16,2% Francois Degrelle: Carousel Bean
16,2% Lionel Lasserre: Planning Bean

If you have a minute then use this links and see the astonishing beans they have created.

It's phantastic to see, that all of this run in Oracle Forms and much more. So you can modernize your application so easily and you have to integrate only some little Java Beans.

Thanks to all of those, who attend at the contest
Gerd

Data Modeling White Paper

2009-03-25T09:14:00.003+01:00

It looks like that the Oracle SQL Developer Data Modeling Tool can grow up to the new Oracle Designer.

And here is a good overview for all of us who want to know more about the data-modeling-capacities SQL Developer Overview

have fun
Gerd

Filtering data through multiple rows

2009-02-27T09:25:00.008+01:00

Lets say we have created a form on the table EMP and want to filter the data, so that only the Employees from Department 20 and 30 are displayed. The best UI for the user is a filter-block, where you see all departments and can mark them.

In this filter we click on the checkboxes for department 20 and 30:

The button Query data (Abfrage starten) starts a query on the emp-block.

What do we need for this technique?

-) A CONTROL-block with an item TI_Filter_String (char, 2000)
-) A checkbox CB_Filter in the DEPT-block (numeric, 0-unchecked, 1-checked)

The button, which starts the query, needs a WHEN-BUTTON-PRESSED:


:Control.TI_Filter_String := ';';
go_block ('DEPT');
first_record;
LOOP
  IF :DEPT.CB_Filter = 1 THEN
    :Control.TI_Filter_String := :Control.TI_Filter_String || :DEPT.DEPTNO || ';';
  END IF;
  IF :SYSTEM.LAST_RECORD = 'TRUE' THEN 
    EXIT;
  ELSE
    Next_Record;
  END IF;
END LOOP;
go_block ('EMP');
execute_query;

The EMP-block needs this default-where:


:Control.TI_Filter_String LIKE '%;' || DEPTNO || ';%'

The trick is, that each deptno from the filter-string can be found using the LIKE %;...;% - technique:

Have fun with this little trick!
Gerd

Forms Function Numbers

2009-01-19T09:22:00.012+01:00

Editing the fmrweb.res to assign hotkeys to Forms Functions is not that easy. The syntax is described inside the file and there are no problems with the parameters JFN, JMN, URKS and URFD. But what IDs are used for FFN ?


#    JFN : JMN : URKS : FFN : URFD   (whitespace ignored)
#
#      JFN = Java function number
#      JMN = Java modifiers number
#     URKS = User-readable key sequence (double-quoted)
#      FFN = Forms function number
#     URFD = User-readable function description (double-quoted)

Here it the complete list:


  FFN Description
-----+---------------
    1 Next Field
    2 Previous Field
    3 Clear Field
    4 Left Arrow
    5 Right Arrow
    6 Up Arrow
    7 Down Arrow
    8 Goto Beginning of Line
    9 Goto End of Line
   10 Goto Extreme Vertical Up
   11 Goto Extreme Vertical Down
   12 Scroll Up
   13 Scroll Down
   14 Scroll Left
   15 Scroll Right
   16 Delete Character Left
   17 Delete Character Right
   18 Delete Line
   19 Move Cursor Left
   20 Move Cursor Right
   21 Scroll-Up
   22 Invoke Editor for Field
   23 Delete Character  ??
/  23 Insert Line       ??
   24 Search
   25 Toggle Insert/Replace Mode
   26 Select Item
   27 Return
   28 Invoke Menu
   29 List of Values
   30 Help
   31 Redraw Screen
   32 Exit
   35 Show Keys
   36 Accept
   61 Next Primary Key
   62 Clear Record
   63 Delete Record
   64 Duplicate Record
   65 Insert Record
   66 Next Set
   67 Next Record
   68 Previous Record
   69 Clear Block
   70 Block Menu
   71 Next Block
   72 Previous Block
   73 Duplicate Field
   74 Clear Form
   75 Enter
   76 Enter Query
   77 Execute Query
   78 Display Error
   79 Print
   80 Count Query
   81 Update Record
   82 User Defined Key 0
   83 User Defined Key 1
   84 User Defined Key 2
   85 User Defined Key 3
   86 User Defined Key 4
   87 User Defined Key 5
   88 User Defined Key 6
   89 User Defined Key 7
   90 User Defined Key 8
   91 User Defined Key 9
   92 Clear Eol
   95 List Tab Pages
11022 Accelerator Key 1
11023 Accelerator Key 2
11024 Accelerator Key 3
11025 Accelerator Key 4
11026 Accelerator Key 5
11027 Accelerator Key 6
11028 Accelerator Key 7
11029 Accelerator Key 8
11030 Accelerator Key 9
11031 Accelerator Key 10

Have fun with the Forms Function Numbers !
I'll check as soon as possible, why the Nr. 23 is duplicated in the notes from Oracle...
Gerd

Tab-Canvases and F2

2008-12-17T10:25:00.004+01:00

I just found an interesting undocumented feature:

While the cursor is inside a tab-canvas you can press F2

Then you see a little list-item in the upper-right corner of the tab-canvas. There you can move up, down and press Enter or Return.

This starts the WHEN-TAB-PAGE-CHANGED-trigger. Here you can do whatever you want. For example a navigation to a block and starting a query.

Have fun
Gerd

New Statement of Direction, October 2008

2008-11-20T09:47:00.005+01:00

This is the new Statement of Direction from October 2008. (Link to my last SoD-Post)

What's different?

The graphical timeline is erased in the document. And nothing else has changed!

Here are some links to older Statement of Directions:
SoD 2008 / 07
SoD 2007 / 11
SoD 2005 / 09
SoD 2005 / 05
SoD 2005 / 03
SoD 2004 / 06

SQL Developer Data Modeling

2008-10-16T13:51:00.013+02:00

Here are the first screenshots of the new data modeling tool, called Oracle SQL Developer Data Modeling (OSDM), which is part of the SQL Developer:

First of all I tested the capability of a table capture. Few clicks and he grabbed my datamodell and placed it in the relational-section of the OSDM.

Then you can "Engineer to Logical Model": After that you have an ER-Model:

Here is the link to the download-area on OTN: Oracle SQL Developer Data Modeling

I'll test more in the next days and write down the next experiences
Gerd

Views based on Year, Month and Day

2008-10-15T09:47:00.004+02:00

While developing applications I need often list-of-values, which have a result-set of years, months or days.

You can hard-code those selects each time in each form, but much more elegant is to create views, which do the whole work.

Here are 3 views for years, months and days:


CREATE OR REPLACE FORCE VIEW YEARS_V 
  (YDATE) AS
SELECT add_months (trunc (sysdate, 'YYYY'), 12 * (50 - Level))
  FROM Dual
CONNECT BY Level <= 100;

CREATE OR REPLACE FORCE VIEW MONTHS_V 
  (MDATE) AS 
SELECT add_months (trunc (sysdate, 'MM'), 500 - Level)
  FROM Dual
CONNECT BY Level <= 1000;

CREATE OR REPLACE FORCE VIEW DAYS_V 
  (DDATE) AS 
SELECT trunc (sysdate) + 15000 - Level
  FROM Dual
CONNECT BY Level <= 30000;

Those Views helps us to Select Data for the


- actual year  +/- 50 years
- actual month +/- 500 months
- actual day   +/- 15000 days

In a Record-Group you can use those views:

LOV of the next 10 years


SELECT YDATE
  FROM Years_V
 WHERE YDATE BETWEEN trunc (sysdate, 'YYYY') 
             AND add_months (trunc (sysdate, 'YYYY'), 10*12);

LOV of the last 30 and the next 10 days


SELECT DDATE
  FROM Days_V
 WHERE DDATE BETWEEN trunc (sysdate-30) AND trunc (sysdate+10);

Try it
Gerd

New Oracle Designer ? - part 2

2008-09-26T15:36:00.002+02:00

I just found an interesting link to the first pictures of the new "Oracle Designer".

The data modeling functions are implemented in the SQL Developer.

Have fun reading Jared's post: Data modeling with SQL Developer

New Statement of Direction, July 2008

2008-08-05T12:35:00.011+02:00

Oracle announced this Statement of Direction some days ago.

What's new ?

1) A link to the Oracle lifetime-support whitepaper. There is a redirect to this URL.

2) And 4 years more support (minimum). In the last statement of direction 2013 was the minimum support-date. Now it is 2017!

Finding all Items in the layout-editor at once

2008-07-21T15:50:00.004+02:00

To find all items, which are displayed in the layout editor at once in the Object Navigator, press Ctrl+A, for select All.

After the Ctrl+A you have all Objects marked in the Tree:

have fun
Gerd

New Oracle Designer ?

2008-07-02T16:47:00.004+02:00

I can't believe it...

Oracle SQL-Developer is announcing modeling-support in an upcoming release.

Here are the top features:


Database Data Modeling to support:

    * Designing logical Entity Relation Diagrams
    * Building physical schema designs
    * Generating and executing DDL scripts
    * Reverse and forward engineering of existing relational data structures
    * Data domain administration
    * Naming standardization
    * Model formatting (font, colors)
    * Importing data models from CA Erwin and Oracle Designer
    * Compare and merge facilities
    * Multiple database support
          o Oracle Database
          o DB2 (Mainframe & UDB)
          o Microsoft SQL Server
    * Logical and physical multi-dimensional modeling
    * Object relational Data Types
    * Spatial Modeling
    * Multi-level logical and physical design environments
    * Model validation rules
    * Offline (file based) and Repository based modeling

what a wonderful news. The old designer is not the best choice today, since it got the codefreeze years ago.
Gerd

Talk2Gerd in a new Look

2008-07-01T13:23:00.007+02:00

Those of you with sharp eyes can see the golden gate bridge to the left. I shot the picture Sept. 2005, Shorebird Park, Oakland.

Faster compilation in Forms Builder

2008-06-26T07:40:00.004+02:00

You can speed up the time for a "Compile All", when you close all nodes in the Object Navigator, so that the form shows only the name of the form. Then you press Ctrl+Shift+K for Compile All.

Compile-Times of a normal pl/sql-library with 70 program units


closed nodes :  2 sec
open nodes   : 18 sec

Compile-Times of a big pl/sql-library with 130 program units


closed nodes :  2 sec
open nodes   : 34 sec

Compile-Times of a medium form with 14 blocks


closed nodes :  3 sec
open nodes   : 12 sec

Compile-Times of a big form with 24 blocks and much sourcecode


closed nodes :  6 sec
open nodes   : 37 sec

With this little trick you can compile really fast !
try it
Gerd

Compile or Compile All ?

2008-06-19T10:31:00.007+02:00

What is the best way to compile a form? Before I answer this question here are some explanations:


Compile Incremental      :         Ctrl + K
Compile All              : Shift + Ctrl + K
Compile Module (Generate):         Ctrl + T
Run                      :         Ctrl + R

In the older versions of Forms the Compile Module was known as Generate. I prefer this, because the Ctrl+T generates the FMX.

During my daily work I open forms and maintain them. If I use the Ctrl+T to generate the FMX, then Oracle Forms starts implicitly a Compile Incremental before the Generate.

And that's the problem. In 9 of 10 cases the generated FMX is OK, but sometimes the automatical Compile Incremental didn't work properly. It results in non-reproducable errors at runtime.

My solution for this problem is:

After opening a form I start immediately a Compile All. Each Incremental Compile and each Generate now works without runtime-problems.

Try it
Gerd

Forms Shutdown

2008-06-05T12:00:00.016+02:00

In the article forms-startup I wrote last year how to hide the browser-window, while starting a forms-application. Another problem is: How can we close the browser-window after exiting the forms-application. Here is my favorite solution:

1) Create a new html-file e.g. close.html in your html-directory. This is the main routine, which closes the browser-window.


< BODY onLoad="window.close();" >

2) Set the formsweb.cfg parameter HTMLbeforeForm in the server-directory. This eliminates the security-question "Do you want to close the browser-window".


HTMLbeforeForm=< SCRIPT LANGUAGE="JavaScript" >window.opener = top;< /SCRIPT >

3) The form, which closes the main-application needs a POST-FORM-trigger. /forms/html is a virtual-directory, which points to the close.html.


web.show_document ('/forms/html/close.html', '_self');

Used directories:


< DevSuite-Home >      : Your Developer-Suite Home Directory
html-directory         : < DevSuite-Home >\tools\web\html
server-directory       : < DevSuite-Home >\forms\server
virtual-html-directory : /forms/html      defined in forms.conf

Many thanks to Duncan Mills, Frank Nimphius and Richard Squires, who posted this code in the OTN years ago.

Pro) This technique works on IE 6 and IE 7, tested with JInitiator and Sun-Plugin.
Contra) No Firefox-Support. Since Firefox 2.0 it isn't allowed to close a window via JavaScript.

Important: The limitations of this blog forced me to write blanks after each "<" and before each ">". Don't write them, when you use this technique !

have fun
Gerd

Sourcecode-Formatting in the OTN-Forum

2008-06-03T10:29:00.013+02:00

Nearly each day I see unformatted code in the OTN-Forum. Why?

The problem is, that many poster don't know, how to format sourcecodes. Example:


/*
|| Selecting the sum from all sub-order's
*/
if :control.sub_order_id is not null then
select sum (value)
into :control.sum
from sum_orders
where order_id in (select order_id 
from sub_orders 
where sub_order_id = :control.sub_order_id
and sub_order_type = 'ONLINE');
else
:control.sum := 0;
end if;
/*
|| now we use the sum in the ...
*/

Who can read that? I love puzzling, but not in sourcecodes. How can we solve this problem?

Use [pre] before your sourcecode and [/pre] after the sourcecode. Then it is readable like your original code:


[pre]
...
if :control.sub_order_id is not null then
  select sum (value)
    into :control.sum
...
[/pre]

and here is the solution of the above example after using the preformat-tags:


/*
|| Selecting the sum from all sub-order's
*/
if :control.sub_order_id is not null then
  select sum (value)
    into :control.sum
    from sum_orders
   where order_id in (select order_id 
                        from sub_orders 
                       where sub_order_id = :control.sub_order_id
                         and sub_order_type = 'ONLINE');
else
  :control.sum := 0;
end if;
/*
|| now we use the sum in the ...
*/

This is readable code at it's best!

Try it
Gerd

Open-Form and the Exit-Strategy (2)

2008-06-02T12:00:00.005+02:00

Sometimes you use your source-codes since years and think - there is no easier, better and smarter way to do something, like the way in that piece of code.

So here. After writing and testing my last post everything runs well!

Today I refactored some parts of my exit-strategy code and created this much easier solution (normally this is not the goal of refactoring !):

all you need is a

KEY-EXIT in the startform:


COPY ('TRUE', 'GLOBAL.EXIT_IMMEDIATE');
EXIT_FORM (no_validate);

and a WHEN-FORM-NAVIGATE in all other forms


DEFAULT_VALUE ('FALSE', 'GLOBAL.EXIT_IMMEDIATE');
IF :GLOBAL.EXIT_IMMEDIATE = 'TRUE' THEN
  EXIT_FORM (no_validate);
END IF;

Now we have a chain reaction, when exiting the startform. Each closing form will let the focus jump to another open form. Then the WHEN-FORM-NAVIGATE triggers and closes this form too. Because the global variable was set to AUTOCLOSE = TRUE.

After the last form the whole application is closed and that's the best way to close an entire application.

If some of those forms started other forms through call_form, then you have to use the WHEN-WINDOW-ACTIVATED, because the WHEN-FORM-NAVIGATE won't trigger while getting the focus.

Isn't that a really easy solution?
Have fun with it
Gerd

Open-Form needs an Exit-Form-Strategy

2008-05-26T12:00:00.010+02:00

This article is for all of us, who work with OPEN_FORM instead of CALL_FORM.

OPEN_FORM has benefits and problems. The user can easily switch between all open forms, but when he closes one of the forms, he only closes the current form.

Solution: Normally all applications have a startform, from where all other forms were opened. When the user is in this form and he presses exit-form, then he wants to close the whole application and not only the startform. In this case we need a method, which loops through all open forms and closes them.

Finding all open forms is a hard job. So, the best way is to store all of them in a global list named GLOBAL.OPEN_FORMS. Values are separated by semicolons, like: ;STARTFORM;EMP;DEPT;

We need a PRE-FORM and a POST-FORM-trigger for all forms, except the startform. The Pre-Form appends the name of the current form to the list, the Post-Form erases an entry.

PRE-FORM :


DEFAULT_VALUE (';', 'GLOBAL.OPEN_FORMS');
:GLOBAL.OPEN_FORMS := :GLOBAL.OPEN_FORMS || 
                      :SYSTEM.CURRENT_FORM || ';';

POST-FORM :


:GLOBAL.OPEN_FORMS := REPLACE (:GLOBAL.OPEN_FORMS, 
                               ';' || :SYSTEM.CURRENT_FORM || ';', 
                               ';');

Now we have the names of all open forms in a global list.

The KEY-EXIT trigger in the startform needs a loop, which closes all other forms and at then startform.

KEY-EXIT in startform :


DECLARE
  V_Form         VARCHAR2 (30);
BEGIN   
  One_Time_Timer.Initialize ('EXIT_STARTFORM');
  DEFAULT_VALUE (';', 'GLOBAL.OPEN_FORMS');
  WHILE :GLOBAL.OPEN_FORMS != ';'
  LOOP
    V_Form := Substr (:GLOBAL.OPEN_FORMS, 
                      2, 
                      InStr (:GLOBAL.OPEN_FORMS, ';', 1, 2) - 2);
    COPY ('J', 'GLOBAL.EXIT_IMMEDIATE');
    GO_FORM (V_Form);
  END LOOP;
END;

The startform is closed through WHEN-TIMER-EXPIRED :


IF One_Time_Timer.Get_Value = 'EXIT_STARTFORM' THEN
  EXIT_FORM (no_validate);
END IF;

All other forms need a WHEN-FORM-NAVIGATE trigger:


DEFAULT_VALUE ('N', 'GLOBAL.EXIT_IMMEDIATE');
IF :GLOBAL.EXIT_IMMEDIATE = 'J' THEN
  EXIT_FORM (no_validate);
END IF;

Now you have a powerful autoclose-method for all opened forms.

Have fun
Gerd

Remember the One-Time-Timer article or use this packages:


PACKAGE Const IS
  gbl_One_Time_Timer CONSTANT VARCHAR2 (61) := 
    upper ('global.One_Time_Timer');
END;

PACKAGE One_Time_Timer IS
  FUNCTION Get_Value RETURN VARCHAR2;
  PROCEDURE Initialize (P_Event IN VARCHAR2);
END;

PACKAGE BODY One_Time_Timer IS
  FUNCTION Get_Value RETURN VARCHAR2 IS
  BEGIN
    Default_Value (NULL, Const.gbl_One_Time_Timer);
    RETURN (NAME_IN (Const.gbl_One_Time_Timer));
  END;
  
  PROCEDURE Initialize (P_Event IN VARCHAR2) IS
    tm_id   timer;
    tm_name VARCHAR2 (30) := 'ONE_TIME_TIMER';
  BEGIN
    tm_id := Find_Timer (tm_name);
    IF ID_Null (tm_id) THEN
      tm_id := Create_Timer (tm_name, 10, NO_REPEAT);
      COPY (p_Event, Const.gbl_One_Time_Timer);
    END IF;
  END;
END One_Time_Timer;

Forms 11g - Poll

2008-04-14T17:52:00.002+02:00

After writing the last posts about Forms 11g, I'm very interested in getting your opinion, which of this new features is so important, that you directly include it into your daily work.

And: What other feature do you really need in the next patches and releases?

Please write them as comments to this post.

Thanks
Gerd

Forms 11g new features: Javascript-API

2008-03-26T12:00:00.000+01:00

Forms 11g allows the direct communication between the generic java-applet in the browser and the world around. The new JavaScript-API implements this functionality.

In Forms 11g we have a new trigger, system-variables and built-ins for the communication with the JavaScript-API.

The trigger WHEN-CUSTOM-JAVASCRIPT-EVENT fires each time, when JavaScript raises an event to forms. In the trigger we can use the payload which is stored in two system-variables. system.javascript_event_name and :system.javascript_event_value.

Informations, which were transfered from HTML to Forms, can be easily used:

In this little example we transfer in the payload the event-name "NewForm" and in the event-value the name of a form. The data is transfered from the internet-page in this way::


< INPUT id="outside_field_id">
< SCRIPT>
  function set_field (field_id, myValue) {
    document.getElementById(field_id).value=myValue;
  };
  function clickEvent1()
  {
    document.forms_applet.raiseEvent("NewForm", "payload");
  }
< /SCRIPT>
< INPUT id="button1" type="button" onClick="void clickEvent1();" value="NewForm">

Internally the method raiseEvent of the class forms_applet is used. The applet's name has to be assigned in the formsweb.cfg to the parameter applet_name.


applet_name=forms_applet

Forms can communicate bi-directional with the HTML. Therefore we can use the new built-ins web.javascript_eval_expr and web.javascript_eval_function.


 web.javascript_eval_expr
    ('document.getElementById("outside_field_id").value="' ||
     :control.ti_inside || '";');
  web.javascript_eval_expr 
    ('set_field("outside_field_id", "' || :control.ti_inside
     || '")');
  :control.ti_get_value := web.javascript_eval_function
    ('document.etElementById("outside_field_id").value');

This example-code fills in the HTML-page a field named "outside_field_id" through the built-in web.javascript_eval_expr. Two techniques can be used. Direct Assignment or the call of a javascript-function, e.g. „set_field“.
You can read field through web.javascript_eval_function. The returnvalue is the value of the corresponding field in the HTML, in this case "outside_field_id".

This is another example of how important the new features in Forms 11g are. Now it's possible for forms to communicate with the world outside the browser's applet!

LOV with splitted data

2008-02-08T08:44:00.000+01:00

I found an easy technique to visualize data in lov's like this:

Use multiple UNION ALL's to concatenate the data :


select '---new colleagues---' ename, NULL job, NULL hiredate
 from dual
UNION ALL
select ename, job, to_char (hiredate, 'DD.MM.YYYY')
 from emp where hiredate >= to_date ('01.07.1981', 'DD.MM.YYYY')
UNION ALL
select '---before 07/81---' ename, NULL job, NULL hiredate
 from dual
UNION ALL
select ename, job, to_char (hiredate, 'DD.MM.YYYY')
 from emp where hiredate < to_date ('01.07.1981', 'DD.MM.YYYY')

have fun and use it
Gerd

Hotkey F1 in Forms 10g

2008-01-11T13:16:00.000+01:00

Since Forms 10g we have problems with F1. He doesn't work like in the older versions.

The Key-Mapping for KEY-HELP e.g. is now Ctrl+H instead of the good old F1.

If you want to have it work like in Forms 6i, then you can change the fmrweb.res:

The internal ID of F1 is 112, the ID of KEY-HELP-Trigger is 30:


...
113  : 0 : "F2"             : 95 : "List Tab Pages"
112  : 0 : "F1"             : 30 : "Help"
72   : 2 : "Ctrl+H"         : 30 : "Help"

have fun
Gerd

The new german Talk2Gerd

2007-12-13T14:20:00.000+01:00

Today I learned from Patrick Wolf, how easy it is to duplicate a blog into another languages. This link here is now the german version of Talk2Gerd.

And last but not least, here is a picture of my company after snowfall... and we had a lot of snow in the last years:

Libraries: PLL vs. PLX

2007-12-12T12:59:00.000+01:00

One big problem while developing an application is the question: Should I use attached libraries as *.pll or *.plx?

Let's have a look at some cases:

A) You use pll+plx while developing the application. The Forms Builder uses the working directory C:\Forms and the FORMS_PATH has an additional path to the directory C:\Forms\Lib. The Sourcefiles have the name Lib.pll, the Menu.mmb and EMP.fmb.

If there is a generated Lib.plx in c:\forms or c:\forms\lib, then this is the version, which will be used at runtime.

This is the direction of how Forms searches for attached libraries:


1) If Lib.plx is in your working directory - take it
2) If not found, search in the FORMS_PATH for Lib.plx
3) If not found, search in the working directory for Lib.pll
4) If not found, search in the FORMS_PATH for Lib.pll
5) If the library can't be found - Forms raises an error

This is identical to menu-mmx and forms-fmx

If the plx in the local working directory is an older version than the pll, this will cause big problems while running the forms-application. Finding errors is very tricky, if the developer didn't look on the timestamps of the libraries.

B) You use only pll's in your application.

You have no problems while developing the application or at runtime. If a library in your working directory didn't exist, then the library can be found in the FORMS_PATH.

Summary:

I never use plx because I saw many projects run into the plx-trap.

Many developer say: plx is faster, but I never had performance problems with the pll's.

Other developer say: plx is only the compiled code, so nobody can steal my sourcecode or something valuable from within the code - in that case try this example:

Create a library with a package and this variable:


PACKAGE Const IS
  HiddenPW                     CONSTANT Varchar2 (100) := 'HiddenPW';
END Const;

Generate the plx and open it in an editor. Search for "HiddenPW". You'll find the name of the variable and the string associated to the var. That's not secure in my eyes. Only the sourcecode is hidden. I think, that a pll on an Application Server is secure enough.

If the application is standard software and not for free, then you must use the plx-technique and wrapped packages in the database.

DOAG Top News: Forms 11g

2007-12-11T14:55:00.000+01:00

The German Oracle User Group (DOAG) has launched a little Forms 11g note from me as their actual Top News.

I wrote some lines about the new features of Forms 11g and the huge capabilities in the new SOA world of tomorrow. Here is the translation:

Forms 11g unterwegs in Richtung SOA : Forms 11g's direction to SOA

After 6 years is this the first Forms Version which integrates new features into the Forms Builder. The two new important features found are:

The first one is the communication between the database and Forms via Advanced Queuing. There is a new object-typ called Event, who communicates with a queue. New data in a Queue starts an event-trigger, which runs in Forms.

The second highlight is the Javascript-API. HTML-pages can exchange data with Forms via Javascript. In the old days you could do this only using java applets. With the new technique you have WHEN-CUSTOM-JAVASCRIPT-EVENT-trigger which can be used from the developer. The communication runs in both directions and it's possible to change data in the HTML-page through Forms via Javascript

Those two new techniques are indications, that Oracle Forms is ready for the future. It is one of the main SOA-parts of the new Oracle Fusion Middleware 11g.

Additional information to Forms 11g can be found in the German Forms Community

Thanks to DOAG

New Statement of Direction, Nov. 2007

2007-12-01T12:00:00.001+01:00

This is the actual timeline for Oracle Forms

Forms 11g new features: Events

2007-11-26T15:00:00.000+01:00

My DOAG Conference 2007 presentation was "Forms 11g: A look behind the scenes"

The main two new features are: Advanced Queuing and Javascript-API. Today I explain the AQ-technique with Forms-Events.

Events - Interaction with Advanced Queuing

This is the first big new change in Forms Builder since 6 years. We can create Events and assign them to queues. If someone inserts new data in this AQ, the appropriate new trigger WHEN-EVENT-RAISED starts.

Allowed Properties for Events


Event Type        : Data Base, User Defined
Subscription Name : AQ-Name (LOV)
Scope             : Application, Form
Auto Subscribe    : Yes, No
Correlation ID    :
View Mode         : Browse, Locked, Removed

formsweb.cfg


maxEventWait=1000

The new parameter maxEventWait is important. The value is in milliseconds and means the intervall, in which the AQ's used by forms get refreshed against the database. Without this parameter the form refreshes only, if the user interacts with the runform.

Code-Snippet


BEGIN
:CONTROL.TI_Payload := get_event_object_property ('EV_Default', Event_Payload);
IF upper (:CONTROL.TI_Payload) = 'ATTACH DEBUG' THEN
 DEBUG.Attach;
 ...
END IF;
END;

This example is a remote control for remote debugging. You can insert a command into the queue, which is assigned to the Event EV_DEFAULT and each time, when the payload has the value ATTACH DEBUG a debug-process starts in the forms runtime.

In my Download-Section is a link to my presentation (in german).

DOAG Conference 2007

2007-11-22T22:57:00.000+01:00

Today was the last day of this years conference. The DOAG (German Oracle User Group) is now 20 years old and so we had some slots in the conference like "Oracle 5.0 New Features)

That was a funny presentation from Dierk Lenz. He used his 20 years old slides and presented us all the new features from the database in 1988.

In the presentation he started a vmWare with DOS 6.22 and SQL*Forms 2

In this presentation we were nearly 200 people ! To have a look on the new Features of Oracle 5.0 :-)

Forms Startup (part 1)

2007-10-01T12:49:00.000+02:00

This is the first article of a serial. Different methods of enhancing the Forms Startup-Phase are discussed here. In most projects I saw techniques, where the Browser-HTML were changed, so that the user cannot easily close the browser for example.

My first example is from Francois Degrelle. He gave me the tip with a self-hiding Internet Explorer on Forms Startup. The only parameter which has to be changed is the HTMLbodyAttrs in the formsweb.cfg:


HTMLbodyAttrs=onLoad='javascript:self.moveTo(2000,2000)'

After the forms-application-startup the browser-window moves to the x,y-position 2000, 2000. This is far behind the normal range of the visible screen. And so the user thinks, that the Browser closed hisself. The icon in the taskbar is still available, but the Browser didn't went visible, when the user clicks on the task-symbol.

pro:
- the user thinks, that the browser hides hisself
- only the forms-runtime window is visible (in separateFrame=True - mode)

contra:
- the active task of the browser is visible and can't be activated, only closed

this is actually my favorite technique

Bigger. taller. better :-)

2007-09-17T17:46:00.000+02:00

I played a little bit with the Template-HTML and found better properties for my Blog.

The old width for example was a bit problematic, because sourcecodes were often too long for the small size of the blog. Now I have 50% more space for my snippets :-)

Easy logging and debugging in Forms

2007-09-13T15:23:00.000+02:00

Each forms-application needs a simple way to log errors and find them. This technique can be used also to debug Forms, Reports and pure PL/SQL.

First create the table, sequence and view to store the logging-information:


CREATE TABLE Logging (
  ID                         NUMBER(8,0) NOT NULL,
  SESSION_ID                 NUMBER(8,0),
  INSERT_DATE                DATE NOT NULL,
  TEXT                       VARCHAR2(2000) NOT NULL);

CREATE SEQUENCE Logging_SEQ;

CREATE OR REPLACE VIEW V_Logging_desc 
         (ID, SESSION_ID, INSERT_DATE, TEXT)
AS SELECT ID, SESSION_ID, INSERT_DATE, TEXT
     FROM Logging
ORDER BY SESSION_ID DESC, ID DESC;

You need also a package with some functions to start the logging-process


CREATE OR REPLACE PACKAGE PK_DEBUG IS
  FUNCTION Debug_allowed RETURN BOOLEAN;
  FUNCTION Next_ID       RETURN NUMBER;

  PROCEDURE Disable;
  PROCEDURE Enable;
  PROCEDURE Destroy;
  PROCEDURE Init  (P_Debug_allowed IN BOOLEAN DEFAULT TRUE);
  PROCEDURE Write (P_Text IN VARCHAR2,
                   P_Session_ID IN NUMBER DEFAULT NULL);

  G_Debug_allowed BOOLEAN := TRUE;
  G_Session_ID    NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY PK_DEBUG IS
FUNCTION Debug_allowed RETURN BOOLEAN IS
BEGIN
  RETURN (G_Debug_allowed);
END;

FUNCTION Next_ID RETURN NUMBER IS
  V_ID NUMBER;
BEGIN
  SELECT Logging_SEQ.nextval
    INTO V_ID
    FROM DUAL;
  RETURN (V_ID);
END;

PROCEDURE Disable IS
BEGIN
  G_Debug_allowed := FALSE;
END;

PROCEDURE Enable IS
BEGIN
  G_Debug_allowed := TRUE;
END;

PROCEDURE Destroy IS
BEGIN
  Write ('----------------------stopp '
    || to_char (G_Session_ID) || '--');
  G_Session_ID := NULL;
END;

PROCEDURE Init (
  P_Debug_allowed IN BOOLEAN DEFAULT TRUE) IS
BEGIN
  G_Debug_allowed := P_Debug_allowed;
  G_Session_ID := Next_ID;
  Write ('--start ' || to_char (G_Session_ID)
    || '----------------------');
END;

PROCEDURE Write (
  P_Text       IN VARCHAR2,
  P_Session_ID IN NUMBER DEFAULT NULL) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF Debug_allowed THEN
    IF G_Session_ID IS NULL THEN
      Init;
    END IF;
    INSERT INTO Logging (ID,
      Session_ID, Insert_Date, Text)
    VALUES (Next_ID,
      NVL (P_Session_ID, G_Session_ID),
      Sysdate, P_Text);
    COMMIT;
  END IF;
END;
END;
/

You start the debug with INIT and end it with DESTROY. Error-Messages are logged through WRITE. For example:


pk_Debug.Write ('Hello World - ' || V_Test);

Parts of your debugging can be deactivated with DISABLE and from this point on nothing will be written into the logging-table till ENABLE is called.

The view V_Logging_desc shows you the logging-data, grouped by the newest session-id.


ID Session Insert-Date     Text
============================================
24    21   10.09.-12:38:48 -------stopp 21--
23    21   10.09.-12:38:48 Hello World - 42
22    21   10.09.-12:38:48 --start 21-------

Try it and have fun
Gerd

San Francisco Impressions

2007-08-31T17:39:00.000+02:00

Here are some impressions from the city, shot in the days after the test-event

From Lombard Street to Coit Tower

17 Miles Drive

Muir Woods

Another proof: Second Life exists in reality and not in virtuality

Stone Art in Sausalito

The Golden Gate

Oracle Fusion Middleware 11g Beta Test - Last Day

2007-08-24T19:00:00.000+02:00

Today was the last Day of the Beta-Test. After the last testings started the big Feedback-Session in Oracle's Conference Center.

All customers which tested this week and fifty Oracle experts came together, plus dozens through a Web-Conference. In the next 3 hours all products of the Fusion Middleware-Stack got feedback from us. The only tester for Oracle Forms (myself) gave this feedback to Oracle:

First of all: Forms is a really big platform in germany and this Beta 2 program showed us in the last days the first Oracle Forms, which has new functionality in the Forms Builder itself.

After that I described the new features and how important they are for the Forms Community.

In the end I had time to explain, which functionality I personally wish to have in the Forms Builder :

* an object called Web-Service at form-level
* the integration of BI-Beans
* and most important: a new PL/SQL-Editor

That was my statement and now I have to thank Oracle for the invitation to this event, Duncan and his group for the best support that you can have in such a week. It was fantastic to have the chance to work with such profis.

And the last thanks went to Rolf (my boss) who sponsored this trip, Manuela (who let me go) and Andreas, who backup'd me for this week in Krefeld. Many many thanks to you !

This must be the proof, that Duncan's career started in the NBA, before Oracle found him (I'm 6'7"):

PS: If you wonder, why I don't name the new features by name or describe, which functionality they have. Please excuse for that, but I had to sign many Documents at Oracle, which don't allow that.

Forms Beta-Test, Day 4

2007-08-23T19:00:00.000+02:00

Duncan and I invented a new feature in mind some days ago and today I was able to develop it in Forms 11g and have now a showcase:

Remote Debugging-Start through AQ

The idea is simple: A developer enqueues a payload to a Forms-AQ, where all running forms-applications have to listen on. Then the payload is transfered to a specific forms-runtime and starts a new AQ-Trigger. The user is asked, if he wants to switch into debugging mode. After a Yes the form starts a debug.attach and developer can use his Forms Builder to attach to the clients runtime.

This is a new and interesting remote debugging extension which can be used in Forms 11g.

Forms Beta-Test, Day 3

2007-08-22T19:00:00.000+02:00

Today Duncan and Phil Kuhn worked hard on some of my forms issues. It's great to see, how they and the team behind them work.

It's the first Oracle Forms Version since seven years, which has new functionality, new objects and new triggers in the Oracle Forms Builder!

All those, who propagated the end of Oracle Forms get invalidated, when the new release is launched.

Forms Beta-Test, Day 2

2007-08-21T19:00:00.000+02:00

The second day of the Beta-Test started with a big overview of the new Application Server. Four sessions were presented on this tuesday. The rest ot the time was testing Oracle Forms 11g.

At the end of the day Oracle invited us to a nice restaurant near the HQ.

Invitation to Oracle Forms beta-testing

2007-08-20T19:00:00.000+02:00

Oracle HQ in Redwood Shores invited me to participate at the 2007' Beta-Test for the Fusion product-stack.

Today we started at Oracle HQ with 16 other companies. In addition to us testers Oracle supports us with nearly 50 Oracle Cracks. For example Duncan Mills as Oracle Forms Guru

They created large test-szenarios for our 5 day testings. So we can test all parts of the new versions and look deep into them.

The spirit of the event is great!

Set Record-Status to Query after a POST-QUERY

2007-08-03T14:46:00.001+02:00

In a block with a POST-QUERY-trigger you often have the problem, that the Record-Status changes to CHANGED, if non-basetable-items were changed through the POST-QUERY.

Setting the recordstatus back to QUERY is a good method to solve this problem. First create a procedure for setting the record-status to QUERY:


PROCEDURE Set_Record_Query_Status IS 
BEGIN
  Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'), 
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'), 
                       STATUS, 
                       QUERY_STATUS);
END;

then use it in your POST-QUERY-triggers:


BEGIN
  SELECT someColumns
    INTO :myBlock.nonBasetable_Item
    FROM myTable
   WHERE someFilter;

  Set_Record_Query_Status;
END;

After resetting each record to QUERY-Status you don't have problems with the fetched data in this block.

Important: If the POST-QUERY changes Basetable-Items in the record you create record-locks, if the block-locking is Immediate. This problem can be solved:


BEGIN
  Set_Block_Property ('myBlock', LOCKING_MODE, Delayed);

  SELECT someColumns
    INTO :myBlock.nonBasetable_Item
    FROM myTable
   WHERE someFilter;

  Set_Block_Property ('myBlock', LOCKING_MODE, Immediate);

  Set_Record_Query_Status;
END;

try it
Gerd

Oracle Certified Trainer

2007-08-01T16:54:00.001+02:00

Since last Friday I'm an Oracle Certified Trainer. It is the precondition to work for Oracle University.

New launch of my Forms Framework project

2007-07-20T23:09:00.000+02:00

A place, where you host your open-source-project must be

- easy to use
- easy to administer
- easy to find

and two days ago I found Google Code. That's a gorgeous place to share own projects !

http://code.google.com/p/forms-framework/

Actually I started deploying the main PL/SQL library + Forms Template

have fun with this project and the next releases
Gerd

Check Form_Success for each Built-In

2007-07-19T12:35:00.000+02:00

Many Built-Ins have the problem, that they don't throw exceptions. (only in the ON-ERROR)

Example: You want to navigate to the block Customer and wrote a typo:


Go_Block ('CUSTOMR');
Do_something_after_Go_Block;

Go_Block can't navigate to the block, because you wrote CUSTOMR. But no exception is thrown inside the PL/SQL-Block. This means, the code didn't stop and the execution of Do_something_after_Go_Block starts. This is a big problem in most cases!

Solution: Create a procedure Check_Builtin


PROCEDURE Check_Builtin IS
BEGIN
  IF NOT Form_Success THEN
    RAISE Form_Trigger_Failure;
  END IF;
END;

Use this procedure after each Built-In:


BEGIN
  Go_Block ('CUSTOMR');
  Check_Builtin;
  Do_something_after_Go_Block;
EXCEPTION
  WHEN FORM_TRIGGER_FAILURE THEN
    -- do something ...
END;

Also you can create your own Built-In for example Goto_Block instead of Go_Block: This new procedure works internally with the new Check_Builtin


PROCEDURE Goto_Block (P_Block IN VARCHAR2) IS
BEGIN
  Go_Block (P_Block);
  Check_Builtin;
  Do_something_after_Go_Block;
END;

and then :


BEGIN
  Goto_Block ('CUSTOMR');
  Do_something_after_Go_Block;
EXCEPTION
  WHEN FORM_TRIGGER_FAILURE THEN
    -- do something ...
END;

Important: When you use this technique you have to write an exception-handling and check the FORM_TRIGGER_FAILURE.

This technique is similiar to Oracle's Check_Package_Failure, but this procedure can only be used, when you work with master-detail-relations.

use Check_Builtin
Gerd

Concatenate big Default-Where-Clauses

2007-07-10T14:26:00.000+02:00

The simplest way to concatenate a Where-Clause is this:

Example: An empty form with table DEPT. Let's say you have three rules, which controls the concatenation of the blocks Default-Where. The problem is: From the second IF on you have to check the value of V_Default_Where. If it is filled you must concatenate ' AND ' before each new part of the string.


DECLARE
  V_Default_Where VARCHAR2 (2000);
BEGIN
  IF Rule_1_is_TRUE THEN
    V_Default_Where := 'DEPTNO IN (10, 20, 30)';
  END IF;

  IF Rule_2_is_TRUE THEN
    IF V_Default_Where IS NOT NULL THEN
      V_Default_Where := V_Default_Where || 
                         ' AND DNAME != ''SALES'' ';
    ELSE
      V_Default_Where := 'DNAME != ''SALES'' ';
    END IF;
  END IF;
  
  IF Rule_3_is_TRUE THEN
    IF V_Default_Where IS NOT NULL THEN
      V_Default_Where := V_Default_Where || 
                         ' AND LOC IS NOT NULL';
    ELSE
      V_Default_Where := 'LOC IS NOT NULL';
    END IF;
  END IF;

  Set_Block_Property ('DEPT', DEFAULT_WHERE, 
                      V_Default_Where);
  Go_Block ('DEPT');
  Execute_Query;
END;

These IF's are not maintainable. Changes in the where-clauses result in two changes in the code:


    ...
      V_Default_Where := V_Default_Where || 
                         ' AND LOC IS NULL';
    ELSE
      V_Default_Where := 'LOC IS NULL';
    ...

A better approach is to use the ' AND ' at the beginning of each concatenation:


DECLARE
  V_Default_Where VARCHAR2 (2000);
BEGIN
  IF Rule_1_is_TRUE THEN
    V_Default_Where := ' AND DEPTNO IN (10, 20, 30)';
  END IF;

  IF Rule_2_is_TRUE THEN
    V_Default_Where := V_Default_Where || 
                       ' AND DNAME != ''SALES'' ';
  END IF;
  
  IF Rule_3_is_TRUE THEN
    V_Default_Where := V_Default_Where || 
                       ' AND LOC IS NOT NULL';
  END IF;

  Set_Block_Property ('DEPT', DEFAULT_WHERE, 
                      Substr (V_Default_Where, 6));
  Go_Block ('DEPT');
  Execute_Query;
END;

the Substr (V_Default_Where, 6) eliminates the first ' AND '.

Really easy and readable code!

PS: Read in the comments, why I didn't use "1=1"

German Weekday

2007-06-27T15:38:00.001+02:00

The big problem of the date-conversion "to_char (sysdate, 'D')" is, that depending on the NLS you get different results:

Sunday is the first day in the US
Monday is the first day in Germany

For the Green-Thursday of 2000 you get:

Green_Thursday := to_date ('23.03.2000', 'DD.MM.YYYY');
America: to_char (Green_Thursday, 'D') = 5
Germany: to_char (Green_Thursday, 'D') = 4

this is sub-optimal, because it's a format-mask, which is based on the underlying NLS and not on a strict rule.

My solution in this case is: The function German_Weekday


FUNCTION German_Weekday (P_Date IN DATE)
RETURN NUMBER IS
  V_Delta NUMBER;
BEGIN
  -- Reference-Day: Green Thursday 2000 = Day 4 in Germany
  V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
                                          'DD.MM.YYYY'),
                                 'D')) - 4;
  RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

and American_Weekday


FUNCTION American_Weekday (P_Date IN DATE)
RETURN NUMBER IS
  V_Delta NUMBER;
BEGIN
  -- Reference-Day: Green Thursday 2000 = Day 5 in the US
  V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
                                          'DD.MM.YYYY'), 
                                 'D')) - 5;
  RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

this function always returns the correct german 'D'-formatmask, indepent of the NLS.

have fun and use it
Gerd

Forms 11g Release Date

2007-06-23T09:55:00.000+02:00

Nobody knows the day, when Forms 11g will be released.

But the newest official news about "when" and "where" is:

"Version 11 of Forms will be in the production version of Application Server version 11 which is currently scheduled for fiscal year 2008."

Read the post in the OTN-Discussion Forum

Oracle Develop in Munich

2007-06-22T11:49:00.000+02:00

The start of the week I attend at the Oracle Develop in the Arabella Sheraton Munich. Nice place, but we had to run between the presentation from one hotel to another. And the coffee machine was only in the hotel, where only one slot from five slots were...

Day 1:

Monday morning I started with Frank Nimphius presentation about Forms, the future of Forms and the integration into Java, SOA, ...

Marc Sewtz and his "SQL Developer Features" was nice, because we saw a bunch of new features in the actual release.

After lunch the famous Bryn Llewellyn started his "PL/SQL Performance: Debunking the Myths". Gorgeous! Next time I hope he gets 2 hours for all the tips and tricks he explained

Parallel to the presentations Oracle served us 3 labs. Monday evening I went into the APEX 3.0 Lab, where we learned, how to work with the new version of Application Express.

Day 2:

Bad news at the coffee-front. We had to get our coffee in Hotel A and run some 100 meters down the road to Hotel B...

Tuesday I started with the next lab: "Developing and Deploying Oracle and PHP". Interesting to see, how easy it can be, working with PHP

After that Bryn presented the new PL/SQL-Enhancements of the new Oracle DB 11g. I love compound triggers ! And all the other stuff too. Accessing sequences without selecting against DUAL !!

Post-Lunch Frank explained "Building Rich UI using JavaServer Faces and AJAX. That was another highlight too me at the conference.

Interesting, how much Microsoft-Stuff was presented through many slots. The "Microsoft Interoperability with Oracle Fusion Middleware" showed the SOA-integration made by the Office-Toolstack. Nice to see a different world to Oracle.

Summary:

Presentations and labs: TOP
No Coffee: FLOP

New Forms Look & Feel

2007-05-10T11:58:00.000+02:00

Good news for all Forms-Developer who need a new Look & Feel for their applications.

In Grant's newest interview he spoke with Francois Degrelle, about his Forms Look & Feel White Paper from April 2007.

Faster Forms-Start with synchronize

2007-04-17T10:21:00.000+02:00

I can't believe it, but it is true in some cases !

If you have a forms-application and some form-starts are too slow in your mind, then you can try to use a synchronize to speed up the initial display. The user now thinks, that the form itself starts faster, but internally only the first display-refresh is faster.


WHEN-NEW-FORM-INSTANCE - trigger 

BEGIN
  synchronize;
  -- your WHEN-NEW-FORM-INSTANCE-code
END;

try it and believe it too !

EOUC 2007 has been canceled

2007-04-05T09:12:00.000+02:00

oh no...

the EMEA-Oracle-User-Council-Conference in Amsterdam has been canceled:

EOUC 2007

Update Dez. 2007: the URL is now canceled too

Multi-Select from DUAL

2007-04-04T10:50:00.000+02:00

An easy way to generate records from scratch is using an easy CONNECT BY against DUAL.

e.g. you need a Forms-LOV which shows the last 12 months.
So you have to create a record-group-select which gives you exactly 12 records. After that you combine it with sysdate. Let's see:


 SELECT Level LVL
   FROM Dual
CONNECT BY Level <= 12;

then you integrate the sysdate into the statement:


 SELECT add_months (trunc (sysdate, 'MM'), -1*Level) Month
   FROM Dual
CONNECT BY Level <= 12;

MONTH
--------
01.03.07
01.02.07
01.01.07
01.12.06
01.11.06
01.10.06
01.09.06
01.08.06
01.07.06
01.06.06
01.05.06

isn't that a pretty easy solution for getting generically the last 12 months?

Assertions

2007-03-14T13:46:00.000+01:00

Using assertions in sourcecodes is well known in Java and other programming-languages, but not in PL/SQL. Why?

That's a good question and I solved it for myself through using this technique:


DECLARE
  e_Assertion EXCEPTION;
BEGIN
  IF condition1 = 'value'
  OR boolean = TRUE
  OR something_else THEN
    RAISE e_Assertion;
  END IF;

-- your code:
...
EXCEPTION
  WHEN e_Assertion THEN
    NULL;
  WHEN OTHERS THEN
    -- when-others-exception-handling
END;

In this example you write all your negative assertions under each other and raise the assertion-exception, which does nothing in the exception-handling.

e.g.


PROCEDURE Double_Manager_Salary (P_EMPNO IN NUMBER, P_JOB IN VARCHAR2) IS
  e_Assertion EXCEPTION;
BEGIN
  IF P_Job != 'MGR' THEN
    RAISE e_Assertion;
  END IF;

  UPDATE EMP SET
         SAL = SAL * 2
   WHERE EMPNO = P_EMPNO;

EXCEPTION
  WHEN e_Assertion THEN
    NULL;
END;

what we see here is very simple: If you assert, that only manager get doubled salaries, then you cancel the procedure directly after it starts. You jump into the e_Assertion-Exception and do nothing.

Try and use it
Gerd

Invitation to EOUC 2007

2007-02-12T09:44:00.000+01:00

The EMEA Oracle User Council's conference is this year in Amsterdam, Netherland, from May 2nd - May 3rd.

My abstract, sent to the conference some months ago was:

Oracle Forms 10g and the integration into BPEL

And now I got an invitation for May 3rd

One Time Timer

2007-01-20T10:03:00.001+01:00

Many times I want to code a go_item, go_block or execute_query while validating an item. But restricted functions can't be used in many triggers. So we need a workaround.

And here comes my "One Time Timer" :

Example: I have a non-basetable control-block with some items. Below this block is a multi-record block based on EMP. The control-block should be used as filter on the EMP-block.

The user wish to enter filter-criteria in the master-block and when navigating to the next item, they automatically want a new query-result in the block EMP. This is impossible with standard validation-triggers, because the navigation has to go into a block and execute a query, while validating an item.

Solution: create a form-level WHEN-TIMER-EXPIRED:


DECLARE
 V_Item   VARCHAR2 (61);
BEGIN
 V_Item := :SYSTEM.CURSOR_ITEM;

 IF One_Time_Timer.Get_Value = Const.ott_Query_in_EMP THEN
   Go_Block ('EMP');
   Execute_Query;
   Go_Item (V_Item);
 ELSIF One_Time_Timer.Get_Value = Const.ott_Something_Else
 THEN
   -- if more One-Time-Timer are needed,
   -- create one for each Branch
   NULL;
 END IF;
END;

create a Const-Package with some constants:


PACKAGE Const IS

-- Globals
 gbl_One_Time_Timer CONSTANT VARCHAR2 (61) :=
   upper ('global.One_Time_Timer');

-- One-Time-Timer
 ott_Query_in_EMP   CONSTANT VARCHAR2 (30) :=
   'Filter EMP-Block';
 ott_Something_Else CONSTANT VARCHAR2 (30) :=
   'Something else';

END;

and a package for general functions:


PACKAGE One_Time_Timer IS
 FUNCTION Get_Value RETURN VARCHAR2;
 PROCEDURE Initialize (P_Event IN VARCHAR2);
END;

PACKAGE BODY One_Time_Timer IS
 FUNCTION Get_Value RETURN VARCHAR2 IS
 BEGIN
   Default_Value (NULL, Const.gbl_One_Time_Timer);
   RETURN (NAME_IN (Const.gbl_One_Time_Timer));
 END;

 PROCEDURE Initialize (P_Event IN VARCHAR2) IS
   tm_id   timer;
   tm_name VARCHAR2 (30) := 'ONE_TIME_TIMER';
 BEGIN
   tm_id := Find_Timer (tm_name);
   IF ID_Null (tm_id) THEN
     tm_id := Create_Timer (tm_name, 10, NO_REPEAT);
     COPY (p_Event, Const.gbl_One_Time_Timer);
   END IF;
 END;
END One_Time_Timer;

the control-block (named "Filter") has e.g. two items: ENAME and SAL

create a WHEN-VALIDATE-ITEM on ENAME :


BEGIN
 One_Time_Timer.Initialize (Const.ott_Query_in_EMP);
END;

last step: the EMP-block needs a PRE-QUERY-trigger on block-level:


BEGIN
 IF :Filter.ENAME IS NOT NULL THEN
   :EMP.ENAME := :Filter.ENAME;
 END IF;
END;

what happens?
After changing the value of ENAME in the FILTER-block the WHEN-VALIDATE-ITEM fires. He initializes the One-Time-Timer. The "global.One_Time_Timer" get the value of Const.ott_Query_in_EMP (which is "Filter EMP-Block"). After that a timer is created which fires 10 ms later.

10ms later:
The WHEN-TIMER-EXPIRED fires and does an execute_query in the EMP-block and returns afterwards back to the original item. In the EMP-block starts the PRE-QUERY and the data of the EMP-block gets filtered through ":EMP.ENAME := :Filter.ENAME"

that's the whole story. Try it and have fun!

Undo

2006-12-20T15:09:00.000+01:00

Retrieving data from the database and changing the data is really easy. But what, if the user changes data and want to do an UNDO?

Doing a new query is the easiest way. The limitations are:

- in a multi-record-block you have to position in the correct record after the query
- if the query was executed via ENTER-QUERY mode you can't jump to the old record because the query-result has changed.

So you have to use a new technique.

The solution is this function. All database-items get their old values back:


PROCEDURE Undo IS
  V_Block  VARCHAR2 (30) := :SYSTEM.CURSOR_BLOCK;
  V_Field  VARCHAR2 (61);
  V_Item   VARCHAR2 (61);
BEGIN
  Validate (Item_Scope);
  IF :SYSTEM.RECORD_STATUS = 'CHANGED' THEN
    V_Field := Get_Block_Property (V_Block, FIRST_ITEM);
    V_Item := V_Block || '.' || V_Field;
    WHILE V_Field IS NOT NULL
    LOOP
      IF Get_Item_Property (V_Item, ITEM_TYPE) 
         IN ('DISPLAY ITEM', 'CHECKBOX', 'LIST', 
             'RADIO GROUP',  'TEXT ITEM')
      AND Get_Item_Property (V_Item, BASE_TABLE) = 'TRUE' 
      THEN
        COPY (Get_Item_Property (V_Item, DATABASE_VALUE), 
              V_Item);
      END IF;
      V_Field := Get_Item_Property (V_Item, NextItem);
      V_Item := V_Block || '.' || V_Field;
    END LOOP;
  END IF;
END;

Best practice is to start this undo-procedure from a menu (e.g. EDIT - UNDO) or handle it through a shortcut.

Equal and UnEqual

2006-12-15T13:00:00.000+01:00

Sometimes you have to check the Equality of two variables.

Writing "IF A = B THEN" is not the solution for all cases. If one variable is NULL the whole statement is NULL and NULL becomes FALSE in an IF-Statement. So you have to work with a different technique:


FUNCTION Equal (P_String1  IN VARCHAR2,
                P_String2  IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  IF P_String1 = P_String2
  OR (P_String1 IS NULL AND P_String2 IS NULL) THEN
    RETURN (TRUE);
  ELSE
    RETURN (FALSE);
  END IF;
END;

FUNCTION UnEqual (P_String1  IN VARCHAR2,
                  P_String2  IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  IF P_String1 != P_String2
  OR (    P_String1 IS     NULL
      AND P_String2 IS NOT NULL)
  OR (    P_String1 IS NOT NULL
      AND P_String2 IS     NULL) THEN
    RETURN (TRUE);
  ELSE
    RETURN (FALSE);
  END IF;
END;

Now you can easily use Equal and UnEqual


IF UnEqual (Var1, Var2) THEN
  -- do something
ELSE
  -- do something different
END IF;

try it

ON-ERROR and ON-MESSAGE-trigger

2006-12-05T10:59:00.000+01:00

Many developer have problems with messages which popup in forms, for example "FRM-40401: No changes to save".

Then they look for workarounds and one of the easiest is manipulating the :system.message_level:

KEY-COMMIT - trigger on form-level (quick and dirty)


BEGIN
  :System.Message_Level := 25;
  COMMIT;
  :System.Message_Level := 5;
END;

or KEY-COMMIT - trigger


DECLARE
 V_Message_Level NUMBER;
BEGIN
 V_Message_Level := :System.Message_Level;
 :System.Message_Level := 25;
 COMMIT;
 :System.Message_Level := V_Message_Level;
END;

these are not best practices. Because you have to write those codes in hundreds of procedures and tons of code.

Filtering the errors and messages is the key to have a powerful message-handling. Here is an easy procedure which shows you the technique:

ON-ERROR - trigger on form-level


DECLARE
  V_Error_Code       NUMBER;
  V_Error_Text       VARCHAR2 (2000);
  V_DBMS_Error_Code  NUMBER;
  V_DBMS_Error_Text  VARCHAR2 (2000);
BEGIN
  V_Error_Code      := Error_Code;
  V_Error_Text      := Error_Text;
 V_DBMS_Error_Code := DBMS_Error_Code;
  V_DBMS_Error_Text := DBMS_Error_Text;
 
  IF V_Error_Code IN (40401, 40405) THEN
    /*
    || 40401, 40405 - no changes to save / apply  get filtered
    */
    NULL;
  ELSIF V_Error_Code IN (-1034, -3114) THEN
    /*
    || -1034, -3114 - not connected to database
    */
    Message ('Not connect to database, exiting Form');
    Exit_Form (no_validate);
  ELSIF V_Error_Code IN (40508, 40735)
  AND   V_DBMS_Error_Code BETWEEN -20999 AND -20000 THEN
    /*
    || -20000 errors are raised by RAISE_APPLICATION_ERROR 
    || They are handled in a different way
    */
    Show_and_Log_DB_Error (V_DBMS_Error_Text);
  ELSE
    /*
    || All other errors went into Show_and_Log_Error, where they
    || get inspected, analyzed and logged.
    */
    Show_and_Log_Error (V_Error_Code);
  END IF;
END;

OOW Summary

2006-11-06T10:28:00.000+01:00

Here you see the Howard-Street, during the OOW 2006. The whole street was one big tent:

Regis Louis and his overview about JDeveloper 11g was very refreshing, because the toolset is the center of Oracles new Fusion-Technology.

Steven Feuerstein discused in his presentations new ways to create exception-handling in PL/SQL and how to use a professionell unit-testing-software like utPLSQL.

Very interesting was his announcement, that he will publish a new application named Quest Code Tester, which helps you creating test-cases for automated PL/SQL-unittests. Production Releases are available in 6 months. This is the link to the new homepage for all tools around those new applications:

http://www.toadworld.com/

Bryn Llewellyn (creator of PL/SQL) showed us in his "Meet the Guru"-hour the new features of the Oracle Database 11.

The most powerful new topic is the "edition". This means that you can create a complete new version of a package / view / table. With an easy "alter system set edition = ..." you change to different versions.

e.g. when you have a set of new packages and want test them on the production-db. So you create the new packages in a new "edition". After that you can change the usage of the two versions online while the database is running. Testing the new packages and reseting to the old ones is done in seconds !

These were some of the most interesting news of the Oracle Open World

Oracle Open World 2006

2006-10-26T14:09:00.000+02:00

Big, bigger, moscone! This is what I'm aware about, when thinking back to this years Oracle OpenWorld.

42000 people attend Larry's big show and it become more and more each year

Larry's announcement this year was: "We give you Red Hat Linux-Support, better than anybody else in the market and less expensive".

This years toy :

Arriving in San Francisco

2006-10-22T18:31:00.000+02:00

Yesterday was the starting day of my oow-vacation.

Here are some impressions of the first night. Today at 7 AM was starting time of the Nike's Woman Marathon in San Francisco at the Union Square

The best runner started at 6:40.

AOUG Conference 2006

2006-10-19T15:10:00.000+02:00

Yesterday I was invited to hold a presentation at the yearly Austrian Oracle User Group in Vienna.

Did you know Vienna? It's such a lovely city with castle's, old buildings and coffee-houses. You'll love it, too.

My presentation was "Oracle Forms 10g and the communication with SOA via BPEL"

OOW is coming closer

2006-09-29T13:04:00.000+02:00

Gosh! Is that a damn hard work to build an oow-schedule in the biggest schedule builder I've ever seen.

Hundreds of good presentations and all of them are parallel at four days and only a few possible timeslots. So you sit before your browser and have to check in to the best presentations. But where are the best presentation when you scroll through 60 parallel slots per time-slot? It's hard work to read all the titles and authors.

and then: Tom's presentation is full - oh no! - a waitlist for those, who came late
but: Steven's isn't full yet! So let's book the last seat - and do it fast

Different presentation

2006-08-03T10:15:00.000+02:00

last week was a hard week !

Monday I got my invitation to the Oracle World from the EOUC.

Friday Oracle rejected my presentation about Forms-Integration in BPEL, because they have the same topic. But they gave me the chance to post a different presentation.

This Wednesday I got the invitation from oracle to present BI Beans

Developing Powerful Oracle Business Intelligence Beans in Oracle Forms

BI Beans replaced Oracle Graphics as the new graphical standard in Forms. For this new java based technology forms developers do not need java experience. In the presentation you learn the easy way to install BI Beans, communication between Forms and BI Beans and how to develop powerful graphical applications.