Converting to Oracle Forms 10g

Caution: Raised your eyebrows with this notice? You should be. Why? Because, this is not a sure-shot method for converting your old forms to new version. If you are having more number of forms, you should try this method with a hand-full of forms first. Make sure that this works, and then proceed to your full set of forms for conversion. The conversion is a complex task, dealt with as a simple task in this article. I believe in breaking up of the complex task into multiple simple chunks which can be managed easily. So beware of estimating the conversion. Always keep a BIG buffer time for your conversion activities as it is NOT AN EASY TASK.

In this article we will go through methods to convert an earlier version of Oracle Forms to Oracle Forms 10g Version. Here we are going to discuss a method through batch file provided by Oracle.

In the <ORACLE_HOME>\BIN folder there is a batch file named frmplsqlconv.bat. In this batch file it is calling a Java class to search and replace values found in older form for enabling the conversion.

The following is the line by which the actual conversion is done:

<ORACLE_HOME>\jdk\bin\java -Dsun.java2d.noddraw=true -DCONVERTER_DEFAULTS=<ORACLE_HOME>\forms\converter.properties -DSEARCH_REPLACE_FILE=<ORACLE_HOME>\forms\search_replace.properties oracle.forms.util.wizard.Converter %*

The file converter.properties contains some default properties of the converter class. The file search_replace.properties contains strings to be replaced in the new converted version of form.

This is the default search and replace functions handled in the search_replace.properties file:

###########################################################################

# The set of search and replace strings

# Each of these are in the form SearchString|ReplaceString

# Users can add their own set of search and replace strings at

# the end of this block

###########################################################################

Replace1=ROLLBACK_FORM|CLEAR_FORM(NO_COMMIT,FULL_ROLLBACK)

Replace2=ROLLBACK_NR|CLEAR_FORM(NO_COMMIT,FULL_ROLLBACK)

Replace3=ROLLBACK_RL|CLEAR_FORM(NO_COMMIT,FULL_ROLLBACK)

Replace4=OHOST|HOST

Replace5=MENU_CLEAR_FIELD|CLEAR_ITEM

Replace6=MENU_FAILURE|FORM_FAILURE

Replace7=MENU_MESSAGE|MESSAGE

Replace8=MENU_NEXT_FIELD|NEXT_ITEM

Replace9=MENU_PREVIOUS_FIELD|PREVIOUS_ITEM

Replace10=MENU_SHOW_KEYS|SHOW_KEYS

Replace11=MENU_SUCCESS|FORM_SUCCESS

Replace12=OS_COMMAND|HOST

Replace13=OS_COMMAND1|HOST

Replace14=:UN|GET_APPLICATION_PROPERTY(USERNAME)

Replace15=:PW|GET_APPLICATION_PROPERTY(PASSWORD)

Replace16=:LN|GET_APPLICATION_PROPERTY(USER_NLS_LANG)

Replace17=:AD|GET_FORM_PROPERTY(NAME_IN('SYSTEM.CURRENT_FORM'),FILE_NAME)

Replace18=:SO|:SYSTEM.TRIGGER_MENUOPTION

Replace19=RUN_PRODUCT|RP2RRO.RP2RRO_RUN_PRODUCT

Replace20=CHANGE_ALERT_MESSAGE|SET_ALERT_PROPERTY

Replace21=BREAK|DEBUG.SUSPEND

Replace22=DISABLE_ITEM|ENABLEDISABLEITEM.REPLACEDISABLEMENU

Replace23=ENABLE_ITEM|ENABLEDISABLEITEM.REPLACEENABLEMENU

One can add any other function (user-defined) in this list by specifying ReplaceNN (replace NN with running sequence numbers).

There is also an option to provide group of built-ins for which warnings are to be given by specifying a alternate built-in to use if the occurrence is a Forms procedure. The default section contains the following:

