Bind parameter and Lexical parameter in Oracle Report

What are bind parameter and lexical parameter used for?

A bind reference replaces a single value or expression.To create a bind reference in a query, prefix the parameter name with a colon (:).

Example:

select value1
from test_table
where field_name = :p_b_field_name;


A lexical reference is a text string and can replace any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, or the ORDER BY clause. To create a lexical reference in a query, prefix the parameter name with an ampersand (&).

Example:

select value1
from test_table
order by &p_l_field_name ;

How to create, drop and recreate the Database Control Repository and configuration files using EMCA

If you machine name/ip address changed, then you have to rebuild your dbconsole. The command below can be use in either Windows or Unix platform.


1. Create Database Console
To create the configuration files and repository for Database Console, run:

emca -config dbcontrol db -repos create

2.Drop Database Console
To drop (remove) the configuration files and repository for Database Console, run:

emca -deconfig dbcontrol db -repos drop

3.Recreate Database Console
To recreate the configuration files and repository for Database Console, run:

emca -config dbcontrol db -repos recreate

Slow performance for form embeded with WebUtil 1.0.6

Ever encounter slow performance for form which embeded with WebUtil 1.0.6. This problem can occur on any platform.

The form takes an extra 30-50 seconds to load. This happens while using Webutil 1.0.6 with Forms 9.0.4.1, 10.1.2 and later releases.

Causes:
1. The problem occurs when the NetBIOS over TCP/IP is enabled for the intranet
web clients. WebUtil 1.0.6 has issues when NetBIOS over TCP/IP is enabled.

2. When client access the application using IP address instead of URL with domain name. NetBIOS does computer name to IP address mapping, name resolution via
NETBT.SYS in Windows NT and VNBT.VXD in Windows 95. NetBIOS will be invoked when IP address is used instead or domain name.

Solution:
1. The NetBIOS over TCP/IP should be disabled for the intranet web clients accessing the web forms. IP address to computer name mapping and name resolution could
also be achieved via DNS.

To disable the NetBIOS over TCP/IP, please execute the following steps:

a. Go to the properties of "Network Configuration" on the client
machine.
b. Click on "Internet Protocol(TCP/IP)".
c. Click on "Properties" push button.
d. Click on "Advanced" push button.
e. Click on "WINS" tab.
f. Click the radio button "Disable NetBIOS over TCP/IP.

2. Manually map the IP address at Host file in client machine.
a. Go to path in Windows "C:\WINDOWS\system32\drivers\etc"
b. Edit the "hosts" file using text editor
c. Put in the application IP address and mapping name.
d. Save and close the document.

Setup WebUtil in Windows for Developer Suite 10g or Oracle AS 10g

WebUtil is a powerful component in Oracle Form 9i,10g, but setting up WebUtil is a nightmare for most of the developer. I hope the guideline below is useful for people who wish to setup WebUtil at Windows environment.

Steps to setup WebUtil in Developer Suite or Oracle AS

1. Download WebUtil and Jacob library
a.Webutil_106.zip from: http://www.oracle.com/technology/software/product/forms/files/webutil/webutil_106.zip

b.Extract the zip file and put into the Oracle AS or Developer Suite 10g\forms

c.Download Jacob library from: http://prdownloads.sourceforge.net/jacob-project/jacob_18.zip

d.Extract the zip file then:
I.Put Jacob.jar in \foms\java
II.Put jacab.dll at \forms\webutil

2.Create webutil package in the database. Open sqlplus and run create_webutil_db.sql at \forms. The script will create webutil package in the database user. You need to run create_webutil_db.sql for all database users which need to make use of Webutil.

3.Compile the webutil.pll. You can compile using Oracle Forms Developer or run the command below:

a.frmcmp module=ORACLE_HOME\forms\webutil.pll userid= module_type=library compile_all=yes

4.Setup default.env file at \forms\server
a.The Java runtime Jar rt.jar (including its physical location) file must be included in the CLASSPATH, along with the Java runtime Jar rt.jar

