An example of a java bean usage in Oracle Forms(Copy/Paste text)

I always wanted to learn to use java in Oracle Forms. Oracle form is very limited on a client side and you often need to expand your possibilities by use java code.
And I have, at last, a task that can not be solved without the java usage.

There is my target: I need to write a form for a text template creating, where I can create such a template for example:
..........
Dear <<Client Name>>
Please accept this purchase order <<Order number>>
Once it has shipped, you will receive a Shipping Confirmation email <<Email address>>.
..........

Our dictionary of tags is:

• Client Name
• Order number
• Email address

The template will be created in one multi-lined item.

I have few tags in my dictionary and I need to pass a tag from the dictionary to a multi-lined text item as easily as possible.
Our tags list is a multi-record block. I write the template and I want to insert a tag value at the position where I am now. I want to click the row with a necessary tag and the tag will be inserted programmatically in a current position in the editable item.

What's a difficulty of this action? Oracle form does not be able to insert text into an inner place of a multi-lined item if you navigate from/to the item.

To pass the tag to the template I want to do:

1. to edit the template item and to place a cursor to a position where I'd like to insert a tag;
2. to click the tag;
3. to copy the tag value into system clipboard(Copy);
4. to navigate to the template item(a focus navigates to the current position);
5. to simulate Cntrl-V pressing(Paste).

The actions (1) and (3) I intend to process by Java Bean.

Java:
I create Java class CopyPasteClipBoard.java
There's its code:

package com.metro.beans;

import java.awt.Robot;
import java.awt.Toolkit;
import java.awt.datatransfer.Clipboard;
import java.awt.datatransfer.ClipboardOwner;
import java.awt.datatransfer.StringSelection;
import java.awt.datatransfer.Transferable;
import java.awt.event.KeyEvent;

import oracle.forms.handler.IHandler;
import oracle.forms.properties.ID;
import oracle.forms.ui.VBean;

public class CopyPasteClipBoard extends VBean implements ClipboardOwner{
static IHandler handler;
static String COPY_PROPERTY_NAME = "COPY_TO_CLIPBOARD";
static String PASTE_PROPERTY_NAME = "PASTE_FROM_CLIPBOARD";
static String selectedText;

protected static final ID CopyText = ID.registerProperty(COPY_PROPERTY_NAME);
protected static final ID PasteText = ID.registerProperty(PASTE_PROPERTY_NAME);

public CopyPasteClipBoard(){
super();
}

public void init(IHandler hand){
super.init(hand);
handler = hand;
}

public boolean setProperty(ID property, Object value){
boolean rc = true;
if (property == CopyText){
selectedText = (String)value;
StringSelection stringSelection = new StringSelection( selectedText );
Clipboard clipboard = Toolkit.getDefaultToolkit().getSystemClipboard();
clipboard.setContents( stringSelection, this );
} else if (property == PasteText){
try {
Robot r = new Robot();
r.keyPress(KeyEvent.VK_CONTROL);
r.keyPress(KeyEvent.VK_V);
r.keyRelease(KeyEvent.VK_CONTROL);
r.keyRelease(KeyEvent.VK_V);

}catch(Exception e){
e.printStackTrace();
}
}else{
return super.setProperty(property, value);
}
return rc;
}

public void lostOwnership(Clipboard arg0, Transferable arg1) {
}

public static void main(String[] args) {
CopyPasteClipBoard obj = new CopyPasteClipBoard();
obj.setProperty(CopyText, "tratata");
obj.setProperty(PasteText, "");
}
}

In Form:

I add a new "Bean Area" item to control block, visible  on some canvas with width=0 and height=0.
I set the "Implementation class" property value com.metro.beans.CopyPasteClipBoard(the package and class name).

I create an item TEMPLATES.TEMPLATE_EDIT for a template editing.

You need to switch the template item property "Keep Cursor Position" to "Yes".(Oracle, thanks for think about). This gives us to navigate to a same position if I leave and I enter to the template item.

