How to setup Webutil for forms 11g R1 (11.1.1.2.0)

Notes:
– frmwebutil.jar already exists/is signed on 11g
– tested with JRE version 1.6.0_25, webutil 1.0.6, jacob 1.10.1_b17
– windows 7, 32bit

1. Download webutil_1.0.6  (or from oracle’s index page) and jacob_1.10.1_b17 (or from project page) jar files.

2. Copy files:
=========
path = c:\Oracle\Middleware\as_1\forms\java
[text]d2kwut60.dll
ffisamp.dll
forms_base_ie.js
forms_ie.js
frmwebutil.jar
jacob.dll
jacob.jar
JNIsharedstubs.dll
sign_webutil.bat[\text]

*Create a folder “server” under forms\:
path = c:\Oracle\Middleware\as_1\forms\server\
[text]base.htm
basejpi.htm
forms.conf.backup
ftrace.cfg
webutil.cfg
webutilbase.htm
webutiljini.htm
webutiljpi.htm[/text]

*May also need the following folder:
path = c:\Oracle\Middleware\as_1\forms\webutil\
[text]d2kwut60.dll
ffisamp.dll
jacob.dll
JNIsharedstubs.dll
sign_webutil.bat[/text] (more…)

How to install Oracle Application Express 4.1 with Database XE 11.2g

Download and install Oracle Database XE 11.2g

Download Oracle Application Express 4.1 (APEX)

Tip: In case you get an ORA-12560 error, your database listener has not started.
Either start it by running “c:\oraclexe\app\oracle\product\11.2.0\server\bin\StartDB.bat” or use  the “Start DB” shortcut from the start menu.

Connect as SYS to the database console
[bash]conn sys/passw as sysdba[/bash]

Run the following sql command to setup APEX
[sql] @c:\apex\apexins myapp myapp  temp /i/
exit[/sql]

How to set PL/SQL Developer or TOAD to use a different oracle instance

Whether you have an oracle home setup or not:

* copy/setup PL/SQL Developer or TOAD to your system
* copy/setup Oracle Instantclient to your system
* add a System PATH Variable for Instantclient i.e.

[text]c:\users\oracle\instantclient

-if this is a 2nd instance add it after 1st instance:
c:\Oracle\Middleware\as_1\bin;c:\users\oracle\instantclient[/text]

* Run PL/SQL Developer or TOAD and configure to use the new instance and tns names

PLSQL: Tools > Preferences > set oracle_home and oracle_oci dll paths
TOAD: If set correctly, it will find the new instance

Note: recommended to use the same tnsnames.ora path for all applications

 

How to set PL/SQL Developer to use a different HOME and OCI library

From within PL/SQL Developer:

Tools > Preferences, Under Oracle > Connection

* Oracle Home… (point to where tnsnames.ora file is, under the Oracle Client Installation)

[text]c:\Users\<username>\oracle\instantclient\network\admin\[/text]

* OCI Library… (point to where the OCI library is placed of the same Oracle Installation)

[text]c:\Users\<username>\oracle\instantclient\bin\OCI.dll[/text]

 

Trace and Alert log files on Oracle Database Server (11g)

Trace and Alert log files

Root ‘log dump’ location:
[sql]select value from v$parameter where name = ‘background_dump_dest’;[/sql]
[text]/u01/app/oracle/diag/rdbms/db_name/trace[/text]

Find the trace file path of current session use the following PL/SQL snippet:
[sql]select u_dump.value || ‘/’ || db_name.value || ‘_ora_’ || v$process.spid ||
nvl2(v$process.traceid, ‘_’ || v$process.traceid, null) || ‘.trc’ “Trace File”
from v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session on v$process.addr = v$session.paddr
where u_dump.name = ‘user_dump_dest’
and db_name.name = ‘db_name’
and v$session.audsid = sys_context(‘userenv’, ‘sessionid’);[/sql]

This would output something similar to:
[text]/u01/app/oracle/diag/rdbms/db_name/trace[/text]

The ALERT file would be:
[text]/u01/app/oracle/diag/rdbms/db_name/trace/alert_<db_name>.log[/text]

You could also create a DB table that would keep the alert log file and you do a select
to view a list of the logged items.

 

Credits to: Rene Nyffenegger

 

How to build a Java app and load it into an Oracle DB

Create the Java class

[java]public class Hello2 {

public static String hello() {
return “Hello World.”;
}

public static String hello(String name) {
return “Hello ” + name + “.”;
}

public static void main(String[] args) {
System.out.println(Hello2.hello());
System.out.println(Hello2.hello(“Java”));
}
}[/java]

Test the output (console):

[bash]java Hello[/bash]
Output:
[text]Hello World.
Hello Java.[/text]
After compilation, run the following (via puntty on DB server) to load it into the Database (console)

[bash]loadjava -r -f -o -user dbuser/dbpass HelloWorld2.class
# run dropjava to unload any loaded java app/class[/bash]
Calling it from JDeveloper:
[sql]–Create a db connection in JDev as SYS
–R-click on this new connection and select “SQL Worksheet”
–Enter the following in command window:
call dbms_java.LOADJAVA(‘/home/oracle/myapp.jar -verbose -force -grant SCOTT -resolve’);
–to Drop it:
call dbms_java.DROPJAVA(‘/home/oracle/myapp.jar -verbose’);[/sql]

Using a Package (see for Function further down):

[sql]–Create PL/SQL wrapper package bind to a Java class file
CREATE OR REPLACE PACKAGE hello2 AS
–Null argument function header
FUNCTION hello RETURN VARCHAR2;
–One argument function header
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2;
END hello2;
/
–Package body
CREATE OR REPLACE PACKAGE BODY hello2 AS
–Null argument function body
FUNCTION hello RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello() return String’;
–One argument function body
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello(java.lang.String) return String’;
END hello2;
/[/sql]

Now check that the objects are present (sql):

[sql]SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN (‘Hello2’, ‘HELLO2’);[/sql]

The output should be something like:

[text]OBJECT_NAME  OBJECT_TYPE   STATUS
———–  ————  ——
HELLO2 PACKAGE VALID
HELLO2 PACKAGE BODY VALID
Hello2 JAVA CLASS VALID[/text]

Finally, test the package by calling:

[sql]SELECT  hello2.hello(‘Mr Java’) FROM dual;[/sql]

Output:

[text]HELLO2.HELLO(‘MRJAVA’)
———————————–
Hello Mr Java.[/text]

Using a PL/SQL Function rather than a Package

[sql]create or replace function hello2 return varchar2 as
language java name ‘Hello2.hello() return java.lang.String’;[/sql]

To call the function, from sqlplus set a variable and dump the string in there:
[sql]variable tmp varchar2(20);
call hello2.hello() into :tmp;[/sql]
Output:
[text]tmp
———
Hello World.[/text]