Internet Explorer crash with Forms 10g, How to Replace Jinitiator with JRE

Oracle Jinitiator is no longer compatible with oracle forms services. By default, Oracle Forms services 10g uses Jinitiator 1.3.1.22 to run Forms applications. Jinitiator was compatible with Internet Explorer 6 and Firefox version 2.0. But latest internet explorer like IE 8 and 9 crash with Jinitiator. Similar behavior is seen with Firfox version 3.0 and later.

To resolve this problem, you must configure JRE to work with forms services 10g. It is really easy and requires only very little work. Forms services 10g are by default configured to run with JRE 1.4.2_06. To get this working, locate following lines in your FORMSWEB.CFG file,

# System parameter: default base HTML file
baseHTML=base.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=basejini.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
baseHTMLjpi=basejpi.htm

Make changes as shown in bold face below.

# System parameter: default base HTML file
baseHTML=basejpi.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=basejpi.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
baseHTMLjpi=basejpi.htm

Make sue that following parmaments in FORMSWEB.CFG are as mentioned below.

jpi_download_page=http://java.sun.com/products/archive/j2se/1.4.2_06/index.html
jpi_classid=clsid:CAFEEFAC-0014-0002-0006-ABCDEFFEDCBA
jpi_codebase=http://java.sun.com/products/plugin/autodl/jinstall-1_4_2-windows-i586.cab#Version=1,4,2,06
jpi_mimetype=application/x-java-applet;jpi-version=1.4.2_06

Now install J2RE version 1.4.2_06 from following URL,

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase14-419411.html#j2re-1.4.2_06-oth-JPR

How to Enable Forms 6i short keys in Forms 10g / 11g

Shortcuts in Forms 10g and 11g are different from Forms 6i keys. For example, Forms 6i uses F10 as short key to save records while in Forms 10g and 11g, CTRL+S is default short key to save or commit changes. If you have migrated an app from older version then probably you would be looking some way to retain key shortcuts. This can be done by using a resource file named “fmrpcweb.res”. By default, Forms 10g and 11g use “frmweb.res”to manage key mapping. Do following steps to enable older short keys.

Create a new configuration in Formsweb.cfg and use “otherparams” parameter to supply resource file for key mapping as under,

[myConfiguration]
otherparams= term=/home/oracle/1010202Mid/forms/admin/resource/US/fmrpcweb.res

Now you should use myConfiguration while calling forms as under,

http://servername:port/forms/frmservlet?config=myConfiguration

The “fmrpcweb.res” resource file is located in Forms directory under middleWare home. in Forms 11g, this file can be located at following location

$Instance_home/config/FormsComponent/forms/admin/resource/US

In Forms 10g, fmrpcweb.res can be located in \forms directory

If you want to customize one ore more specific keys, visit following link to see how this can be done.

Hide username and password while calling oracle Reports. USING CGICMD.DAT file

If you have not configured reports with Oracle Single Sign-on, Oracle report server explicitly requires username and password when report is called using Web.Show_document(). Username and password is required in report calling URL, for example following method calls a reports using Web.Show_Documnet().

Web.Show_Document('http://domainname.com:8090/reports/rwservlet?userid=username/password@db& server=ReportsServer_1&desformat=PDF&destype=cache&report=report.rdf&paramform=yes','_blank');

In above call username and password are visible in URL, causing security problem. Oracle has provieded serveral methods to resolve this problem, one of these solution is to define keymapping in CGICMD.DAT file. In Reports services 11g this file is located at following location

$DOMAIN_HOME/servers/WLS_REPORTS/stage/reports/reports/configuration/cgicmd.dat

In Oracle Reports services 10g this file can be located in reportsconfdirectory.

To define a key mapping, append follwing line at the end of the file

userlogin: userid=username/password@db %*

Restart reports server/Managed server, now you can call your report using following URL

Web.Show_Document('http://domainname.com:8090/reports/rwservlet?userlogin&server=ReportsServer_1&desformat=PDF&destype=cache&report=report.rdf&paramform=yes','_blank');

You can define key mapping for as many parameter as you need using following syntax,

userlogin: userid=username/password@db server=ReportsServer_1 desformat=PDF destype=cache %*

If reports have been configured with SSO, simply pass ssoconn=configparameter in reports calling URL, here config is the Resource Access Descriptor defined in OID. This parameter will automatically get login information from Oracle Internet Directory.

Reference:http://download.oracle.com/docs/cd/E12839_01/bi.1111/b32121/pbr_run013.htm

What does ERP stand for and what does it do for businesses today?

ERP stands for Enterprise Resource Planning, is a large-scale software program designed for modern businesses, both large and small. A simple definition is that ERP systems aid the flow of internal business processes and allow for communication between a business’s departments and its internal functions and data. 