I create a multi-record block TEMPLATE_TAGS with an item TAG_LABEL.

There's the "WHEN-MOUSE-UP" trigger's code of the tag item  :RM_TEMPLATE_TAGS.TAG_LABEL

v_dummy := ' <<'||:TEMPLATE_TAGS.TAG_LABEL||'>> ';
set_custom_item_property('CONTROL.COPY_PASTE_BEAN','COPY_TO_CLIPBOARD',v_dummy);
go_item('TEMPLATES.TEMPLATE_EDIT');
synchronize;
set_custom_item_property('CONTROL.COPY_PASTE_BEAN','PASTE_FROM_CLIPBOARD',v_dummy);
else
go_item(:CONTROL.CURRENT_FIELD);
end if;

There are steps and problems you need to solve:

• To export our java project with CopyPaste bean to CopyPasteJbeans.jar and to place one in 

(Forms 10)

c:\Oracle\Middleware\Oracle_FRHome1\forms\java\;

(Forms 11 for me, development web logic installation) c:\Oracle\Middleware\user_projects\domains\FormsReports\servers\AdminServer\tmp\_WL_user\formsapp_11.1.2\7tyy89\war\metro_java\

• For Oracle*Forms 11 version you need ORACLE signature for this JAR by jarsigner. In addition you must create directory maping in the file(for server web logic installation):

c:\Oracle\Middleware\asinst_1\config\FormsComponent\forms\server\forms.conf

• To add the jar file name to the parameter archive_jini in an appropriate section of formsweb.cfg 

archive=/forms/metro_java/MetroJbeans.jar,frmall.jar

• For Forms 11 you must create 
• For example:
   archive_jini = frmall_jinit.jar, CopyPasteJbeans.jar
• To solve java permissions. You search the c:\Program Files\Oracle\JInitiator 1.3.1.26\lib\security\java.policy
  (this location for 10 version, for 11 version you need to search in %JAVA_HOME% path a file with same name) and after row

// "standard" properties that can be read by anyone

      to add two rows

        permission java.awt.AWTPermission "accessClipboard";

      permission java.awt.AWTPermission "createRobot";

I see one problem only: where do you place your JARs. I've give directory pathes, this can help...

I wish you success

Yuri

Oracle database objects downloading

ODA installed. We see a blank window.
Now is the time for DB objects downloading.
You must press the "Administration" button.

The "Administration" window appears

You press the "Insert Objects" button.

Next window allows you to call few adapters.

The first adapter allows to connect to your analyzed database and to download its objects.

The "Adapter" field value is your logical name for this database connect.

You can populate all items and press "Create properties file" button for parameters file creating and you can use this file on batch downloading.

For the batch downloading you run command:

java -Xms512m -Xmx1256m -jar oda.jar -batch <Parameters file created earlier> admin

Else you can press the "Ok" button for online downloading.

ODA Oracle object dependencies analyzer. Part 1

I want to familiar you with the ODA. It's a tool for impact analysis of program code on the one side and the database objects on the other side.
You can download the ODA from our ODA site.
The ODA is Java Swing application and requires Java Runtime Environment (JRE 1.6 Version and higher).
Further, you create an Oracle user, which will be created the ODA repository.
The user repository owner must have "CREATE TABLE" and "CREATE SEQUENCE" and unlimited quota size permissions.
Here's the sample of a script:
  -- Create the user
  create user ODA_OWNER
  default tablespace USER
  temporary tablespace TEMP
  profile DEFAULT;
  -- Grant/Revoke role privileges
  grant connect to ODA_OWNER;
  grant resource to ODA_OWNER;
  grant select_catalog_role to ODA_OWNER;
  -- Grant/Revoke system privileges
  grant select any table to ODA_OWNER;

  grant unlimited tablespace to ODA_OWNER;
For the ODA starting you run command:

