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.

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

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 ONempp(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:

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.

Disclaimer

This blog contains things about technology that I jot about, keep for my records. Some articles in this blog are not owned by me. They are for my reference only. In older posts the original links may not be there, its not intentional. Thanks for understanding.