WarnOnlyBuiltIn.Message=The String %s was found. If it is an occurrence of the Forms Built-In, It should be replaced with %s.

WarnOnlyBuiltIn.WarnOnly1=CALL|CALL_FORM

Then create a batch file with all your old form modules name like:

REM Start Compiling

::convert_to_forms10g.bat

cls

Echo Compiling Forms....

for %%f IN (*.fmb) do frmplsqlconv module=%%f userid=xx/xx log=%%forms_conversion.log

for %%f in (*.pll) do frmcmp userid=xx/xx module=%%f module_type=LIBRARY batch=yes window_state=minimize compile_all=yes

for %%f in (*.mmb) do frmcmp userid=xx/xx module=%%f module_type=MENU batch=yes window_state=minimize compile_all=yes

for %%f in (*.mmb) do frmcmp userid=xx/xx module=%%f module_type=FORM batch=yes window_state=minimize parse=YES

for %%f in (*.mmb) do frmcmp userid=xx/xx module=%%f module_type=FORM batch=yes window_state=minimize script=YES

for %%f IN (*.fmb) do frmcmp userid=xx/xx module=%%f batch=yes module_type=form compile_all=yes window_state=minimize

ECHO Finished Conversion...Check for log file for details

Making Oracle Case insensitive

Well, this article is the fruit of thoughts on which how you can make Oracle's output case insensitive. Of course, the simplest way is to use the UPPER() function to change the case of either data stored or data being checked. For Example:

SELECT * 

FROM emp

WHERE UPPER(ename) = 'RAVI';

Function Based Index 

But by following this method, there is one problem. If the column ename is having an index, it will no longer be used. But starting from Oracle 8i, there is a concept called function-based index. Before getting to use function-based indexes, the following criteria must be met with:

• You must have the system privilege query rewrite to create function based indexes on tables in your own schema.
• You must have the system privilege global query rewrite to create function based indexes on tables in other schema's.
• For the optimizer to use function based indexes, the following session or system variables must be set:
  QUERY_REWRITE_ENABLED=TRUE
  QUERY_REWRITE_INTEGRITY=TRUSTED
• You must be using the Cost Based Optimizer (which means analyzing your tables/indexes)

And then it’s just a case of creating the index in the conventional way:

create index UPPER_ENAME_IX on ENAME ( UPPER(ENAME) ) ;

Note that this index will NOT be used for case-SENSITIVE queries. You could always have two indexes, one on ENAME and one on UPPER(ENAME), but it would probably be more efficient to just have the function-based one and code.

REGEXP_LIKE

With the release of Oracle version 10g, the regular expressions came to help for solving the problem. The following query can be executed to get case insensitive output:

SELECT * 

FROM emp

WHERE REGEXP_LIKE(ename,'ravi','i');

The output is:

ENAME               
--------------------
rAvi                
RAVI                
Ravi    

NLSSORT Function

There is one more way by which we can achieve this output. We can create a function-based index using nlssort() function. Like:

CREATE INDEX empp_idx ON
empp(NLSSORT(ename,'NLS_SORT=BINARY_CI'));

The above approach will not invalidate the index, and will hold a good candidate for using case-insensitive queries.

There is one more approach where it is no required  to change the queries. We just need to update two oracle system parameters, NLS_COMP and NLS_SORT. The following example will suffice:

NLS_COMP, NLS_SORT

alter session set NLS_COMP=ANSI;

alter session set NLS_SORT=BINARY_CI;

select ename from empp where ename = 'ravi';

ENAME               
--------------------
rAvi                
RAVI                
Ravi      

This approach will be far more efficient because of two reasons:

1. Indexes will be used if it exists on the column.

2. No development (rework) is required to make queries insensitive.

Click here to read How to Change Oracle back to Case Sensitive once again

Converting date to a date

In this article we will discuss what people end up doing erroneously. As data type Oracle stores DATE in a separate format then Character. But when passing values between two programs, the format may cease to be a Date anymore.