java -Xms512m -Xmx1256m -jar oda.jar admin

In Login prompt you connect to Oracle user - owner of ODA repository(8 tables and 1 sequence).
At first time the ODA repository created automatically in Login ODA user schema. In future you can drop or create new repository in Login window too.
ODA user must see DBA views. 

You can see the installation details there.

Dependency is always a relationship between objects and a few objects:

• The program unit use tables, views, others program units
• The form trigger use DB objects
• The view look to few tables or views
• The view used in few program units

For relationships search I can use Oracle dictionary views if this relation there is between DB objects or simple string search in the program units(PL/SQL) or just text(Word,scripts).
Well, that in both these cases, there is something that can help us.
In first case there are DB views(PUBLIC_DEPENDENCIES).
In second case the search more difficult, but we have all database object's list downloaded into the ODA repository and if text search found database object name in the text we know what's type of this object and we can understand what's kind of dependency we found.

So, our first step - to download  metadata of database objects.

Problems that must be solved in order to build dependencies analyzer

I saw the problems which I wrote in previous post and I decided to develop a tool that will answer to this questions (and with that to study the Java language too ). 

So what do I have?

• Some Oracle database instances
• Some legacy applications use this instances (Forms application, Informatica, Word files...)

It's clear, for one overall answer I must have one common repository and some adapters that work with different types of applications.

How the dependencies will be shown ?
I like a tree visual object. The tree will give me the opportunity to show the dependency in two directions: 

• object that used in some codes
• code which some objects uses

So I need two tabs for two directions.

Every time I chose a tree branch, depending on the direction, I want to see the context of use of object, just a fragment of code where's the object's name. I need a window in which I can show track / snippets of code. Also I want to see whole PL/SQL procedure code or text fragment. I want to see the object reference allocated by color.

The separate task - how to show code. There are different tasks to show PL/SQL package, PL/SQL procedure, Oracle form or INFORMATICA workflow.

The tree gives me the opportunity to watch dependence with moving from level to level. But I need to see all dependencies of the object as a grid, where I can see all referenced objects with its details and the usage type.

The object analyzer works also with some database instances so it's necessary to work with DB links and synonyms.

I need a report about the found dependencies for an offline work.

Why we search database object dependencies

In large companies you often observe many multi-database and multi-platform legacy systems. This setup makes it almost impossible to discover information regarding the dependencies between database objects, programs, forms and reports. Applications can include several hundred stored procedures, Oracle forms and reports using thousands of objects inside the Oracle database (tables, views, packages, synonyms and DB links).

Lack of technical documentation, or partial documentation, incompatibility between application's documentation and implementation complexity are just some of the reasons why it is almost impossible to determine the various dependencies to any given object. Legacy systems have lost a common structure with a common logic and it has become hard to analyze and identify the object's dependencies and relationships.

We can see something similar on the internet. At most you can see the logic of a website, but the structure and relationships on the internet itself are impossible to know without a web crawler.

I see some reasons for to search object's dependencies:

1. I wants to change object definition and I want to know the objects that will be invalid after the change (DB objects).
2. What's external object use DB objects. I know the some external objects work with database:
            -  Oracle* Form/Reports
            -  xNIX and sql scripts
            -  Word files (Global/Detail design documents)
            -  Flat files (Java, C, XML)
            -  Informatica workflows work against DB

3. Reverse engineering of legacy systems

Only if we search dependencies in DB we have Oracle support as dependencies information in dictionary views 

• ALL_DEPENDENCIES
• DBA_DEPENDENCIES
• USER_DEPENDENCIES
• PUBLIC_DEPENDENCIES

For  the answer to the second question we don't have any information. We use a kind of search methods for a string search if it's possible.


For the third reason it doesn't enough to know what's dependency so how did the object use. We want to know what's context of the object usage, what's a type of table usage: select/insert/update/delete.

For a big company it's desirable to receive one overall answer for all its applications.