Installing JRE Plug-in for Oracle Developer Suite 10g

Hi all,

Today i tried to Installed JRE Plugin for Oracle developer suite 10g ( Yeah right i m late :D) our applications are mainly in Client/Server (Developer 6i based) and we are moving to towards migration any ways lets see how to install JRE Plug-in to work with Developer suite 10g

- Download JRE from http://java.sun.com/javase/downloads/index.jsp Choose JRE then Select Windows in your Platform and Choose Windows Offline Installation.

- Run the downloaded file and install it.

-  Set the configuration section in ur $ORACLE_HOME/forms/server/formsweb.cfg file similar to this
(Thanks Francois Degrelle for configurations settings)

[jreconfig]
baseHTMLJInitiator=webutiljpi.htm
jpi_download_page=http://java.sun.com/products/archive/j2se/1.4.2_09/index.html
jpi_classid=clsid:8AD9C840-044E-11D1-B3E9-00805F499D93
jpi_codebase=http://java.sun.com/products/plugin/autodl/jinstall-1_4-windows-i586.cab#Version=1,4,0,0
WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384 
archive=frmall.jar


-archive_jini will replace with archive


- Set Form builder Runtime settings to use JRE Plugin goto Edit > Preferences > Runtime  > Application Server URL 

http://myappserver.com:8889/forms/frmservlet?config=jreconfig

- Run the Form and sign the JRE Plugin


-Check java console for confirmation

For webutil configuration check here
Cheers,
Baig

Open File Dailog box example using Webutil CLIENT_GET_FILE_NAME Forms 10g

Today i m going to show the How to configure Oracle WebUtil Package with Oracle forms 10g in simple steps with working example that i have created.

In this example we'll upload a Text file (Fields separated by Comma ) into Oracle Forms data block.
Download Text file and FMB
Before we start we have to configure WebUtil  to work with Forms here are the steps

Updated July 14, 2010 A good Forms 10g Configuration manual by my collegue Mr.Moiz can be found here



Configuring WebUtil at OS:

1.Download WebUtil http://www.oracle.com/technology/software/products/forms/files/webutil/webutil_106.zip
2. Download Java Com Library http://prdownloads.sourceforge.net/jacob-project/jacob_18.zip
3. Extract both zips to some directory.
4. Copy
  File frmwebutil.jar
  From folder: webutil_106\webutil_106\java  To: $ORACLE_HOME/forms/java

  File: jacob.jar 
  From folder: jacob_18  To: $ORACLE_HOME/forms/java

  File: d2kwut60.dll, JNIsharedstubs.dll
  From folder: webutil_106\webutil_106\webutil To: $ORACLE_HOME/forms/webutil

  File: jacob.dll
 From folder: jacob_18 To: $ORACLE_HOME/forms/webutil

  File: forms_base_ie.js, forms_ie.js
- From folder: webutil_106\webutil_106\java To: $ORACLE_HOME/forms/java

 File: webutil.olb, webutil.pll and create_webutil_db.sql
 From folder: webutil_106\webutil_106 To: $ORACLE_HOME/forms

 File: webutilbase.htm, webutiljini.htm, webutiljpi.htm and webutil.cfg
- From folder: webutil_106\webutil_106\server To: $ORACLE_HOME/forms/server

File: sign_webutil.bat
- From folder: webutil_106\webutil_106\webutil To: $ORACLE_HOME/forms/webutil


5. In $ORACLE_HOME/forms/server/formsweb.cfg file
Change the following entries

# Forms applet archive setting for JInitiator
archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar 

if you use JRE plugin then this line also required
archive=frmall_jinit.jar,frmwebutil.jar,jacob.jar 

[webutil]
WebUtilArchive=/forms/java/frmwebutil.jar,/forms/java/jacob.jar

 6. In $ORACLE_HOME/forms/server/default.env file change the following entries

  FORMS_PATH=C:\YourOracle_Home\forms;C:\YourOracle_home\forms\webutil

  # webutil config file path
  WEBUTIL_CONFIG=C:\YourOracle_Home\forms\server\webutil.cfg

Add frmwebutil.jar, jacob.jar into Classpath 

CLASSPATH=C:\YourOracle_Home\forms\java\frmwebutil.jar;C:\YourOracle_Home\forms\java\jacob.jar

 7. Add frmwebutil.jar and Jacob.jar path entries into Windows Registry (Start > Run > type regedit and press enter) goto HKEY_LOCAL_MACHINE > SOFTWARE > ORACLE > DEVSUITE_HOME > FORMS_BUILDER_CLASSPATH Variable.