For example there are two independent systems, one which is a Oracle system (which we are going to see) and another foreign system which uses some other technology.

In this case let us also assume that the foreign system is our front-end to the user and Oracle system is the back-end part of the system. Say the front-end system is passing some date value which has been input from user to the database. The value will be interpreted as a Character by the Oracle system.

To convert the Character value to a Oracle-specific date data type, we will be using the function to_date(). The function to_date() accepts a Character value and will convert it into a Date (as understood by Oracle). We also needs to tell to the function what is the format in which the date value is coming. For example the character value 12-FEB-2007 can be converted to Date data type by using to_date('12-FEB-2007','DD-MON-YYY') function call.

Now what normally people do is that they do a conversion of a date data type to a date. That is a call to to_date() with date as input. For example: to_date(sysdate). What such a call will do is that it will implicitly convert the date parameter passed to a Character value, and then will pass it to to_date() function to be converted to a date again. The result is loss of time information. The time part will be truncated in this implicit conversion to Character.

Proof:

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
05.07.2007 13:42:00

SQL> select to_char(to_date(sysdate),'dd.mm.yyyy hh24.mi.ss') from dual;

TO_CHAR(TO_DATE(SYS
-------------------
05.07.2007 00.00.00

The moral learned is that never go for an implicit conversion. It is safe to use explicit data type conversions

Oracle SQL Developer v 1.1 Evaluation Release 3 - Known Issues

Contents

1. General
2. Connections
3. Creating and Modifying Objects
4. PL/SQL
5. Preferences
6. Reports
7. Schema Diff
8. Linux Only Issues
9. Oracle 8.1.7 (Not Certified)

1. General

• Settings from previous release not migrated
• Extensions for SQL Developer 1.0 no longer work 1.1.
• You can't invoke SQL*Plus for users with TNSNAMES Connections.
• Query Builder
  • Where Clause for a self referencing table is incorrect.
  • Tables are not listed alphabetically.
  • You can't always drag a table onto the white space. Workaround: Close and reopen the Query Builder.
  • Two consecutive queries give an Ora error. Workaround: Add ';' to the first, before adding second query.
• Icons to be updated for production release.
  • PL/SQL icons require more definition
• There is an error when you add External Tools. This is not causing any SQL Developer errors. You can ignore it.

2. Connections

• Cannot connect to remote database as OPS$ account.
• Import Connection: Does not permit replacing existing connections.

3. Creating and Modifying Objects

• For Functions, Packages & Procedures: Invalid icons not always shown for invalid objects.
• Create new Database Link. Failure occurs if you drop and try to recreate a database link with the same name.
• It is not possible to create a second index for a materialized view.
• Importing Data into Table using Excel spreadsheet has some issues.

4. PL/SQL

• Can't save spec and body in same file.
• Remote debugging works only with IP, not with hostname.
• In some instances compiling PL/SQL results in a 'token' error.

5. Preferences

• File Types: Associate File Extension With SQL Developer, Preference is not saved.(Windows only)
• Database - > Worksheet parameters Unable to run using @ or Start when you set the working directory. Workaround: Use the full path.

6. Reports

• SQL Developer hangs or throws an exception when you rerun the Sessions report.

7. Schema Diff

• The Tools menu now shows an item "Schema Diff". This is not functional yet.

8. Linux Only Issues

• Browsing
  • Views > Data Tab - Actions not working.
• Connections
  • Core dump while expanding a connection created using connection identifier.
  • New connection using connect identifier gives ocijdbc10 error.
• SQL Worksheet
  • Describe - F4 key does not work correctly.

9. Oracle 8.17 (NOTE: SQL Developer is only certified for database 9.2.0.1 and above)

• There are general issues with browsing 8.1.7 objects.

Courtesy: http://www.oracle.com/technology/products/database/sql_developer/files/knownIssues_v1_1.html

Download Oracle Sql Developer here