b.At CLASSPATH add: \jdk\jre\lib\rt.jar

5.Signing frmwebutil.jar and Jacob.jar.
a.Before signing for jar file, you need to make sure your PATH environment variable is point to a jarsigner. To do this, follow the step below:
I.Right click on “My computer”, then choose “Properties”
II.Choose “Advanced” tab, and click on “Environment Variables” button
III.Find “PATH” variable and the follow line: \jdk\bin

b.Go to command prompt and locate yourself into \forms\webutil (Please close the previous command prompt windows and open a new one, because once you change the value at environment variable, it will not be reflected at DOS until you open a new command windows)

c.In command prompt use the sign_webutil.bat to sign the jar files. Specifiy the command as below:
I.sign_webutil \forms\java\frmwebutil.jar
II.sign_webutil \forms\java\jacob.jar

6.To enable upload and downloading file using WebUtil, please make sure the setting in \forms\server\webutil.cgf is following as below:

transfer.database.enabled=TRUE
transfer.appsrv.enabled=TRUE
transfer.appsrv.workAreaRoot=
transfer.appsrv.accessControl=FALSE

7.Setup frmweb.cfg file. For any application which need to use WebUtil just past all the setting in [webutil] section into the application section. For instance, I have a application called “TEST” make use of the function in WebUtil and I already create a section in frmweb.cfg as below:

[TEST]
FORM=D:\FORMS\MENU70_10G.FMX
USERID=TEST/123@ORCL

Copy the setting from [webutil] section and paste at [TEST] as below:

[TEST]
FORM=D:\FORMS\MENU70_10G.FMX
USERID=TEST/123@ORCL
WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
baseHTMLjinitiator=webutiljini.htm
baseHTMLjpi=webutiljpi.htm
archive_jini=frmall_jinit.jar
archive=frmall.jar
lookAndFeel=oracle

8.Grant WebUtil session at client side. Upon client first login to the application. JInitiator will ask client to grant permission for signed applet. Client must choose “Grant this session” or “Grant Always”. Without this step WebUtil will not work.

Using PRAGMA AUTONOMOUS_TRANSACTION

Autonomous transaction is available from Oracle 8i onward.
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. An autonomous transaction is an independent subprogram which called by the main transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independently from its parent transaction.

The autonomous transaction must be committed or roll backed before the autonomous transaction is ended and the parent transaction continues.

To define PL/SQL block as an autonomous transaction is quite easy. Just simply include the following statement in your declaration section:


PROCEDURE test
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Insert…
…
…
Commit;
END test;


Comparison of normal transaction with AUTONOMOUS TRANSACTION:

Normal Transaction example:


SQL:> declare
2 Procedure Insert_Test_Table_B
3 is
4 BEGIN
5 INSERT into Test_Table_B(x) values (1);
6 Commit;
7 END ;
8 BEGIN
9 INSERT INTO Test_Table_A(x) values (123);
10 Insert_Test_Table_B;
11 Rollback;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL :> Select * from Test_Table_A;
X
----------
123
SQL :> Select * from Test_Table_B;
X
----------
1
When the compiler execute until line 10, Insert_test_table_b subprogram was called. In Insert_test_table_b a COMMIT command was issued and all transactions were committed, therefore the ROLLBACK command at line 11 didn’t change any value.

AUTONOMOUS TRANSACTION:

SQL :> declare
2 Procedure Insert_Test_Table_B
3 is
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT into Test_Table_B(x) values (1);
7 Commit;
8 END ;
9 BEGIN
10 INSERT INTO Test_Table_A(x) values (123);
11 Insert_Test_Table_B;
12 Rollback;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL :> Select * from Test_Table_A;
no rows selected
SQL :> Select * from Test_Table_B;
X
----------
1
In AUTONOMOUS TRANSACTION case, the COMMIT command in Insert_Test_Table_B only commit the transaction that was done in Insert_Test_Table_B itself. Therefore when a ROLLBACK command was issued at line 12, insert operation at Test_Table_A was undo.