Note: if you are using Linux then instead of this setup the environment variable.

8. Sign Webutil JAR files by running these following commands Make sure Form Builder is Closed

C:\DevSuiteHome_1\forms\webutil>sign_webutil.bat c:\DevSuiteHome_1\forms\java\frmwebutil.jar
C:\DevSuiteHome_1\forms\webutil>sign_webutil.bat c:\DevSuiteHome_1\forms\java\jacob.jar

Configuring Database:

1. Connect as Scott or your User
2. Run the script $ORACLE_HOME/forms/create_webutil_db.sql using SQLPLUS or any tool.

Setting up Form Builder:

1. Open Form builder Connect with SCOTT and in PL/SQL libraries open Webutil.pll file
2. File > Save as and Rename the file to different name e.g WebUtil_lib
(Trust me it works to avoid Webutil not found error) :)
3. Compile the new PLL file and generate it
4. Attached the new named Webutil_lib.pll to your form with Remove Path option YES.
5. Open WebUtil.olb file Object group in builder and copy or subclass it into your form.
6. Now compile your form and run it.

Note: Browser will ask to certify these libraries first time Choose ALWAYS certify. Check Java Console icon on the task bar and see frmwebtul.jar and jacob.jar are loaded successfully.
7. Press Browse button on form and choose Datafile.txt and Press Open...Text file will load into the Data Block.

Feel free to comment on this if i missed something plz inform me.

Check this Manual for further info on WebUtil
http://www.oracle.com/technology/products/forms/htdocs/webutil/web_util.pdf

Cheers,
Baig

Oracle Forms 10g ORA-01017-invalid username/password while running on Oracle 11g

I recently installed Oracle11g on Windows to work with Oracle developer Forms 10g everything works fine but when i run the form in browser from builder i got error

ORA-01017 Invalid username/password logon denied

i was confused what to do but problem solved...

In Oracle 11g a new parameter called SEC_CASE_SENSITIVE_LOGON which is defaults to TRUE for case sensitive passwords. My SCOTT password was "tiger" is small case and Forms 10g was taking it as "TIGER" on runtime.

Solution 1:
- Connect as sys and change the parameter to false.

alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;

Solution 2:
- Connect as DBA and change the password of SCOTT to LETTER case.

alter user scott identified by TIGER;

Cheers,
Baig

CPU Usage Overhead EM Dbconsole 10g / Failed to shutdown DBConsole gracefully

While working on Oracle 10gR2 On Linux i noticed CPU consumption was much high and in Top activity section it was showing that SYSMAN user with OMS Program is looping since morning i decided to stop EM dbconsole using

$ emctl stop dbconsole

but error msg came....

Stopping Oracle Enterprise Manager 10g Database Control ...

--- Failed to shutdown DBConsole Gracefully ---

Oracle Support comes with this solution.....

Cause:

The server has two active NIC cards and therefore two active IP addresses. DNS resolves to the IP address of eth1 and /etc/hosts lists the ip address of eth0.

Solution

To implement the solution, please execute the following steps:

1. Ensure that nslookup returns the ip address of the primary network adaptor, normally eth0.
2. set the environment variable ORACLE_SID to
3. Run the following command to remove the exisiting Database Control configuration: emca -x
4. Run the following command to recreate the Database Control configuration whilst leaving the Database Control repository intact (-r) : emca -r
  4.1 optionally, specify the flags -c and -a to configure Database Control for a RAC database and for monitoring of ASM respecitively, e.g. emca -r -c | emca -r -a | emca -r -c -a

I googled the CPU usage problem but found solutions for Oracle 11g (click here) not working for 10g so i decided to kill the emctl process and restart dbconsole coz Oracle solution will delete the Repository information

Here are the steps:

--Kill the process of DbConsole

1. $cat $ORACLE_HOME/HOSTNAME_SID/emctl.pid
2. Kill the process id that return from that file.
$kill -9 1234
3. start the dbconsole using
$emctl start dbconsole

After restarting Dbconsole that CPU utilization problem was resolved.


Cheers,
Baig

DBCA not Starting Oracle11gR2 Exception in thread "main" java.lang.NoClassDefFoundError: oracle/i18n/text/OraMapTable

