Weblogic Corner: Oracle Forms and Reports Quick start

During the summer I needed to quickly work myself into Oracle Forms for a business opportunity and I decided to share my experience in form of this quick start guide.

We will look at the installation process on Windows and Linux in a development and production configuration. To work with the Forms samples we install an Oracle Express Edition Database. We will look at the basic operations and a database tutorial to get acquainted with this edition. We also look at an Application Express Example, Oracle’s quick and simple approach to database applications. We will use the development configuration to run through the Forms tutorial “Creating a Master-Detail Form”, which ships with the product. Eventually we will look at a Forms and Reports installation in a high-availability configuration. We propose a setup on two virtual box Linux machines and provide an action plan for its installation.

Software Versions used:

Weblogic Server 10.3.6 Generic
JRockit 64-Bit for Windows R28.2.4
Oracle Forms and Reports 11g Release 2, Windows 64-bit
Oracle Database Express Edition 11g Release 2
Oracle Linux Release 5 Update 8 for x86_64 (64 Bit)
Java SE Development Kit 6 Update 33 for Linux x64
Oracle Forms and Reports 11g Release 2 for Linux 64 bit
Oracle SQL Developer 3.1 (3.1.07.42).

OracleFormsReports.pdf 2,2 MB

******************************************************************************************
This is the text-only preview. Please open the PDF file for full formatting and pictures.
******************************************************************************************

1 Contents

Oracle Forms and Reports Quick start 1

1 Contents 1

2 Introduction 3

3 Installation of the Forms and Reports Development Environment 3

3.1 Java Installation 4

3.2 Weblogic Installation 4

3.3 Forms and Report Installation 5

3.4 Forms and Reports Configuration 5

3.5 Troubleshooting 5

4 Oracle Express Database 6

4.1 Installation 6

4.2 Quickstart 8

4.2.1 Start the database 8

4.2.2 Login at the home page 8

4.2.3 Create a user 8

4.2.4 Install SQL Developer 9

4.2.5 Creating a Connection in SQL Developer. 9

4.2.6 Tutorial: Creating Objects for a Small Database 10

4.2.7 Un-locking the Sample User Account 12

4.2.8 Creating an Application Using Application Express 13

4.3 Offline Documentation 13

4.4 Links 13

5 Forms Quickstart 14

5.1 Start Forms 14

5.1.1 Start Weblogic Server 14

5.1.2 Weblogic Server URLs 14

5.1.3 Test forms 14

5.2 Tutorial 15

5.2.1 Prerequisites 16

5.2.2 Step 1 - Setting Up the Development Environment 16

5.2.3 Further Steps 17

5.3 Deploying an Application. 17

6 High Availability Configuration 18

6.1 Installation of Oracle Linux 5.8 on Virtual Box 19

6.2 Extending the file system 19

6.3 Network settings 22

6.3.1 Editing the hosts files. 22

6.3.2 Configure Linux Network 23

6.4 Testing the network 23

6.5 Linux Users. 23

6.6 Forms installation 24

6.7 Java Installation 24

6.8 WLS Installation 25

6.9 Forms installation 25

6.10 Forms Configuration 29

6.11 Testing the installation 30

6.11.1 Starting the server 30

6.11.2 Checking Browser URLs: 30

6.12 Next Steps 31

6.13 Troubleshooting Forms Configuration 31

6.13.1 Log file review 32

6.13.2 Testing the elements of the Installation 32

6.13.3 Removing Instance asinst_1 33

7 Links 34

7.1 Download Links 34

7.1.1 JRockit 64-Bit for Windows R28.2.3 34

7.1.2 JRockit 64-Bit for Windows R28.2.4 34

7.1.3 Weblogic Server 10.3.6 Generic 64-bit 34

7.1.4 Weblogic Server 10.3.5 32-Bit for Windows 34

7.1.5 Oracle Forms and Reports 11g Release 2, Windows 64-bit 35

7.1.6 Oracle Forms and Reports 11g Release 2, Windows 32-bit 35

7.1.7 Database Express Edition 11g Release2 35

7.1.8 Java SE Development Kit 6 Update 33 for Windows x64 35

7.1.9 Oracle Linux Release 5 Update 8 for x86_64 (64 Bit) 35

7.1.10 Java SE Development Kit 6 Update 33 for Linux x64 35

7.1.11 Oracle Forms and Reports 11g Release 2 for Linux 64 bit 36

7.1.12 Oracle Forms 11g Demo Pack 36

7.1.13 Oracle SQL Developer 3.1 (3.1.07.42) February 7, 2012 36

7.2 Documentation Links 36

8 Conclusion 37

2 Introduction

