Understanding Oracle Forms timeout parameters (or should I say FORMS_TIMEOUT) FRM-92102 – A network error has occured

Alternate title: Why does my Oracle Form keep randomly disconnecting

Today I was at a client who was having trouble since there forms where disconnecting randomly after 15 minutes. They had tried to play around with the TIMEOUT  settings but they got bogged down in too many documents that pointed to more documents. I decided to post a quick guide to Oracle Forms timeout parameters. To help the community configure Forms sessions to time out after a specified period of user inactivity. Here I discuss the specific timeout parameters of Oracle Forms. For general web server timeout parameters you should look at note  294749.1  on Oracle Support – Troubleshooting WebForms Tuning / Performance /Time out Problems

Basically Oracle Forms 10g relies on 3 settings to control inactivity timeout.

1) FORMS_TIMEOUT environment variable:

This is an environment variable that is set in the env file found $ORACLE_HOME/forms/server/default.env. The default is et to use a file called default.env file  but this can be customized in the formsweb.cfg found in $ORACLE_HOME/forms/server/formsweb.cfg  to use .env files for each configuration in which case these should be modified instead of default.env.

Please note: The Forms environment variable FORMS_TIMEOUT is refered to as FORMS90_TIMEOUT in version IAS 9i and 10g R1 (9.0.4) it is in OAS 10g R2 (10.0.1.2) it is FORMS_TIMEOUT.

Add a row to the end of the file such as:

FORMS_TIMEOUT=30

The default value for forms timeout is 15 and Valid Values range from  3 to 1440 (1 day)

This parameter specifies the amount of time in elapsed minutes before the Form Services process is terminated when there is no client communication with the Form Services. Client communication can come from the user doing some work, or from the Forms Client heartbeat if the user is not actively using the form.

2) heartBeat applet parameter:

The heartBeat parameter is a feature of the applet and it is set in the formsweb.cfg file found in $ORACLE_HOME/forms/server/formsweb.cfg

This parameter is used to set the frequency at which a client sends a packet to the server to indicate that it is still running. We can define this integer value in minutes or in fractions of minutes, for example, 0.5 for 30 seconds. The default is two minutes.

Please note: If the heartbeat is less than FORMS_TIMEOUT, the user’s session will be kept alive, even if they are not actively using the form. HeartBeat is recommended to be at least 2 beats smaller than FORMS_TIMEOUT

In the formweb.cfg file add the following line to the configuration of your choise:

heartBeat=12

The default value for heartBeat is 2 and Valid Values range from  1 to 1440 (1 day)

3)The session-timeout  parameter of the forms servlet in the web.xml configuration file

This file defines the OC4J forms & listener servlet definitions, including servlet parameters and session configuration. The file is found
$ORACLE_HOME/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/web.xml

The value of session-timeout refers to the maximum amount of inactivity that a single session can have before it is automatically invalidated by the server.

Please note: The value of should be the same as FORMS_TIMEOUT.

The following lines must be added to web.xml.

<web-app>
……
……

<session-config>
<!– Session timeout in minutes –>
<session-timeout>60

</session-config>

</web-app>

networkRetries applet parameter

Besides the above timeout parameters there are additional reasons that forms maybe disconnecting that are not timeout based but network related. Ususally they are prefaced by error FRM-92102: A network error has occurred. The Forms Client has attempted to reestablish its connection to the Server 5  time(s) without success.  This parameter specifies the number of times the Forms client should try reconnecting to the middle-tier before finally timing out. The Oracle Forms network connection to the server is a little picky it does not like being ignored. So as such if it tries to reach the server more than 5 times without response it dies. Although this would seem logical most of the time 5 seconds is just too little if we take into consideration that there maybe database performance issues or network waits.

To add tolerance to Forms patience thay have added a great parameter called ‘networkRetries’ to the forms configuration file $ORACLE_HOME/forms/server/formsweb.cfg.  Set the value of the networkretries parameter to a suitable number according your  network characteristics needed. For example, networkRetries=30.  If the error continues to occur even after setting up an appropriate value for networkRetries, you should check firewall and proxies that maybe blocking the applications server ports.

Some interesting links about this topic are:

Known Causes of FRM-92101 Error In Forms [Note: 604633.1 on Metalink]

Subject: How To Fix The Forms Timeout Issue In Oracle Applications 11i

Understanding How networkRetries Works [ID 332942.1]

Master note on Known Causes of FRM-92102 Error in Forms [ID 756369.1]