Using software-generated automated reports, enterprise resource planning systems are able to give companies an immediate picture of its real-time operations: production, inventory and order processing. ERP software tracks a business’s resources (raw materials, cash, employees), overhead and commitments (employee payroll, purchase orders and customer orders) for individual departments and for the company as a whole. Most ERP systems are modular. 

Workers can access only the modules they need in order to complete their duties while higher-ups can access all modules in order to both create and review data and reports. By keeping work zones modular, the security of the company is better protected as a whole.

 The more modules offered by an ERP system, the more specific reports and projects could get if necessary; one potential drawback to a company having many modules is that each module represents an additional cost for the purchasing company. 

ERP systems are a powerful way for companies to manage costs, service and production.

What does Entity-Relationship Diagram (ERD) mean?

Entity-Relationship Diagram (ERD):

An entity-relationship diagram (ERD) is a data modeling technique that graphically illustrates an information system’s entities and the relationships between those entities. An ERD is a conceptual and representational model of data used to represent the entity framework infrastructure.

The elements of an ERD are:

• Entities
• Relationships
• Attributes

Steps involved in creating an ERD include:

1. Identifying and defining the entities
2. Determining all interactions between the entities
3. Analyzing the nature of interactions/determining the cardinality of the relationships
4. Creating the ERD

Techopedia explains Entity-Relationship Diagram (ERD)

An entity-relationship diagram (ERD) is crucial to creating a good database design. It is used as a high-level logical data model, which is useful in developing a conceptual design for databases.

An entity is a real-world item or concept that exists on its own. Entities are equivalent to database tables in a relational database, with each row of the table representing an instance of that entity.

An attribute of an entity is a particular property that describes the entity. A relationship is the association that describes the interaction between entities. Cardinality, in the context of ERD, is the number of instances of one entity that can, or must, be associated with each instance of another entity. In general, there may be one-to-one, one-to-many, or many-to-many relationships.

For example, let us consider two real-world entities, an employee and his department. An employee has attributes such as an employee number, name, department number, etc. Similarly, department number and name can be defined as attributes of a department. A department can interact with many employees, but an employee can belong to only one department, hence there can be a one-to-many relationship, defined between department and employee.

In the actual database, the employee table will have department number as a foreign key, referencing from department table, to enforce the relationship.

Steps to change IP address of Oracle Application Server (J2EE and WebCache)

Below are the steps for changing IP Address And/or Hostname by using chgiphost.sh command.


Located at $ORACLE_HOME/chgip/scripts.


STEP 1] Stop all J2EE instance services (Mid Tier)


A) Go To $MID_HOME/opmn/bin
Give following command to stop opmn processes.
. /opmnctl stopall


B) Go to $MID_HOME /bin
Give following command to stop em console.
. /emctl stop iasconsole


STEP 2] Take Entire application server backup.


STEP 3] Note down the old IP- address and old hostname (if hostname is going to change) of the server.


STEP 4] change the IP/Hostname of the server, restart the server if required.


STEP 5] Check that the IP-Address and hostname in /etc/hosts is correct


STEP 6] Check that you are able to ping from other machine to that server.


STEP 7] Go to $MID_HOME /chgip/scripts


STEP 8] Run sh chgiphost.sh -mid


STEP 9] After excuting above command we have to specify NEW and OLD IP Or/And Hostname


When prompted for destination IP/Hostname enter new IP/Hostname.
When Prompted for source IP/Hostname enter enter Old IP/Hostname
When prompted for OIDAdmin password enter orcladmin password.


STEP 10] Check for any errors in $MID_HOME /chgip/log/


STEP 11] Start j2ee services using following method.


A) Go to $MID_HOME /opmn/bin
. /opmnctl startall


B) Go to $MID_HOME /bin
. /emctl start iasconsole


STEP 12] Check all services are started properly.


I hope it will help you in changing Oracle Application Server IP/ Hostname.

Steps to change IP address of Oracle Application Server (Infrastructure Tier and Business Inteligence)

We have to Change the IP address of server on which Infrastructure and forms are installed.

Note: Changing Hostname where infrastructure is installed is not supported method. It is not recommended to change hostname. Only Changing IP-Address is supported method.

Following are the steps which i have followed while doing the activity :

In following document the IP Address will be changed using chgiphost.sh command.

Located at $ORACLE_HOME/chgip/scripts.

STEP 1] Stop all services mid-tier plus infra

Stopping Mid –Tier

A) Go To $MID_HOME/opmn/bin
. /opmnctl stopall
B) Go to $MID_HOME /bin

Give following command to stop em console.
. /emctl stop iasconsole

Stopping INFRA-Tier

A) Go To $INFRA_HOME/opmn/bin
. /opmnctl stopall

B) Go to $INFRA_HOME/bin