Oracle Forms and Reports is part of the Fusion Middleware stack, and although we can find many recommendations to use ADF for new developments, many companies stick to their legacy applications, developed with Forms, and are quite happy with it. Meanwhile Oracle has lifted this traditional two tiered client-server technology on the application server, thus improving scalability and high availability, while maintaining backward compatibility. As it is the case with all Fusion Middleware products, Forms is based on WLS. The development configuration uses a single server domain, whereas the high availability solution is based on WLS cluster technology and can span multiple machines. We want to have a look at both configurations. We will start with the simplest installation form, which is the development environment that uses only a single Weblogic Server. This is enough to run a Forms tutorial, which is included in Oracles documentation. This tutorial instructs us to create a master detail form application and provides a good overview of the technology and development approach.

Before running the tutorial however, we need to install and configure a database. Oracle Express edition is sufficient for our purposes. We will look at the installation and some samples. Eventually we will install Forms for a second time, in a Virtual Box Linux machine. This time we choose a production configuration, including an entry HTTP server which load-balances the requests to the WLS tier. This installation also includes the enterprise manager. While this installation is yet not clustered, we will briefly discuss the steps to setup a cluster and span it across two machines.

3 Installation of the Forms and Reports Development Environment

We want to install Forms and Reports on Windows 7 64-bit. We choose the development installation option which means that we install Forms in a single WLS server instance without a separate Admin server. As a consequence the Enterprise Manager is not available.

