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.

10g OCA Exam

Just passed my 10g OCA exam(IZ0-042) yesterday. Hurray!!! I'd been delaying the exam for ages, at last i made up myself to go ahead and finish it once for all..hahah.. When you are working you become a bit lazy to go through all those revisions to make sure you are 100% prepared for the exam. Ok i know, i admit it just an lame excuse for being plain lazy.

Tips for passing the exam:
1. Revision on Oracle Administration document
2. Run some tests for prove of concept
3. Read the sample questions.
(This one really helps a lot. Test King, Brains Dump, Cert Killer and etc are providing useful test questions. Search on web, you may even find a bunch for free.)

The 10g OCA exam wasn't so hard overall. If you are familiar with working on Oracle databases, in fact you will find it very easy.

Create Calendar in Oracle Form

Before you start, make sure you have the "stndrd.olb" and"calendar.pll" file.

1.Open the file stndrd.old from Form Developer. You will notice the item "standards" is added to the object navigator/object libraries section.

2.Next open or create a new form.

3.Go to "Object Libraries" under "STANDARDS" library, expand the "library tabs", and double click on "COMPONENTS" object.

4.Drag the component "calendar" to the new form. A message box wills pop-up to ask whether you want to "sub-class" or "copy". Select "copy" or the new form with the calendar will not work on PC's that does not have the standards item added. Copying will add all required bits you need for the calendar.

5.Next open the "calendar.pll" file. This will add a "calendar" item to the "pl/sql libraries" section on the "object navigator". Select the "program unit" section and drag it up to the "programs unit" section of your form. This will add 2 items to the "programs unit" section (date_lov - package spec & date lov -package body).

6.After that you need to add a program unit procedure named DATE_CHOOSEN in the new form or you will get error “date_choosen not declared”. Copy this code into the procedure:

PROCEDURE date_choosen IS
BEGIN
copy(to_char(date_lov.current_lov_date,'dd-mon-yyyy'), date_lov.date_lov_return_item);
go_item(date_lov.date_lov_return_item);
if date_lov.lov_auto_skip = TRUE then
next_item;
end if;
END;


7.Next on the new form you can add a button to call the "calendar" with button trigger. Use the code below to call the "calendar".

DATE_LOV.GET_DATE
(NVL(:BLOCK.ITEM, SYSDATE),
'BLOCK.ITEM',
200, 100,
'SELECT DATE',
'OK','CANCEL',
TRUE, FALSE, FALSE);

The “block.item” is where you want the date to be inserted into via the calendar user interaction.

Parameters for the Date_LOV.Get_Date are as follows:

date_lov.get_date (initial date,
return block.item,
window x position,
window y position,
window title,
ok button label,
cancel button label,
highlight weekend days,
autoconfirm selection,
autoskip after selection);

You may take a look at the 'Calendar' code that is available as a download from the Oracle Forms website http://www.oracle.com/technology/sample_code/products/forms/6idemos.html

References: http://www.orafaq.com/forum/t/30334/0/

How to calculate table size

Sometime we need to estimate the size of the table for storage planing. But looking at the database, it doesn't shows us directly how many space is actually occupied by a particular table. A bit of peek on the USER_SEGMENTS and USER_TABLES views will do the trick.

To query the actual space allocated for the table:

select segment_name, bytes/1024/1024,blocks MB
from user_segments
where segment_name = 'T1'

or

select (BLOCKS*[replace your data block size here])
from user_tables
where table_name = 'T1';


To query the actual space occupied by the data:

select table_name,(num_rows*avg_row_len)/(1024*1024) MB
from user_tables
where table_name='T1';

or

select ((BLOCKS*[replace your data block size here])-(BLOCKS*AVG_SPACE))/1024/1024 MB
from user_tables
where table_name='T1'

Somehow the second method will show bigger amount of spaces being occupied.


Note: we need to analyzed the table before we can get all these statistic.

Oracle Forms SQL Injection

As Oracle Form user, we always like to query record using the "query mode" button. A wonder button with a picture of database and question mark. But often, most of the people don't know that there is an ancient feature behind called "Query/Where" that allows users to modify existing SQL statements. This is an feature which is built with wonderful purpose but dangerous due to the fact that all forms user can use it to execute arbitrary SQL statements.This function available for Oracle Forms versions 3.0 up to 10g.

Short demonstration of Oracle Forms SQL Injection:
1. Start a Forms module and switch to the query mode
2. Enter a colon ( : ) or ampersand ( & )
3. An empty Query/Where windows will pops up
4. Inject your SQL statement

Example:
My data block is querying data from SUBJECT table.
The normal data block query will be "select subject_code, subject_description from subject".
if i put the data column into query mode then place ":" or "&" and execute the query. A blank box will comes out. Then i enter "subject_code = '%') union select table_name, null from user_tables--". Once i click on "OK", all table name is shown on the subject code column.

Suggestion of Solution:
1. Disable "query allow" for all data block columns which appear on the form
2. Set environment variable FORMSxx_RESTRICT_ENTER_QUERY=true (FORMS60_RESTRICT_ENTER_QUERY for Forms 6.x, FORMS90_RESTRICT_ENTER_QUERY for Forms 9.x/10g)
3. Use PRE_QUERY/ON-ERROR-trigger to detect sql injection

Flashback Query

Anyone ever encounter some “oh my god” or “I’m in deep shit” situations when you did a batch update to the data?

Instead of recover the data from backup. We have something call flash back query from Oracle 9i onward. With this feature you are able to query out the version of data “as of” specific timestamp.

Command Example:

1. SQL> SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON';

2. SQL> INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON');


P/S: This feature is of available in Oracle 9i onward. You must make sure your UNDO_RETENTION value is big enough, else you will get "ora-01555 snapshot too old" error

Related topic: Oracle Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.

Useful sql to clean duplicated records

Delete duplicated data from table can be a headache. In the example below we are using the unique ROWID to distinguish the duplicated rows. (Each record in database contains a uniques system generated number)

DELETE FROM TEST1 A
WHERE ROWID >
(SELECT MIN(ROWID)
FROM TEST1 B
WHERE A.COLUMN1 = B.COLUMN1
[AND A.COLUMN2 = B.COLUMN2
AND A.COLUMNn = B.COLUMNn])

Alternatively we can use a temporary table do the trick:

CREATE TABLE TEMP_TEST2 AS
SELECT DISTINCT *
FROM TEST1
WHERE [PUT IN UR CONDITIONS];

DELETE TABLE TEST1;

INSERT INTO TEST1 SELECT * FROM TEMP_TEST2;

Hello World!!

Hi everyone, welcome to my blog. I will be basically sharing my 2 years plus experience as an Oracle Form/Report Developer and Oracle DBA in this blog. I will not rate myself as an Oracle expert but instead i to share some of the tips and tricks which i think it is quite useful for Oracle Developer and DBA beginner. Feel free to drop me some feedback if you have any opinion or suggestion on my articles. Thanks.