Give following command to stop em console.
. /emctl stop iasconsole

C) Go to $INFRA_HOME/opmn/bin
Stop lsnrctl
./lsnrctl stop

D) Go to $INFRA_HOME/opmn/bin
Connect to Infra database
./sqlplus “/as sysdba”
./shut immediate

STEP 2] Take complete backup of application server.

STEP 3] Change the IP-address of the server, Reboot If required.

STEP 4] Check that the IP-Address and hostname in /etc/hosts is correct.

STEP 5] Check that you are able to ping from other machine.

STEP 6] Set ORACLE_SID, ORACLE_HOME to infrastructure database.


STEP 7] Start Infrastructure database.
Go to $INFRA_HOME/bin
./sqlplus “/as sysdba”
Startup

STEP 8] Start database listener
Go to $INFRA_HOME/bin
./lsnrctl start

STEP 9] Start opmn service
Go To $INFRA_HOME/opmn/bin
./opmnctl start

STEP 10] Start OID
Go To $INFRA_HOME/opmn/bin
./opmnctl startproc ias-component=OID process-type=OID

STEP 11] Run Chgiphost script.
Go to $INFRA_HOME/chgip/scripts
Sh chgiphost.sh -infra

STEP 12] After excuting above command we have to specify NEW and OLD IP with orcladmin password :

When prompted for destination IP enter new IP.
When Prompted for source IP enter Old IP.
When prompted for OIDAdmin password enter orcladmin password

STEP 13] Check for any errors in $INFRA_HOME/chgip/log

STEP 14] Startall opmn service
Go To $INFRA_HOME/opmn/bin
./opmnctl startall

Check the status of opmn services.

STEP 15] Go to $MID_HOME

STEP 16] Set $ORACLE_HOME path to $MID_HOME

STEP 17] Go to $MID_HOME/chgip/scripts
chgiphost.sh –mid

STEP 18] After excuting above command we have to specify NEW and OLD IP with orcladmin password :

When prompted for destination IP enter new IP.
When Prompted for source IP enter Old IP.
When prompted for OIDAdmin password enter orcladmin password.

STEP 19] Check for any error in $MID_HOME/chgip/log

STEP 20] Start all services as follows:

A) Go To $INFRA_HOME/opmn/bin
./opmnctl startall

B) Go To $INFRA_HOME/bin
. /emctl start iasconsole

C) Go To $Mid_ORACLE_HOME/opmn/bin
. /opmnctl startall

D) Go to $MID_HOME/bin
. /emctl start iasconsole


STEP 20] Check status from front-end.

How To Write To A Path, Make A FTP Operation, Send E-Mail A SQL Query Result In Excel File Via PL/SQL

After I read some blog entries about exporting Excel files via PL/SQL recently, I also decided to share my Excel API code in PL/SQL. I wrote a similar article before .  This API has following features;

Most of the interface procedures accepts SQL query as parameter:

• Creates an excel file content as CLOB variable (PRC_CREATE_EXCEL_BY_QUERY)
• Writes any CLOB data to a specific folder that defined before with a filename. (PRC_WRITE_EXCEL_BY_MANUAL)
• Writes any SQL query to a specific folder that defined before (PRC_WRITE_EXCEL_BY_QUERY)
• Sends e-mail any CLOB data as attachment with TO, CC, BCC features and with also some specific words with spaces in sender name like “John Smith Company”. (PRC_EMAIL_EXCEL_BY_MANUAL)
• Sends e-mail with SQL query result in Excel file as attachment (PRC_EMAIL_EXCEL_BY_QUERY)
• Writes any CLOB data to a FTP path as file. (PRC_FTP_EXCEL_BY_MANUAL)
• Writes SQL query result in Excel file to a FTP path. (PRC_FTP_EXCEL_BY_QUERY)
• Downloads SQL query result in Excel file from APEX application using WPG_DOCLOAD package. (PRC_DOWNL_EXCEL_FILE)
• You can also create dynamic Excel file content in CLOB variable. For example;


DECLARE
MYEXCELCONTENT CLOB;
BEGIN
-- Test statements here
PK_EXCEL_API.G_SHOW_BORDER := 'Y';
PK_EXCEL_API.EX_EXCEL_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_WORKSHEET_OPEN(MYEXCELCONTENT, 'test');

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'asdasdasd');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'INVOICE NUMBER');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'SEQ NO');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'VERSION..');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'BLA BLA BLA');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_WORKSHEET_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.EX_EXCEL_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.PRC_WRITE_EXCEL_FILE(P_CLOB => MYEXCELCONTENT,
P_FILENAME => 'test.xls',
P_DIR => 'UTL_FILE_DIR');
END;

Here is the all package here:

PK_EXCEL_API_v1.0

PK_EXCEL_API_v1.0