First we have a look at the Certification Matrix in Excel (http://www.oracle.com/technetwork/developer-tools/forms/oracle-forms-11gr2certmatrix-519680.xls ).

We follow the instructions from the Quick Installation Guide for Oracle Forms and Reports http://docs.oracle.com/cd/E17904_01/install.1111/e12003/claqi.htm

Since there might be problems on Windows systems with too long pathnames we plan the directory structure of the installation with the following path names.

Middleware Home D:\12Forms

Java Home D:\12Forms\jR28-64

Weblogic Server Home D:\12Forms\wlserver_10.3

Oracle Forms Home D:\12Forms\FRHome1

Oracle Common Home D:\12Forms\oracle_common

Domain Home D:\12Forms\domains\ClassicDomain

Instance Home D:\12Forms\asinst_3

Table 1. Pathnames for the Forms Installation on Windows

3.1 Java Installation

Installation of jrockit-jdk1.6.0_33-R28.2.4-4.1.0-windows-x64.exe

Installation location=D:\12Forms\jR28-64

We simply run the installer executable and go with the defaults. We use the download from 7.1.2.

We test the version:

C:\Users\uScorpio>set JAVA_HOME=D:\12Forms\jR28-64

C:\Users\uScorpio>%JAVA_HOME%\bin\java -version

java version "1.6.0_33"

Java(TM) SE Runtime Environment (build 1.6.0_33-b03)

Oracle JRockit(R) (build R28.2.4-14-151097-1.6.0_33-20120618-1634-windows-x86_64, compiled mode)

Ok.

3.2 Weblogic Installation

We use the download of the generic installation archive from 7.1.3.

We run the following command to start the installer.

cd D:\12Forms\

set JAVA_HOME=D:\12Forms\jR28-64

set PATH=%JAVA_HOME%\bin;%PATH%

java -version

%JAVA_HOME%\bin\java -jar wls1036_generic.jar

Ok.

3.3 Forms and Report Installation

We use the unpacked installation from the download at 7.1.5.

We run the following commands to start the installer.

cd D:\01Downloads\ofm_frmrpts_win_11.1.2.0.0_64_disk1_1of1\Disk1

setup.exe

Location of the installation logfile:

Log=C:\Program Files\Oracle\Inventory\logs\install2012-07-18_06-38-20PM.log

3.4 Forms and Reports Configuration

We set up a development configuration by starting the configuration tool with the following commands.

cd D:\12Forms\FRHome1\bin

config.bat

Logfile=C:\Program Files\Oracle\Inventory\logs\install2012-07-18_08-10-43PM.log

We save the summary to D:\12Forms\configuration_summary.txt

Typ: Oracle Forms and Reports-Installation

Konfigurationsoptionen

Speicherort von Middleware Home: D:\12Forms

Speicherort von Oracle Home:D:\12Forms\FRHome1

Speicherort der Oracle-Instanz:D:\12Forms\asinst_3

Oracle-Instanz:asinst_3

Domainoption: Create Domain

Domainname:ClassicDomain

Domainstandardverzeichnis:D:\12Forms\domains\ClassicDomain

Domainhostname: 192.168.178.103

Domain-Port-Nummer: 7001

Benutzername: weblogic

Automatische Port-Erkennung:true

Administratorkonsole: http://192.168.178.103:7001/console

Forms-URL: http://192.168.178.103:7001/forms/frmservlet

Reports-URL: http://192.168.178.103:7001/reports/rwservlet

Ok.

3.5 Troubleshooting

The configuration of Forms on Windows fails repeatedly. The installer fails in restarting weblogic. In the installation Log we find the line:

java.lang.Exception: "\Java\jre6\lib\ext\QTJava.zip" kann syntaktisch an dieser Stelle nicht verarbeitet werden.

Further investigations reveal that the variable CLASSPATH in the system environment is set to

CLASSPATH=.;C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip

We login to Windows as Administrator and change it to

CLASSPATH=.

After that the installation succeeds.

4 Oracle Express Database

We install the latest version of the Oracle Express Database to be used with the Forms installation.

4.1 Installation

We use the following installer: D:\01Downloads\OracleXE112_Win32\DISK1\setup.exe.

According to the Installation Guide the user for installations needs to be a member of the Administrators group.

We run the installation, using “run as Administrator”.

We choose the password “welcome1” for the SYS and SYSTEM database accounts.

The summary of the installation is given in the following figure.

Figure 1. Summary of the Database 11g Express Installation.

We get the following error:

Figure 2. Error during installation.

The directory C:\Users\aScorpio\AppData\Local\Temp\{2D01C272-ADE9-45A9-A7FD-6B7F4EB8AE97} exists, but the file KEY_XE.reg does not. So it is unclear what this error means. The installation proceeds successfully.

After starting the database and running Quick start from the start menu the following error appears:

Obviously the database is not correctly installed.

We run an uninstall, add the user uScorpio to Administrators, reboot and install again.

Again we get the following error:

But the installation proceeds successfully again. But we get the same error message from the “Get Started” desktop icon.

The URL http://127.0.0.1:8080/apex/f?=4950 delivers a http Bad Request.

The database creation logs at D:\10Oracle\03Database\oraclexe\app\oracle\product\11.2.0\server\config\log don’t contain any errors.

The logfile OracleDatabaseXEServerInstall.log cannot be found on the system.

We reboot the computer and start the database from the start menu. (Run->All Programms->Oracle Database 11g Express->Start Database.

After that the “Get Started” icon starts a browser with the database start screen.

Figure 3. Database start screen which shows after reboot and database start.

Ok.

4.2 Quickstart

We follow the instructions in the “Getting Started Guide” to become acquainted with the database. (http://docs.oracle.com/cd/E17781_01/admin.112/e18585/toc.htm)

4.2.1 Start the database

We use the Desktop Icon “Start database” which executes:

C:\Windows\SysWOW64\cmd.exe /k D:\10Oracle\03Database\oraclexe\app\oracle\product\11.2.0\server\bin\StartDB.bat

4.2.2 Login at the home page

We browse to http://127.0.0.1:8080/apex/f?p=4950 and click “storage”.

From the Login screen we login as system/welcome1

4.2.3 Create a user

We start the SQL command line from the windows start menu which executes the command:

D:\10Oracle\03Database\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog

We connect and create a user and grant privileges.

SQL> connect

Enter user-name: system

Enter password:

Connected.

SQL> create user chris identified by welcome1;

User created.

SQL> grant connect, resource to chris;

Grant succeeded.

SQL> grant create view to chris;

Grant succeeded.

4.2.4 Install SQL Developer

We install SQL Developer by unpacking the file to D:\10Oracle\03Database\sqldeveloper64-3.1.07.42-no-jre\sqldeveloper\

We use this java executable D:\13Java\03JavaSE6\jdk1.6.0_33-64bit\bin

C:\Users\uScorpio>D:\13Java\03JavaSE6\jdk1.6.0_33-64bit\bin\java.exe -version

java version "1.6.0_33"

Java(TM) SE Runtime Environment (build 1.6.0_33-b05)

Java HotSpot(TM) 64-Bit Server VM (build 20.8-b03, mixed mode)

We start SQL Developer with D:\10Oracle\03Database\sqldeveloper64-3.1.07.42-no-jre\sqldeveloper\sqldeveloper.exe

We create an icon in the start menu.

4.2.5 Creating a Connection in SQL Developer.

We manually create a connection for Chris as depicted in the following figure:

Figure 4. Manually creating a connection with SQL Developer.

4.2.6 Tutorial: Creating Objects for a Small Database

We work through the tutorial from the Oracle SQL Developer User’s Guide on Creating Objects for a small database. (http://docs.oracle.com/cd/E18464_01/doc.30/e17472/tut_library.htm#CBACFDCH)

Here are some remarks to the chapters.

In “4.8 Debug a PL/SQL Procedure”

We need to grant more privileges to user Chris. We create a new connection (as SYSDBA) with the user sys/welcome1 an run the commands in a SQL worksheet.

grant DEBUG CONNECT SESSION to chris;

grant DEBUG ANY PROCEDURE to chris;

Now we go back to the session from Chris and continue.

In “4.10 Script for Creating and Using the Library Tutorial Objects” the whole tutorial is summarized as the following sql script, which we format, using the SQL editor from eclipse.

-- Clean up from any previous tutorial actions.

DROP TABLE transactions;

DROP TABLE books;

DROP TABLE patrons;

DROP SEQUENCE patron_id_seq;

DROP SEQUENCE transactions_seq;

DROP TRIGGER transactions_trg;

DROP VIEW patrons_trans_view;

DROP PROCEDURE list_a_rating;

DROP PROCEDURE list_a_rating2;

set serveroutput on

-- Create objects.

CREATE TABLE books (

book_id VARCHAR2(20),

title VARCHAR2(50)

CONSTRAINT title_not_null NOT NULL,

author_last_name VARCHAR2(30)

CONSTRAINT last_name_not_null NOT NULL,

author_first_name VARCHAR2(30),

rating NUMBER,

CONSTRAINT books_pk PRIMARY KEY (book_id),

CONSTRAINT rating_1_to_10 CHECK (rating IS NULL OR

(rating >= 1 and rating <= 10)),

CONSTRAINT author_title_unique UNIQUE (author_last_name, title));

CREATE TABLE patrons (

patron_id NUMBER,

last_name VARCHAR2(30)

CONSTRAINT patron_last_not_null NOT NULL,

first_name VARCHAR2(30),

street_address VARCHAR2(50),

city_state_zip VARCHAR2(50),

location MDSYS.SDO_GEOMETRY,

CONSTRAINT patrons_pk PRIMARY KEY (patron_id));

CREATE TABLE transactions (

transaction_id NUMBER,

patron_id CONSTRAINT for_key_patron_id

REFERENCES patrons(patron_id),

book_id CONSTRAINT for_key_book_id

REFERENCES books(book_id),

transaction_date DATE

CONSTRAINT tran_date_not_null NOT NULL,

transaction_type NUMBER

CONSTRAINT tran_type_not_null NOT NULL,

CONSTRAINT transactions_pk PRIMARY KEY (transaction_id));

CREATE SEQUENCE patron_id_seq

START WITH 100

INCREMENT BY 1;

-- The sequence for the transaction_id

-- in the tutorial is created automatically,

-- and may have the name TRANSACTIONS_SEQ.

CREATE SEQUENCE transactions_seq

START WITH 1

INCREMENT BY 1;

-- The before-insert trigger for transaction ID values

-- in the tutorial is created automatically,

-- and may have the name TRANSACTIONS_TRG.

CREATE OR REPLACE TRIGGER transactions_trg

BEFORE INSERT ON TRANSACTIONS

FOR EACH ROW

BEGIN

SELECT TRANSACTIONS_SEQ.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;

END;

CREATE VIEW patrons_trans_view AS

SELECT p.patron_id,

p.last_name,

p.first_name,

t.transaction_type,

t.transaction_date

FROM patrons p, transactions t

WHERE p.patron_id = t.patron_id

ORDER BY p.patron_id, t.transaction_type;

-- Procedure: List all books that have a specified rating.

CREATE OR REPLACE PROCEDURE list_a_rating(in_rating IN NUMBER) AS

matching_title VARCHAR2(50);

TYPE my_cursor IS REF CURSOR;

the_cursor my_cursor;

BEGIN

OPEN the_cursor

FOR 'SELECT title

FROM books

WHERE rating = :in_rating'

USING in_rating;

DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');

LOOP

FETCH the_cursor INTO matching_title;

EXIT WHEN the_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(matching_title);

END LOOP;

CLOSE the_cursor;

END;

show errors;

-- Insert and query data.

INSERT INTO books VALUES ('A1111', 'Moby Dick', 'Melville', 'Herman', 10);

INSERT INTO books VALUES ('A2222', 'Get Rich Really Fast', 'Scammer', 'Ima', 1);

INSERT INTO books VALUES ('A3333', 'Finding Inner Peace', 'Blissford', 'Serenity', null);

INSERT INTO books VALUES ('A4444', 'Great Mystery Stories', 'Whodunit', 'Rodney', 5);

INSERT INTO books VALUES ('A5555', 'Software Wizardry', 'Abugov', 'D.', 10);

INSERT INTO patrons VALUES (patron_id_seq.nextval,

'Smith', 'Jane', '123 Main Street', 'Mytown, MA 01234', null);

INSERT INTO patrons VALUES (patron_id_seq.nextval,

'Chen', 'William', '16 S. Maple Road', 'Mytown, MA 01234', null);

INSERT INTO patrons VALUES (patron_id_seq.nextval,

'Fernandez', 'Maria', '502 Harrison Blvd.', 'Sometown, NH 03078', null);

INSERT INTO patrons VALUES (patron_id_seq.nextval,

'Murphy', 'Sam', '57 Main Street', 'Mytown, MA 01234', null);

INSERT INTO transactions (patron_id, book_id,