Refernce :- http://oracleformsinfo.wordpress.com/2012/01/12/forms_timeout-frm-92101/ 

Adding an Exit All Button to Your Forms Menu / Toolbar

Alternative title: Eject me from the forms system fast! Close all forms without all those pesky validations.

This week a need arose at a customer site to exit all open forms (with one button click ) and return to the first form with the main menu.

This need may also arise when the user creates a large workflow of different forms calling each other but then wants to close all called forms with one button.

The steps are quite simple:

1) You begin by putting the following code in the WHEN-NEW-FORM-INSTANCE trigger of all the forms in the application

DEFAULT_VALUE(‘false’,'GLOBAL.closing’);

This will create a new global variable called “closing” in your forms system. This value will be false by default and it will be changed to true when the process to exit all has been triggered.

If you do not have a global program unit in your WHEN-NEW-FORM-INSTANCE trigger for all forms, then you will want to use one of the methods to update forms in batch that are available.   You can use either JDAPI or convert all forms to XML and add this code in Notepad++  ( for more information you can check out Blog posts: JDAPI, Converting Forms to XML)

2) Next step is to check each form when the window is activated if we are in the process of exiting all or if we have reached the main form yet.  Here we want to test

(a) Is the global called “closing” set to true? If so we are in the middle of the exit all process

(b) Have we reached a form called . If so we have already arrived to the main navigation form.

To do this we put the following code in WHEN-WINDOW-ACTIVATED trigger of all system forms. Again this may be done in batch (see above)

:IF :GLOBAL.closing = ‘true’ AND GET_APPLICATION_PROPERTY (CURRENT_FORM_NAME) <> ‘’

THEN
EXIT_FORM(NO_VALIDATE);
ELSE
:GLOBAL.closing := ‘false’;
END IF;

We do an exit all without form validation here. This means that if the form has not yet been saved, all changes that were done will be automatically saved. If this is not appropriate for your system then simply replaces the  EXIT_FORM (NO_VALIDATE) with EXIT_FORM(NO_VALIDATE,FULL_ROLLBACK) This will be the only way that the forms can close without user intervention. Since if the forms have outstanding changes that require validation it will prompt the users to correct or commit the changes.

Also in the above code don’t forget to replace with the name of the main menu or navigation form.

3) The final step in the process is of course how we start the exit all domino effect. This can be done by simply adding an exit all or close all button in the main menu or toolbar.

This button should appear on all forms so anywhere that is seen globally in all forms should be fine.

The code of the button should look like this:
:IF GET_APPLICATION_PROPERTY (CURRENT_FORM_NAME) <> ‘’

THEN
:GLOBAL.closing := ‘true’;

EXIT_FORM(NO_VALIDATE);
END IF;

Again you can replace the exit_form command with what's appropriate for your system.

So that's it! As you can see, this is so simple the most time consuming part will be deciding what icon to put on the exit all button. Even here I can help. Check out the links below. 

Reference - http://oracleformsinfo.wordpress.com/2012/02/02/quick-tip-adding-an-exit-all-button-to-your-forms-menu-toolbar/

Chart of Accounts Segment Descriptions in Oracle EBS R12 (Script)

You can use the following query to get the description of COA of Oracle EBS by using apps.gl_flexfields_pkg.get_description_sql and avoid hideous number of joins back to FND_FLEX_VALUES_TL and generally hard-code in value set ids.


SELECT gcc.code_combination_id
                ,gcc.segment1
                ,gcc.segment2
                ,gcc.segment3
                ,gcc.segment4
                ,gcc.segment5
                ,gcc.segment6
                ,gcc.segment7
                ,gcc.segment8
                ,DECODE(gcc.segment1,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                                 (gcc.chart_of_accounts_id,1,gcc.segment1)) seg1_desc           
               ,DECODE(gcc.segment2,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                                ( gcc.chart_of_accounts_id,2,gcc.segment2)) seg2_desc
               ,DECODE(gcc.segment3,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                                 (gcc.chart_of_accounts_id,3,gcc.segment3)) seg3_desc
               ,DECODE(gcc.segment4,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                                ( gcc.chart_of_accounts_id,4,gcc.segment4)) seg4_desc
               ,DECODE(gcc.segment5,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                                ( gcc.chart_of_accounts_id,5,gcc.segment5)) seg5_desc
               ,DECODE(gcc.segment6,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                               ( gcc.chart_of_accounts_id,6,gcc.segment6)) seg6_desc
               ,DECODE(gcc.SEGMENT7,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                               ( gcc.chart_of_accounts_id,7,gcc.segment7)) seg7_desc
               ,DECODE(gcc.SEGMENT9,NULL,'',apps.gl_flexfields_pkg.get_description_sql
                                                               ( gcc.chart_of_accounts_id,8,gcc.segment8)) seg8_desc
              ,gcc.chart_of_accounts_id chart_of_accounts_id
              ,gcc.account_type
 FROM  gl_code_combinations gcc;