Few days ago i tried to install Oracle11g Release 2 on Oracle Enterprise Linux 5 (OEL) on my Macbook Pro using VMware Fusion 3 to Virtualize Linux while installing i was getting error about missing files

Here is the error screen  http://img682.imageshack.us/i/11gerror.tif/

I was ignoring the error and thought that may be 11g is not supported on VMware Fusion and OEL setup because i was not able to install Oracle10gR2 as well from DVD on OEL5 which is working fine on RedHat Linux AS 4 on my laptop.




After installing (By ignoring missing files) i was running dbca and error was coming




Exception in thread "main" java.lang.NoClassDefFoundError: oracle/i18n/text/OraMapTable
        at oracle.i18n.util.OraLocaleInfo.(OraLocaleInfo.java:113)
        at oracle.sysman.assistants.util.attributes.InitParamAttributes.fillCharacterSets(InitParamAttributes.java:2344)
        at oracle.sysman.assistants.util.attributes.InitParamAttributes.(InitParamAttributes.java:528)
        at oracle.sysman.assistants.util.step.StepContext.(StepContext.java:309)
        at oracle.sysman.assistants.dbca.backend.Host.(Host.java:778)
        at oracle.sysman.assistants.dbca.ui.UIHost.(UIHost.java:257)
        at oracle.sysman.assistants.dbca.ui.InteractiveHost.(InteractiveHost.java:54)
        at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:164)
        at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:112)
        at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:184)




I posted the my problem at OTN Forums also

http://forums.oracle.com/forums/thread.jspa?threadID=984585&tstart=0


Any ways Problem was in ZIP file that i downloaded the chksum was showing wrong information so i downloaded the file linux.x64_11gR2_database_1of2.zip again and everything works perfect..
at OTN the file


linux.x64_11gR2_database_1of2.zip (1,239,269,270 bytes) (cksum - 3152418844)
linux.x64_11gR2_database_2of2.zip (1,111,416,131 bytes) (cksum - 3669256139)

Solution:

--Run on Linux to make sure cksum is correct
cksum linux.x64_11gR2_database_1of2.zip
cksum linux.x64_11gR2_database_2of2.zip


i will recommend plz make sure cksum is returning same value as mentioned before installing.

Cheers,
Baig

PL/SQL Function to Return Table Type

As i described in my previous post PL/SQL Procedure to return Table Type. In this post i m goin to show the same thing but with database Function to return multiple rows.

--Create Database Type as Object

create or replace type TypeForFunction
as
object
(EmpCd number,
EmpName Varchar2(60),
DepartCode number,
Salary number );

--Create table of the Type

Create or replace type TableForFunction as table of TypeForFunction; 

--Create Database Function

Create or replace
FUNCTION MyFunction
(deptCode number) RETURN TableForFunction
is 
OutTableParam TableForFunction;
begin
select cast(
Multiset(select empno, ename, deptno, sal
from scott.emp
where deptno = deptcode)
as TableForFunction)
into outtableparam
from dual;

return OutTableParam;
end;

--To return values from this function

SQL> select * from table(MyFunction(10));

Output would be like



EMPCD                 EMPNAME          DEPARTCODE      SALARY                
---------------------- ------------------------ ---------------------- ----------------------
7782                   CLARK                       10                     2450                  
7839                   KING                          10                     5000                  
7934                   MILLER                     10                     1300             

PL/SQL Procedure to Return Table Type

Few days ago I got a requirement in my company to provide a 'Manager-wise Infected Portfolio' which will show Managers with No of Contracts in their Portfolio, current overdue amount, outstanding exposure and last three month's exposure based of cutoff date user is providing.

Pattern look likes this

Name  No of Contracts / OverdueAmount / ThisMonthExposure /  Exposure Month3 / Exposure Month2 / Exposure Month1

Solution:

So I decided to solve this task using Database procedure which will take some inputs and return whole PL/SQL Table type containing multiple rows of the same contract but data of different Months.....I used that procedure in a form and when user press the button it inserts all rows in a database table to generate a report.

Using this operation the report generation process became faster than my older work working directly into Form application.

Any ways come to the example here are the steps to implement this kind of requirement.

Note: For some reasons i m not showing my exact code but just showing the concept of my logic that I've implemented for this Example u need traditional SCOTT schema in Oracle database.

Example:

In This Example I m going to show the PL/SQL procedure to receive input parameter DeptCode (The Department code from Employee table) and return all employees of the same department but as PL/SQL Table

