Webutil Configuration for 10G

-- the webutil configuration in 10 g is easier than previous version as you just make the following simple steps to start using webutil functionality

1) Download http://prdownloads.sourceforge.net/jacob-project/jacob_18.zip and extract to a temporary staging area. Do not attempt to use 1.7 or 1.9.

2) Copy or move jacob.jar and jacob.dll [JacobStage] is the folder where you extracted Jacob, and will end in ...\jacob_18 ( note that the jacob 18 is the recommended version by oracle) copy jacob.jar [OraHome]\forms\java\. copy jacob.dll [OraHome]\forms\webutil\.

3) Sign frmwebutil.jar and jacob.jar Open a DOS command prompt. Add [OraHome]\jdk\bin to the PATH: set PATH=[OraHome]\jdk\bin;%PATH% ---- of the operating system path OR BY THE REGULAR WAY (system properties from my computer---- advanced tab--environement variables) Sign the files, and check the output for success:
[OraHome]\forms\webutil\sign_webutil [OraHome]\forms\java\frmwebutil.jar [OraHome]\forms\webutil\sign_webutil [OraHome]\forms\java\jacob.jar

4) If you already have a schema in your RDBMS which contains the WebUtil stored code, you may skip this step. Otherwise, Create a schema to hold the WebUtil stored code, and privileges needed to connect and create a stored package. Schema name "WEBUTIL" is recommended for no reason other than consistency over the user base. Open [OraHome]\forms\create_webutil_db.sql in a text editor, and delete or comment out the EXIT statement, to be able to see whether the objects were created witout errors. Start SQL*Plus as SYSTEM, and issue: CREATE USER webutil IDENTIFIED BY [password] DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; / GRANT CONNECT, CREATE PROCEDURE, CREATE PUBLIC SYNONYM TO webutil; /
CONNECT webutil/[password]@[connectstring] @[OraHome]\forms\create_webutil_db.sql -- Inspect SQL*Plus output for errors, and then CREATE PUBLIC SYNONYM webutil_db FOR webutil.webutil_db; /
Reconnect as SYSTEM, and issue: grant execute on webutil_db to public;
/

5) Modify [OraHome]\forms\server\default.env, and append [oraclehome]\jdk\jre\lib\rt.jar to the CLASSPATH entry.

6) Start the OC4J instance

7) Start Forms Builder and connect to a schema in the RDBMS used in step (4). Open webutil.pll, do a "Compile ALL" (shift-Control-K), and generate to PLX (Control-T). It is important to generate the PLX, to avoid the FRM-40039 discussed in Note 303682.1 If the PLX is not generated, the Webutil.pll library would have to be attached with full path information to all forms wishing to use WebUtil. This is NOT recommended.

8- note that you must regenertate the webutil.pll before use , rather than you will encounter the error (ora 06508)you can do that by cmd as follow :frmcmp module=.....\webutil.pll userid=module_type=librarycompile_all=yes

9- also the rt.jar and frmwebuitl.jar must be included in the CLASSPATH enviroment variable

10- to use webutil in your form you must attach the webutill.pll and copy the object group"webutil" from the webutil.olb

11-in the first run of webutil in the client machine all libraries required will be downloaded

12- for advanced using of webutil ( like file transfer ) you must edit the "webutil.cfg" file to enable the file transfer that by default disabled

13- also in the "webutil.cfg" file you can edit loging option ( you should clean it up regualary)

14-also you must note that you shouldn't call any functionality of webutil before the interface is loaded ( suppose you want to get the client information like operating system or machine name when the form instance simply you can create timer with 1 second or 2 second and put your code in " when timer expired"

14- client_image.read_image_file and write_image_file may not read or write non ASCII file names corretly

General Contacts for all tables

-- when designing application you will face the case that the customers, suppliers or employees that have a set of similar attiributes but repeated at unknown frequency ,

example,
you may have more than phone number or more than email address of on entity , according to normalization rules and the business logic the following design is not valid:

