Configuration of WebUtil to know client information

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



[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 into Registry of window into FORMS_BUILDER_CLASSPATH Variable.

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

Manually upgrade Database server from 10.2.0 to 11.1.0 (10g to 11g)

How to upgrade R12 Database server from 10.2.0 to 11.1.0 (10g to 11g) manually ?


We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home

as a separate ORACLE_HOME in parallel to 10g Oracle Home.

We have to install the new database for upgrading the existing database.

But before installing check the software versions for.....

a)Oracle database version and check the appropriate upgrade path accordingly depending on the present version.

b)E-business suite version---minimum is 12.0.4 and apply interoperability patch for 11g on apps node using adpatch

c)Autoconfig has to be upgraded to latest version by applying 7207440.

I did not need to apply as my instance is on 12.0.6 and 6728000(12.0.6 upgrade patch) supersedes this autoconfig patch..

Now steps for database installation

1.Apply patch 6400501 to Apps 10.1.0.5 Oracle-home

2. Deregister the current database if you want to change database sid,host,port.And update autoconfig for database also before that.
$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps
contextfile=$CONTEXT_FILE -removeserver
3. Update applications context file with new database parameters....
s_dbhost
s_dbdomain
s_db_serv_sid
s_dbport
s_apps_jdbc_connect_descriptor
to new database values. Don't run auto config on apps tier now....
otherwise apps will not be able to connect to database at all.
Run autoconfig only after complete upgrade process is finished..
Database Installation
The 11.1.0 Oracle home must be installed on the database server node
in a different directory other than the current Oracle home
Log in to the database server node as the owner
of the Oracle RDBMS file system and database instance. Ensure
that environment settings, such as ORACLE_HOME, are set for the new
Oracle home you are about to create, and not for any existing
Oracle homes on the database server node
Choose to install software only (without any default database) in the new location.
After the installation,
Run utlu111i.sql (located in 11g_ORACLE_HOME/rdbms/admin) on source 10g database
and check the output.
SQL>11g_oracle_home/rdbms/admin/utlu111i.sql
It displays warnings and recommends steps to clear the issues.
Time Zone Issue
select * from v$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 3
If time zone file version is less than 4 then apply time zone patch 5632264
This can be done using opatch.
unzip the patch and run opatch going into the unzipped patch directory.
or manually copy the .dat files under 5632264/files/oracore/zoneinfo into
$ORACLE_HOME/oracore/zoneinfo
Bounce the database and check the TIMEZONE version.
Do not forget to take a backup of old zoneinfo directory before this.
Re-run utlu111i.sql after patching the database to record
the new timezone file version.
This time it says something like Database contains stale optimizer statistics.
So..
Gather statistics

Steps to Cloning a Database

The following steps must be on Source -Production instance/Server
Step 1. Shutdown database in normal mode and start it up in restricted mode.
Step 2. Take the backup of control file
Step 3. Shutdown database again in normal mode.
Step 4. Copy /FTP init parameter file ,control file script and all the database file on the destination server/location, once all the files are successfully copied, you may startup the database normally.The following step must be on destination – Clone instance/server.
Step 5. Edit init parameter file and control file script.
Step 6. New Environment setup .
Step 7. Connect with svrmgrl and recreate control file.
Step 8. Open the database in resetlogs.
Step 9. Shutdown the database in normal mode
Step 10. Take the cold backup and start the database in archive/non archive mode.
Details :-

Step 1 – 3 on the Source Server ( Server A)
SQL> connect sys/pass
Connected.
sql> shutdown ;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
Total System Global Area 57124108 bytes
Fixed Size 70924 bytes
Variable Size 40198144 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to trace;

Statement processed.
SQL>
SQL> show parameter user_dump_dest
NAME TYPE           VALUE
----------------------------------- ------- -------------------------
user_dump_dest         string E:\ora816\admin\ora816\udump
Note :- Backup control file will generate in user dump destination as above. Check for the latest Ora.trc . Rename this file to Ctrlprod.sql

Read Text File from Oracle PL/SQL Program

Read Text File from Oracle PL/SQL Program


PROCEDURE get_file_contents(filename varchar2,Nxt in out number ) IS in_file CLIENT_Text_IO.File_Type;
linebuf VARCHAR2(3000);
c number:=0;
Nxt number:=0;
pipe varchar2(35);
BEGIN

--opening the file in READ ONLY Mode 'r'

Select nvl(max(Record_no),0) into Nxt from Qapadmin.Labdata;
in_file := CLIENT_Text_IO.Fopen(filename, 'r');

--CLIENT_Text_IO.Get_Line(in_file, linebuf);--to skip the heading titles

--CLIENT_Text_IO.Get_Line(in_file, linebuf);--to skip the heading seperators

--navigating to data block

go_block('data');

---clear block if contents required to replaced

clear_block;
first_record;

LOOP

---this logic depends on the structure of ur Text file.....play with linebuf variable.

--I am reading the file as Comma seperated and no of column are known in my example.

Nxt:=Nxt+1;

/*:empid and empname is block's item*/

:empid := ltrim(rtrim(substr(linebuf, 1, instr(linebuf,' ',1,1)-1)));
:empname := ltrim(rtrim(substr(linebuf, instr(linebuf,' ',1,1)+1)));
End if;
C:=C+1;
next_record;
CLIENT_Text_IO.New_Line;--to move to next line in file
END LOOP;
EXCEPTION
WHEN no_data_found THEN ---when no line remains this exception will raise...
CLIENT_Text_IO.Fclose(in_file);
first_record;
END;

Steps to Apply Oracle Patch using Opatch

1.backup the whole database;
2.shutdown the databse as
   SQL>Shutdown immediate;
   SDL>exit
3.check any installed patch as
   oracle_home/Opatch Isinventory -detail
4.check for patch conflicts in advanced by running the following command
   Opatch apply -slient -no_bug_superset -report
5.install patch
   oracle_home/Opatch/opatch apply patch_location(like D:\patch\7631957