--Create a Package which will contain structure of your returning table (You can do it database object types as well)

Create or replace Package MyTablePackage as

-- Create a Record type for Table

TYPE MyRecord
IS RECORD
(EmpCd number,
EmpName Varchar2(60),
DepartCode number,
Salary number );

--Declaring a Returing Table type

TYPE MyTableType IS TABLE OF MyRecord
INDEX BY BINARY_INTEGER;

-- Procedure will use that Table type as OUT parameter
PROCEDURE MyProcedure
(DeptCode number,
OutTableParam OUT MyTableType);
end MyTablePackage;


--Next is to create Package body.
Create or replace PACKAGE BODY MYTABLEPACKAGE AS
  PROCEDURE MyProcedure
  (DeptCode number, OutTableParam OUT MyTableType) AS
      v_counter number := 0;
  BEGIN
     for EmpCursor in
         (select empno, ename, sal
           from scott.emp
           where deptno = deptcode
           order by empno)
     loop
       v_counter := v_counter + 1;
       OutTableParam(v_counter).EmpCd := EmpCursor.empno;
       OutTableParam(v_counter).EmpName := EmpCursor.ename;
       OutTableParam(v_counter).DepartCode := Deptcode;
       OutTableParam(v_counter).Salary := EmpCursor.sal;
    end loop;  
  END MyProcedure;
END MYTABLEPACKAGE;



---To Verify the output

Set serveroutput on
declare
   v_counter  BINARY_INTEGER := 1;
   v_mytable  MyTablePackage.MyTableType;
begin
   --Passing Parameters to Procedure Department No 10
    MyTablePackage.MyProcedure(10, v_mytable);

 

  while v_counter <= v_mytable.count
   loop

       dbms_output.put_line(v_mytable(v_counter).EmpCd||'|'||

                                           v_mytable(v_counter).EmpName||'|'||

                                           v_mytable(v_counter).DepartCode||'|'||

                                           v_mytable(v_counter).Salary);
      v_counter := v_counter + 1;
   end loop;
end;


Output would be like this (   MyTablePackage.MyProcedure(10, v_mytable);)

Department No 10
7839|KING|10|5000
7934|MILLER|10|1300

Department No 30 (   MyTablePackage.MyProcedure(30, v_mytable);)
7499|ALLEN|30|1600
7521|WARD|30|1250
7654|MARTIN|30|1250
7698|BLAKE|30|2850
7844|TURNER|30|1500
7900|JAMES|30|950 

Feel free to comment

Cheers,
Baig

 

Creating an Oracle ASM Instance and Database

Hi,

As i explained in my previous post the configuration of Oracle ASM on Linux. Next step is to configure Oracle ASM Instance which will manage our data and our database software.

Here we go

1. Install Base Oracle software (Configuration Software only and ORACLE_SID=ORCL)
Installation of Oracle (You can find thousands of installation guides on google) here the one i found.

After installing Base Software we will install Oracle for ASM in another Oracle Home.

2. Setup your Environment variables to different location mainly ORACLE_HOME and ORACLE_SID
e.g
$ export ORACLE_SID = +ASM
$ export ORACLE_HOME=/u01/app/product/oracle/10.2.0/ASM

3. Run Oracle Installer runInstaller script as oracle user.

4. Select Advance installation  >  Enterprise Edition  > Specify Oracle ASM Home Name and Path

5. Select Configuration option Choose Software only.

6. Run the root.sh script in other terminal login as root comeback to Installer and OK exit the Installer.

7. Run localconfig script exists in $ORACLE_HOME/bin as root user
Note: Executing this command will create the Oracle Cluster Registry (OCR) keys.

$ ./localconfig add

8. Run dbca (Database configuration assistant to create database and instance) and Choose 'Congfiure Automatic Storage Management' Enter Password for SYS (ASM Instance)

8. Create two Disk groups Named DF1 to store datafiles and FRA1 for Flash Recovery area.  Choose Redundancy External. Redundancy has 3 options

High : Trice Mirroring required more disk space.
Normal: Double Mirroring at ASM level.
External : Mirroring is OS dependent.

9. Set ORACLE_HOME and ORACLE_SID to ORCL

10. Run dbca and Follow screen shots.

 

 

 

 

 

Note: Specify the Password of ASM Instance SYS user

 

 

 

 

 

11. After completing Creation of database  edit your /etc/oratab file and mark Y

