Tab-Canvases and F2

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

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

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

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

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

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

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 ?

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

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

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 ?

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

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

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)

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

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

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

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

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

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