customer -- customer_id,customer_name,phone1,phone2,phone3

--- instead of this you can locate the set of phone number in another entity, so you can enter unlimited number of phones of the customer

----------------------------------------------------------
----- ----------contact object group ----------------------
1- create the followinng tables


CREATE TABLE GN_CONTACTS
(
CONTACT_RECID NUMBER(16),
TABLE_RECID NUMBER(16) NOT NULL,
TABLE_NAME VARCHAR2(50 BYTE) NOT NULL,
CONTACT_WAY_RECID NUMBER(16) NOT NULL,
CONTACT_DESC VARCHAR2(50 BYTE),
CONTACT_NOTES VARCHAR2(200 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE GN_CONTACT_WAY
(
CONTACT_WAY_RECID NUMBER,
CONTACT_WAY_DESC VARCHAR2(50 BYTE) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX PK_CONTACT_RECID ON GN_CONTACTS
(CONTACT_RECID)
LOGGING
NOPARALLEL;

CREATE UNIQUE INDEX PK_CONTACT_WAY ON GN_CONTACT_WAY
(CONTACT_WAY_RECID)
LOGGING
NOPARALLEL;

CREATE OR REPLACE TRIGGER TRG_CONTACTS
BEFORE INSERT
ON GN_CONTACTS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RECID NUMBER;
BEGIN
SELECT SEQ_CONTACTS.NEXTVAL INTO V_RECID FROM DUAL ;
:NEW.CONTACT_RECID := V_RECID;
END TRG_CONTACTS ;
/
SHOW ERRORS;


CREATE OR REPLACE TRIGGER TRG_CONTACT_WAY_RECID
BEFORE INSERT
ON GN_CONTACT_WAY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RECID NUMBER;
BEGIN
SELECT SEQ_contact_way.NEXTVAL INTO V_RECID FROM DUAL ;
:NEW.contact_way_recid := V_RECID;
END trg_contact_way_recid;
/
SHOW ERRORS;


ALTER TABLE GN_CONTACTS ADD (
CONSTRAINT PK_CONTACT_RECID PRIMARY KEY (CONTACT_RECID));

ALTER TABLE GN_CONTACT_WAY ADD (
CONSTRAINT PK_CONTACT_WAY PRIMARY KEY (CONTACT_WAY_RECID));

2- after that you can create an object group ( to use globally ) the object group should contain the following :

-canvas

-window

-backage

-two block (database block gn_contacts and control block)

-visual attribute [optional]

the contacts_pkg :

PACKAGE contact_pkg IS
procedure go_contact(p_view_only number default 0);

function get_block_table_name(V_BLOCK_NAME VARCHAR2)return varchar2;

PROCEDURE SUPPORT_CONTACTS(P_BLOCK_NAME VARCHAR2,P_PRIMARY_KEY_ITEM VARCHAR2);

PROCEDURE INSERT_CONTACTS;

END;

PACKAGE BODY contact_pkg IS

----------------------------------------------------------------------------------------------
PROCEDURE INSERT_CONTACTS IS ---- TO BE CALLED IN THE PRE INSERT OF CONTACT TABLE
v_pk_col varchar2(50);

v_value varchar2(50);

BEGIN
:TABLE_NAME:=NAME_IN('CONTACT_TABLE_NAME');
:table_recid:=NAME_IN(NAME_IN('CONTACT_PRIMARY_KEY'));
END ;

----------------------------------------------------------------------------------------------
PROCEDURE SUPPORT_CONTACTS(P_BLOCK_NAME VARCHAR2,P_PRIMARY_KEY_ITEM VARCHAR2) IS --- TO BE CALLED AFTER NEW FORM INSTANCE
BEGIN
set_item_property('GN_CONTACTS.CONTACT_WAY_RECID',required,property_false);

P_POPULATE_LIST('GN_CONTACTs.CONTACT_WAY_RECID','SELECT CONTACT_WAY_DESC ,TO_CHAR(CONTACT_WAY_RECID) FROM GN_CONTACT_WAY');

set_item_property('GN_CONTACTs.CONTACT_WAY_RECID',required,property_TRUE);
:CONTACT_BLOCK_NAME:=P_BLOCK_NAME;

:CONTACT_TABLE_NAME:=GET_BLOCK_TABLE_NAME(P_BLOCK_NAME);

:CONTACT_PRIMARY_KEY:=P_PRIMARY_KEY_ITEM;

END ;
---------------------------------------------------------------------------------------------function get_block_table_name(v_block_name varchar2)return varchar2 is
v_return varchar2(100);

begin

v_return:=get_block_property(v_block_name,query_data_source_name);
return(v_return);end ;
-----------------------------------------------------------------------------------------------PROCEDURE go_contact(p_view_only number default 0) IS -- IN THE CONTACT BUTTON
BEGIN
if p_view_only=1 then

set_block_property('gn_contacts',insert_allowed,property_false);

set_block_property('gn_contacts',update_allowed,property_false);

set_block_property('gn_contacts',delete_allowed,property_false); end if ;
:PK_VALUE:=TO_NUMBER(NAME_IN(NAME_IN('CONTACT_PRIMARY_KEY')));
go_block('GN_contacts');

execute_query;
END;

-------------------------------------------------------------------------------------------------- END;

3- in the when new form instance intialize the block name and its primary key column by invoking the following procedure
CONTACTS.SUPPORT_CONTACTS(P_BLOCK_NAME VARCHAR2,P_PRIMARY_KEY_ITEM VARCHAR2);

4- create a boutton that call the contacts

contact_pkg.go_contacts(p_view_only default 0);

5- note that the data in the contact_way table is system defined (email,fax,phone,website)
also you can add to it if required
6-also you can validate the data that entered in contacts according to its type

Include Graph Chart in your web form

1-copy the jar file in the forms/java directory

2-add the jar name in the archivjinit in the formsweb.cfg

3-add the full path included the jar name in the forms_builder_classpath in the registery (require to restart the forms builder)

4-add the full path included the jar name in the forms_classpath in the default.env

5-create your block

6- create a bean area in your form and set its implementation class to (oracle.forms.demos.bigraph.FormsGraph) -- case sensitive---

7-create your procedure that will pass the data to your graph bean and draw the graph

--- code---- the firs way is to loop your block data
PROCEDURE create_graph_block IS

vItem VARCHAR2(200) := 'emp.empno';

v_graphic_item ITEM := FIND_ITEM ('BEAN_BLOCK.GRAPHIC_BEAN');

vData VARCHAR2(4000);

BEGIN

-- Initialize Graph

SET_CUSTOM_PROPERTY (v_graphic_item,1,'CLEAR_GRAPH','');
GO_BLOCK ('emp');

FIRST_RECORD;

LOOP

vData := :emp.ename ',' :emp.ename ','

:emp.sal; SET_CUSTOM_PROPERTY(v_graphic_item,1,'ADD_ROWDATA',vData); IF :SYSTEM.Last_Record = 'TRUE' THEN EXIT;

END IF; NEXT_RECORD;

END LOOP;

-- FIRST_RECORD;
-- Show Graph SET_CUSTOM_PROPERTY(v_graphic_item, 1,'ADD_DATA_TO_GRAPH',''); SET_CUSTOM_PROPERTY(v_graphic_item, 1,'GRAPHTYPE','VERTICAL_BAR');
-- GO_ITEM(vItem);

end ;

--------------------------------------------------The second way is to pass the data by cursor

PROCEDURE create_gr_cursor IS

vGrafic ITEM := FIND_ITEM ('bean_block.GRAPHIC_BEAN');

vData VARCHAR2(200);
BEGIN

-- Initialize Graph SET_CUSTOM_PROPERTY(vGrafic, 1, 'CLEAR_GRAPH', '');
FOR c_cur IN (SELECT dname, SUM(sal) sum_sal FROM dept, emp

where dept.deptno=emp.deptno GROUP BY dname)

LOOP

vData := c_cur.dname ',' 'Total Sales 2000 - 2005' ',' c_cur.sum_sal; SET_CUSTOM_PROPERTY(vGrafic, 1, 'ADD_ROWDATA', vData);

END LOOP;
-- Show Graph SET_CUSTOM_PROPERTY (vGrafic, 1, 'GRAPHTYPE', 'PIE_GRAPH'); SET_CUSTOM_PROPERTY (vGrafic, 1, 'ADD_DATA_TO_GRAPH', '');
END;

------------------------------------------------------------------------------

Dynamic triggers and sequences creation

suppose that your tables must have pre-insert trigger that get the id of the table from sequence,
so you can save your time by creating the trigger and the sequence dynamically for new tables

1- create the following pkg

CREATE OR REPLACE PACKAGE DYNAMIC_TRIGGER_CREATION_PKG AS
--/******************************************************************************---- 1.0 26/03/2007 created by haythem mousa 1. Created this package.--******************************************************************************/
PROCEDURE P_CREATE_SEQ (V_TABLE_NAME VARCHAR2 ) ;
Function is_table_sequenced(p_table_name varchar2) return varchar2;
Procedure p_apply_new_tables;
END OBJECT_CR_PKG;/
CREATE OR REPLACE PACKAGE BODY DYNAMIC_TRIGGER_CREATION_PKG AS/******************************************************************************
1.0 26/03/2007 1. Created this package body.******************************************************************************/
Procedure p_apply_new_tables IS
cursor c_cur is select object_name from user_objectswhere object_type='TABLE'
and object_cr_pkg.is_table_sequenced(object_name)='NO';

begin
FOR I IN C_CUR LOOP
V_TABLE_NAME :=I.OBJECT_NAME ;
DYNAMIC_TRIGGER_CREATION_PKG .P_CREATE_SEQ(V_TABLE_NAME);
END LOOP;
end;-------------------------------------------------------------------------------------------------------------------------------
Function is_table_sequenced(p_table_name varchar2) return varchar2 is
v_return varchar2(20);
v_seq_name varchar2(50);
cursor c_cur (v_table_name varchar2) is select object_name from user_objects
where object_type='SEQUENCE'
and object_name='SEQ_'V_TABLE_NAME;
begin
open c_cur(p_table_name) ;
fetch c_cur into v_seq_name ;
if c_cur %notfound then
v_return:='NO' ;
elsev_return:='YES' ;
end if ;
close c_cur ;
return(v_Return);

end ;
-----------------------------------------------------------------------------------------------------------------------
PROCEDURE P_CREATE_SEQ (V_TABLE_NAME VARCHAR2) IS
V_SEQ VARCHAR2(30);
V_PRIMARY_KEY VARCHAR2(50) :=SUBSTR(V_TABLE_NAME,4)'_RECID' ;
V_TRG VARCHAR2(50);
V_String VARCHAR2(4000) ;
BEGIN V_SEQ:='SEQ_' V_TABLE_NAME;
V_TRG:='TRG_'V_TABLE_NAME;

EXECUTE IMMEDIATE ('CREATE SEQUENCE ' V_SEQ ' INCREMENT BY 1
START WITH 1 NOMAXVALUENOCYCLENOCACHEORDER') ;

SELECT'CREATE OR REPLACE TRIGGER ' V_TRG' BEFORE INSERT ON ' V_TABLE_NAME' REFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWDECLARE V_RECID NUMBER ;BEGINSELECT ' V_SEQ'.NEXTVAL INTO V_RECID FROM DUAL ; :NEW.' V_PRIMARY_KEY ' := V_RECID ;END TRG_'V_TABLE_NAME ';' INTO V_String FROM DUAL;

EXECUTE IMMEDIATE(v_string);
END P_CREATE_SEQ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
END OBJECT_CR_PKG;/
---############################################################

2-run the following procedure after any new table creation
DYNAMIC_TRIGGER_CREATION_PKG .p_apply_new_table;

3- you can customize the code according to your naming convention of the sequence or triggers

How to get Screen resolution

There is a way to read the Screen resolution. It could be useful to know ,for example, whether the screen resolution now is 800 x 600 or 1024 x 768.It may helps you to determine the size of your main form,for example.

Please, Follow the following Steps:
1- Download the jar file "myutil.jar"
2- copy the jar file to /forms/java
3- go to /forms/server
4- edit the formsweb.cfg and add the jar name to the attribute:Archive_Jini
5-edit the file default.env file and add the full jar path and name to the CLASS_PATH attribute
6- open the regedit and add the full jar path and name to the FORMS_BUILDER_CLASSPATH
7- open the form Builder and choose progarm>Import Java Class
8- import the following class: custompkg.ReadScreenSize
9- A pL/SQL package is now created with the same name of the class
10- the following procedure display message for the reolution,Just an example.

Declare
screenObj ora_java.Jobject;
width number;
height number;
Begin
screenObj := readscreensize.new;
width := readscreensize.getScreenWidth(screenObj);
height := readscreensize.getScreenHeight(screenObj);
message(width'x'height);
end;

Applying Barcode in oracle forms and reports

Forms configuration

1- copy the jar file in the forms/java directory

2-add the jar file name to archivjinit

3-add the path jar to the registery of the windows ( forms_builder_classpath)

4- add the full path of the jar in forms_classpath in the default.env file

5-create file in the c:\temp 'image.jpg'

6-import the following two java class into your form ( oracle.apps.barcode.util.barcodeconstant --------------- barcodemaker)

7-create a procedure or function that generate the barcode

function xxx return Char is bcobj ora_java.jobject := barcodemaker.new();

myfilename varchar2(20); result varchar2(20);

barcodeData VarChar2(50) := :serialnumbertobeconverted;

begin
barcodemaker.setBarWidthInch(bcobj, 0.005);

barcodemaker.setBaseCodeData(bcobj,barcodeData);

barcodemaker.setBarCodeType(bcobj,BarCodeConstants.BAR_CODE_128);

myfilename := barcodeData;

barcodemaker.setFullPath(bcobj, myFileName);

barcodemaker.renderBarCode(bcobj);

return(myfilename);

end;

The jar file name (oraclebarcode.jar)

-------------------------------------------------------------------------

Reports configuration

1- set the report_temp registery key as c:\temp
2-edit the windows environment variables -- add to the user variables ( REPORTS_TEMP) MUST BE CAPITAL to allow the report server to access teh temp direcotry
3- copy the jar file to (home\reports\jilib)
4- edit your reports server con file add line to the tags and classpath=full path in the jilib --- the full path must be in double qoutation
5-restart the report server
6-include the path of the jar in the report class path registery key to allow to the report builder to import the java classes
7- create new report and import the mentioned above (infroms confgiuration) to your report , then create a formula column and set its pl/sql code as follow:
function CF_1Formula return char is
bcobj ora_java.jobject := barcodemaker.new();

myfilename varchar2(20); result varchar2(20);

barcodeData VarChar2(50) := :empno;

begin

myFileName :=srw.create_temporary_filename;

barcodemaker.setBarWidthInch(bcobj, 0.005);

barcodemaker.setBaseCodeData(bcobj,barcodeData);

barcodemaker.setBarCodeType(bcobj,BarCodeConstants.BAR_CODE_128);

myfilename := barcodeData;

barcodemaker.setFullPath(bcobj, myFileName);

barcodemaker.renderBarCode(bcobj);

return(myfilename);

end;

8- after that create a field and set its source to your formula and after that set " read from file" to yes and "file format" to image
9- run your report

---###############################################################