12. Set ORACLE_SID = +ASM and ORACLE_HOME to ASM Home Start the Instance using sqlplus and then change ORACLE_SID to orcl and ORACLE_HOME to orcl home and start the instance of orcl.

$ sqlplus / as sysdba

SQL> startup

Any comments, queries or suggestions welcome i hope it helps

Cheers,

Baig

Implementing Oracle ASM (Automatic Storage Management) on Linux

Hi all,
This is going to be my first post of Year 2010 so i am going to post about Oracle ASM (Automatic Storage Management) the super hit feature of Oracle10g database it has many benefits most commonly i m explaining here like

- Logical Volume management DBAs are not required to further think about the physical location of the indexes, tables etc etc.
- Performance of Raw Devices
- Centralized Storage management.
- Mirroring / stripping at DB level.

Prerequisites:

1. 2 Raw Partitions
- Not formatted with any file system i.e ext3 etc etc  Minimum 3 GB each for basic software install one for data files and one for flash recovery area.
- Oracle recommends that you create a single whole-disk partition on each disk that you want to use.
- Use fdisk command to setup partitions run the partprobe command after partitioning all disks so your kernel will becomes aware of partition changes.

2. OS user oracle and group dba
#groupadd dba
#useradd oracle -g dba

Here are the Steps to follow

1. Download ASM Libs for your OS and Kernel from http://www.oracle.com/technology/tech/linux/asmlib/index.html 
(In my case i have downloaded Redhat Linux AS 4 Kernel 2.6.9-34.EL)
You can use "uname -r" command to determine exact version of Linux kernel. Make sure you have downloaded files Lib, Support and other RPMs

2. Install ASMLibs in following order to avoid package dependency errors make sure you are logged in as root
a. oracleasm-support
b. oracleasm
c. oracleasmlib

install using this command  (rpm -ihv  i - install /  -h --hash to display / -v verbose)

rpm -ihv package-name

e.g

# rpm -ihv oracleasm-support-2.1.3-1.el4
# rpm -ihv oracleasm-2.6.9-34.EL-2.0.3-1
# rpm -ihv oracleasm-2.6.9-34.ELsmp-2.0.3-1
# rpm -ihv oracleasmlib-2.0.4-1.el4

3. After successfully installing all required ASM packages next is to configure ASMLibs run the following command as root

/etc/init.d/oracleasm configure

Output would be like this

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oracle]: oracle
Default group to own the driver interface [dba]: dba
Start Oracle ASM library driver on boot (y/n) [y]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]


Note: All ASM configuration will be stored at  /etc/sysconfig/oracleasm

4. To enable ASM run the following command

# /etc/init.d/oracleasm enable

5.Labeling Disk with ASM run the following command
(i am using Volume Management as OS level so my device path is different)


# /etc/init.d/oracleasm createdisk  disk-label device-path

e.g

# /etc/init.d/oracleasm createdisk DF1 /dev/sda2
# /etc/init.d/oracleasm createdisk DF2 /dev/mapper/VolGroup00-ASMDf
# /etc/init.d/oracleasm createdisk FRA1 /dev/mapper/VolGroup00-ASMFra

There u go we have completely configured our Linux system to use Oracle ASM still installation part is remaining.

Tip:
Run Oracle database Installer and select software only then install ASM instance in separate Oracle HOME after that you can create database using dbca and select Automatic Storage Management to install ASM Instance. (I will write separate blog post for this installation procedure)

There are bunch of ASM related commands

Usage: oracleasm [--exec-path=] [ ]
       oracleasm --exec-path
       oracleasm -h
       oracleasm -V

The basic oracleasm commands are:
    configure        Configure the Oracle Linux ASMLib driver
    init                  Load and initialize the ASMLib driver
    exit                 Stop the ASMLib driver
    scandisks        Scan the system for Oracle ASMLib disks
    status              Display the status of the Oracle ASMLib driver
    listdisks          List known Oracle ASMLib disks
    querydisk       Determine if a disk belongs to Oracle ASMlib
    createdisk       Allocate a device for Oracle ASMLib use
    deletedisk       Return a device to the operating system
    renamedisk     Change the label of an Oracle ASMlib disk
    update-driver  Download the latest ASMLib driver


For further info on ASM i recommend this book from Oracle Press.


Feel free to comment on this post if i missed something plz inform me. i hope this post will help someone to implement Oracle ASM

Best Regards,
Baig