Thanks - http://oracleared.blogspot.com/2012/07/chart-of-accounts-segment-descriptions.html

ORA-01000: maximum open cursors exceeded tips

Oracle docs note this about ORA-01000:


ORA-01000 maximum open cursors exceeded

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

Question:

I keep encountering ORA-01000 and can't even create a table

Answer:

First, you should try increasing your OPEN_CURSORS and take a look at the application to see if/why cursors are staying open. Here is an example from a forum:

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

Furthermore, to resolve ORA-01000, try to close whatever cursors are no longer in use, raise the OPEN_CURSORS parameter within your initialization file, and restart Oracle.

MOSC also offers information regarding ORA-01000 when it occurs with inserts in PL/SQL or SQL*Plus. ORA-01000 is thrown because of a maxed limit of open cursors in an execution or user session. Working around ORA-01000 can be done on the database level by setting the init.ora parameter, OPEN_CURSORS, which states the maximum cursors allowed. Note that the OPEN_CURSORS are defaulted at 50 which may not be high enough, causing ORA-01000, and that each operating system determines the appropriate value.

Resolving ORA-01000 try setting this OPEN_CURSORS to a higher number. Keep in mind when resolving ORA-01000 in this way that changing this value in the proper way should not alter the system performance, but will probably require more memory for cursor storing.

Thanks - http://www.dba-oracle.com/sf_ora_01000_maximum_open_cursors_exceeded.htm

Data Pump Export (expdp) and Data Pump Import(impdp)

Data Pump Export (expdp) and Data Pump Import(impdp)
Introduction
Oracle introduced the Data Pump in Oracle Database 10g Release 1. This new oracle technology enables very high transfer of data from one database to another. The oracle Data Pump provides two utilities namely:

Data Pump Export which is invoked with the expdp command.
Data Pump Import which is invoked with the impdp command.

The above two utilities have similar look and feel with the pre-Oracle 10g import and export utilities (imp and exp) but are completely separate. This means that dump files generated by the original export utility (exp) cannot be imported by the new data pump import utility (impdp) and vice-versa.

Data Pump Export (expdp) and Data Pump Import (impdp) are server-based rather than client-based as is the case for the original export (exp) and import (imp). Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped to a file system directory be specified in the invocation of the data pump import or export.

It for this reason and for convenience that a directory object be created before using the data pump export or import utilities. For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:

SQL> create directory expdp_dir as '/u01/backup/exports';


then grant read and write permissions to the users who will be performing the data pump export and import.

SQL> grant read,write on directory expdp_dir to system, user1, user2, user3;

Invoking Data Pump Export
You can invoke the data pump export using a command line. Export parameters can be specified directly in the command line.

Full Export Mode
A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role. Example :

$ expdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.og


Schema Export Mode
The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go. Optionally, you can include the system privilege grants as well.

$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe


Table Export Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own schema. You can only specify tables in the same schema.

$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments


Invoking Data Pump Import

The data pump import can be invoked in the command line. The export parameters can be specified directly in the command line.

Full Import Mode The full import mode loads the entire contents of the source (export) dump file to the target database. However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.


$ impdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og

Schema Import Mode
The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.


$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe


Table Import Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.

$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments


Thanks - http://wiki.oracle.com/page/Data+Pump+Export+%28expdp%29+and+Data+Pump+Import%28impdp%29

Using AUTO_INCREMENT in MYSQL

Using AUTO_INCREMENT in MYSQL

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.
Note

For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;


Regards-
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

ORA-12638: Credential retrieval failed error

ORA-12638: Credential retrieval failed error -
Oracle 11gR2


Cause: The authentication service failed to retrieve the credentials of a user.

Solution:

Please check the sqlnet.ora file (ORACLE_HOME\\NETWORK\ADMIN\). Change NTS to NONE and this should work.

Before – SQLNET.AUTHENTICATION_SERVICES= (NTS)

After - SQLNET.AUTHENTICATION_SERVICES= (NONE)

Thanks