How can i took xml field data form clob clumn directly ?

How can i took xml field data form clob clumn directly ?
SQL>
SQL> CREATE TABLE XML_LOAD_IN
2 (XML_CFILE CLOB
3 );

Table created.

SQL>
SQL> INSERT INTO XML_LOAD_IN ( XML_CFILE)
2 VALUES (
3 '
4 1
5 2
6 1500
7 2
8 Bank Asia
9 20090714
10
11 ');

1 row created.

SQL>
SQL> CREATE TABLE CCR_IN
2 (
3 “BundleCount” NUMBER(6) NOT NULL,
4 “ItemWithinCashLetterCount” NUMBER(8) NOT NULL,
5 “CashLetterTotalAmount” NUMBER(14) NOT NULL,
6 “ImagesWithinCashLetterCount” NUMBER(9),
7 “ECEInstitutionName” VARCHAR2(18 BYTE),
8 “SettlementDate” VARCHAR2(8 BYTE)
9 )
10 /

Table created.

SQL>
SQL> CREATE OR REPLACE procedure Dpr_Insert_From_Xml_CCR
2 is
3 l_bfile BFILE;
4 l_clob CLOB;
5 l_parser dbms_xmlparser.Parser;
6 l_doc dbms_xmldom.DOMDocument;
7 l_nl dbms_xmldom.DOMNodeList;
8 l_n dbms_xmldom.DOMNode;
9
10 TYPE tab_type IS TABLE OF CCR_IN%ROWTYPE;
11 t_tab tab_type := tab_type();
12
13 BEGIN
14
15 Begin
16 select xml_cfile
17 into l_clob
18 from xml_load_in;
19 Exception
20 When no_data_found then
21 raise_application_error(-2001,'Inward XML File Not Found.');
22 When others then raise;
23 End;
24
25 l_parser := dbms_xmlparser.newParser;
26 dbms_xmlparser.parseClob(l_parser, l_clob);
27 l_doc := dbms_xmlparser.getDocument(l_parser);
28 dbms_xmlparser.freeParser(l_parser);
29 l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/CCR');
30
31 –FOR ECR
32 FOR CUR_CCR IN 0 .. dbms_xmldom.getLength(l_nl) – 1 LOOP
33 l_n := dbms_xmldom.item(l_nl, CUR_CCR);
34 t_tab.extend;
35 — Use XPATH syntax to assign values to he elements of the collection.
36 dbms_xslprocessor.valueOf(l_n,'BundleCount/text()' ,t_tab (t_tab.last).”BundleCount” );
37 dbms_xslprocessor.valueOf(l_n,'ItemWithinCashLetterCount/text()' ,t_tab(t_tab.last).”ItemWithinCashLetterCount” );
38 dbms_xslprocessor.valueOf(l_n,'CashLetterTotalAmount/text()' ,t_tab(t_tab.last).”CashLetterTotalAmount” );
39 dbms_xslprocessor.valueOf(l_n,'ImagesWithinCashLetterCount/text()' ,t_tab(t_tab.last).”ImagesWithinCashLetterCount” );
40 dbms_xslprocessor.valueOf(l_n,'ECEInstitutionName/text()' ,t_tab(t_tab.last).”ECEInstitutionName” );
41 dbms_xslprocessor.valueOf(l_n,'SettlementDate/text()' ,t_tab(t_tab.last).”SettlementDate” );
42 END LOOP;
43
44
45 FOR CUR_CCR IN t_tab.first .. t_tab.last LOOP
46
47 INSERT INTO CCR_IN
48 (”BundleCount” ,
49 “ItemWithinCashLetterCount” ,
50 “CashLetterTotalAmount” ,
51 “ImagesWithinCashLetterCount” ,
52 “ECEInstitutionName” ,
53 “SettlementDate”
54 )
55 VALUES
56 (t_tab(CUR_CCR).”BundleCount” ,
57 t_tab(CUR_CCR).”ItemWithinCashLetterCount” ,
58 t_tab(CUR_CCR).”CashLetterTotalAmount” ,
59 t_tab(CUR_CCR).”ImagesWithinCashLetterCount” ,
60 t_tab(CUR_CCR).”ECEInstitutionName” ,
61 t_tab(CUR_CCR).”SettlementDate”
62 );
63
64 END LOOP;
65
66 COMMIT;
67
68 dbms_xmldom.freeDocument(l_doc);
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 dbms_xmlparser.freeParser(l_parser);
73 dbms_xmldom.freeDocument(l_doc);
74 END;
75 /

Procedure created.

SQL>
SQL> select * From xml_load_in;

XML_CFILE
——————————————————————————–

1
2
SQL>
SQL> exec Dpr_Insert_From_Xml_CCR;

PL/SQL procedure successfully completed.

SQL>
SQL> select * from ccr_in;

BundleCount ItemWithinCashLetterCount CashLetterTotalAmount
———– ————————- ———————
ImagesWithinCashLetterCount ECEInstitutionName Settleme
————————— —————— ——–
1 2 1500
2 Bank Asia 2009071

USE OF CASE AND DECODE

CASE AND DECODE : Two powerful constructs of SQL
________________________________________
CASE and DECODE are the two widely used constructs in the SQL . And both have the functionality of an IF-THEN-ELSE statement to return some specified value meeting some criteria.Even though they are used interchangeably there are some differences between them.

This article tries to show list the advantage of CASE over DECODE and also explain how to convert DECODE to CASE and vice versa.

CASE was introduced in Oracle 8.1.6 as a replacement for the DECODE . Anyway it is much better option than DECODE as it is ,

1. More Flexible than DECODE

2. More easier to read

3. ANSI Compatible

4. compatible in PL/SQL Context

SIMPLE CASE

Generally CASE has two syntaxes as below

a. Expression Syntax
Code:
CASE [ expression ]
WHEN Value_1 THEN result_1
WHEN Value_2 THEN result_2
...
WHEN Value_n THEN result_n
[ELSE else_result]
END
Here CASE checks the value of Expression and returns the result each time for each record as specified. Here is one such example to list the new salaries for all employees
Code:
SQL> SELECT EMPNO,JOB , SAL ,
2 CASE JOB WHEN 'ANALYST' THEN SAL*1.2
3 WHEN 'MANAGER' THEN SAL*1.4
4 ELSE SAL END NEWSAL
5 FROM EMP;

EMPNO JOB SAL NEWSAL
---------- --------- ---------- ----------
7369 CLERK 800 800
7499 SALESMAN 1600 1600
7521 SALESMAN 1250 1250
7566 MANAGER 2975 4165
7654 SALESMAN 1250 1250
7698 MANAGER 2850 3990
7782 MANAGER 2450 3430
7788 ANALYST 3000 3600
7839 PRESIDENT 5000 5000
7844 SALESMAN 1500 1500
7876 CLERK 1100 1100
7900 CLERK 950 950
7902 ANALYST 3000 3600
7934 CLERK 1300 1300

14 rows selected.

SQL>
The Equivalent DECODE syntax will be
Code:
SQL> SELECT EMPNO,JOB , SAL ,
2 DECODE (JOB,'ANALYST', SAL*1.2 ,
3 'MANAGER', SAL*1.4,
4 SAL ) NEWSAL
5 FROM EMP;

EMPNO JOB SAL NEWSAL
---------- --------- ---------- ----------
7369 CLERK 800 800
7499 SALESMAN 1600 1600
7521 SALESMAN 1250 1250
7566 MANAGER 2975 4165
7654 SALESMAN 1250 1250
7698 MANAGER 2850 3990
7782 MANAGER 2450 3430
7788 ANALYST 3000 3600
7839 PRESIDENT 5000 5000
7844 SALESMAN 1500 1500
7876 CLERK 1100 1100
7900 CLERK 950 950
7902 ANALYST 3000 3600
7934 CLERK 1300 1300

14 rows selected.

SQL>
b. Conditional syntax
Code:
CASE
WHEN Condition_1 THEN result_1
WHEN Condition_2 THEN result_2
...
WHEN Condition_n THEN result_n
[ELSE else_result]
END
Here CASE tries to return the values on meeting some conditions rather than checking for the expressions . Here is such an example with the same functionality as above .
Code:
SQL> SELECT EMPNO, JOB , SAL ,
2 CASE WHEN JOB='ANALYST' THEN SAL*1.2
3 WHEN JOB='MANAGER' THEN SAL*1.4
4 ELSE SAL END NEWSAL
5 FROM EMP;

EMPNO JOB SAL NEWSAL
---------- --------- ---------- ----------
7369 CLERK 800 800
7499 SALESMAN 1600 1600
7521 SALESMAN 1250 1250
7566 MANAGER 2975 4165
7654 SALESMAN 1250 1250
7698 MANAGER 2850 3990
7782 MANAGER 2450 3430
7788 ANALYST 3000 3600
7839 PRESIDENT 5000 5000
7844 SALESMAN 1500 1500
7876 CLERK 1100 1100
7900 CLERK 950 950
7902 ANALYST 3000 3600
7934 CLERK 1300 1300

14 rows selected.

SQL>
Even DECODE can be a replacement for such condition scenario ,but it cannot have as flexibility as CASE can have . Here is such scenario to display the hiked the salary for only for those employees who were hired before 01-JAN-1982.
Code:
SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
2 CASE WHEN HIREDATE 3 THEN SAL*1.6
4 ELSE SAL
5 END NEWSAL
6 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 1280
7499 20-FEB-81 SALESMAN 1600 2560
7521 22-FEB-81 SALESMAN 1250 2000
7566 02-APR-81 MANAGER 2975 4760
7654 28-SEP-81 SALESMAN 1250 2000
7698 01-MAY-81 MANAGER 2850 4560
7782 09-JUN-81 MANAGER 2450 3920
7788 09-DEC-82 ANALYST 3000 3000
7839 17-NOV-81 PRESIDENT 5000 8000
7844 08-SEP-81 SALESMAN 1500 2400
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 1520
7902 03-DEC-81 ANALYST 3000 4800
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
The Equivalent code for DECODE will be ,
Code:
SQL> SELECT EMPNO ,HIREDATE ,JOB , SAL ,
2 DECODE (LEAST ( HIREDATE , TO_DATE('01/01/1982','DD/MM/YYYY')),
3 HIREDATE ,SAL*1.6,
4 SAL) NEWSAL
5 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 1280
7499 20-FEB-81 SALESMAN 1600 2560
7521 22-FEB-81 SALESMAN 1250 2000
7566 02-APR-81 MANAGER 2975 4760
7654 28-SEP-81 SALESMAN 1250 2000
7698 01-MAY-81 MANAGER 2850 4560
7782 09-JUN-81 MANAGER 2450 3920
7788 09-DEC-82 ANALYST 3000 3000
7839 17-NOV-81 PRESIDENT 5000 8000
7844 08-SEP-81 SALESMAN 1500 2400
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 1520
7902 03-DEC-81 ANALYST 3000 4800
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
But here CASE query is more flexible , readable and elegant.

NESTED CASE

What we discussed till now is about the simple CASE . But it can be nested also. Lets make it clear with some examples . Here is such an example to hike the salaries only for Analysts and Managers joined before 01-JAN-1982 and Analysts joined on or after the same date.
Code:
SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
2 CASE WHEN HIREDATE < TO_DATE('01/01/1982','DD/MM/YYYY')
3 THEN CASE WHEN JOB='ANALYST' THEN SAL*1.2
4 WHEN JOB='MANAGER' THEN SAL*1.4
5 ELSE SAL END
6 ELSE CASE WHEN JOB='ANALYST' THEN SAL*1.6
7 ELSE SAL END END NEWSAL
8 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 800
7499 20-FEB-81 SALESMAN 1600 1600
7521 22-FEB-81 SALESMAN 1250 1250
7566 02-APR-81 MANAGER 2975 4165
7654 28-SEP-81 SALESMAN 1250 1250
7698 01-MAY-81 MANAGER 2850 3990
7782 09-JUN-81 MANAGER 2450 3430
7788 09-DEC-82 ANALYST 3000 4800
7839 17-NOV-81 PRESIDENT 5000 5000
7844 08-SEP-81 SALESMAN 1500 1500
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 950
7902 03-DEC-81 ANALYST 3000 3600
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
It equivalent decode statement will be
Code:
SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
2 DECODE (LEAST ( HIREDATE , TO_DATE('01/01/1982','DD/MM/YYYY')),
3 HIREDATE ,
4 DECODE (JOB,'ANALYST', SAL*1.2 ,
5 'MANAGER', SAL*1.4,
6 SAL ),
7 DECODE (JOB, 'ANALYST', SAL*1.6,
8 SAL )) NEWSAL
9 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 800
7499 20-FEB-81 SALESMAN 1600 1600
7521 22-FEB-81 SALESMAN 1250 1250
7566 02-APR-81 MANAGER 2975 4165
7654 28-SEP-81 SALESMAN 1250 1250
7698 01-MAY-81 MANAGER 2850 3990
7782 09-JUN-81 MANAGER 2450 3430
7788 09-DEC-82 ANALYST 3000 4800
7839 17-NOV-81 PRESIDENT 5000 5000
7844 08-SEP-81 SALESMAN 1500 1500
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 950
7902 03-DEC-81 ANALYST 3000 3600
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
One major advantage of CASE over DECODE

CASE works good in bot SQL and PL/SQL Context while DECODE is supported only in SQL Context. Below example explains this scenario.
Code:
SQL> CREATE OR REPLACE FUNCTION GIMME_VAL ( N NUMBER)
2 RETURN NUMBER AS
3 BEGIN
4 RETURN N*N ;
5 END;
6 /

Function created.

SQL> variable x number
SQL> begin
2 :x :=10;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select Gimme_val(decode(:x,0,1,:x)) from dual;

GIMME_VAL(DECODE(:X,0,1,:X))
----------------------------
100

SQL> select Gimme_val(CASE :x WHEN 0 THEN 1 ELSE :x End ) from dual;

GIMME_VAL(CASE:XWHEN0THEN1ELSE:XEND)
------------------------------------
100

SQL>
Both are working well in SQL context . And to test it in PL/SQL , lets create two procedures ; one with CASE and another with DECODE.
Code:
SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_1 AS
2 X NUMBER :=10 ;
3 Z NUMBER ;
4 BEGIN
5 Z := GIMME_VAL(CASE X WHEN 0 THEN 1 ELSE X END );
6 DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
7 END;
8 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_2 AS
2 X NUMBER :=10;
3 Z NUMBER;
4 BEGIN
5 Z := GIMME_VAL(DECODE(X,0,1,X));
6 DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
7 END;
8 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE GETME_VAL_2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: Statement ignored
5/16 PLS-00204: function or pseudo-column 'DECODE' may be used inside
a SQL statement only

SQL>
We got one compilation error while creating a procedure to use the DECODE inside the PL/SQL context . ie "function or pseudo-column 'DECODE' may be used inside a SQL statement only"

Anyway there is alternative to overcome this error. We need to incorporate the DECODE in an Select statement as below.
Code:
SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_2 AS
2 X NUMBER :=10;
3 Z NUMBER;
4 BEGIN
5 SELECT GIMME_VAL(DECODE(X,0,1,X))
6 INTO Z
7 FROM DUAL;
8 DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
9 END;
10 /

Procedure created.

SQL>
And now both the procedure will work fine .
Code:
SQL> Exec getme_val_1 ;
GIMME_VAL = 100

PL/SQL procedure successfully completed.

SQL> Exec getme_val_2 ;
GIMME_VAL = 100

PL/SQL procedure successfully completed.

SQL>
An interesting scenario

Its really interesting !!! Understand the scenario without any explanation .
Code:
SQL> SELECT DECODE (NULL,NULL,'NULL','NOTNULL') NICE
2 FROM DUAL;

NICE
----
NULL

SQL> SELECT CASE NULL
2 WHEN NULL THEN 'NULL'
3 ELSE 'NOTNULL' END NICE
4 FROM DUAL;

NICE
-------
NOTNULL

SQL> SELECT CASE
2 WHEN NULL IS NULL THEN 'NULL'
3 ELSE 'NOTNULL' END NICE
4 FROM DUAL;

NICE
-------
NULL

SQL>
Conclusion

Though both CASE and DECODE are used interchangeably , CASE is definitely proved to be better and elegant option over DECODE as it is more flexible , readable and ANSI Compatible . And moreover CASE is perffered in PL/SQL statements as DECODE is only supported in the SQL statements.

Solutions for Southeast Asia: Identifying new high-performing keywords for paid search using Google Analytics

Solutions for Southeast Asia: Identifying new high-performing keywords for paid search using Google Analytics

Tuning Script in sqlplus

set echo off
rem
rem Script: tuning.sql
rem
rem Purpose: Check various statistics for the currently-running database
rem to see if there are any database parameters or other modifications
rem you could make to tune the database for faster response.
rem
rem
set feedback off
column SID new_value SID
select substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1) SID
from global_name;
prompt
rem Bypass multi-threaded server checks if there aren't any mts servers.
set termout off heading off
spool tmp~~~.sql
select '/*' from v$parameter where name = 'mts_servers' and value = '0';
spool off
@tmp~~~.sql
set termout on heading on
prompt ================================================================================;
prompt
prompt . Tuning Multi-Threaded Server
prompt
prompt ================================================================================;
prompt
prompt If SERVERS_HIGHWATER exceeds the MTS_SERVERS parameter, increase the number of
prompt MTS_SERVERS in the init&SID..ora file.
set heading off
select 'Current MTS_SERVERS number is ' || value from v$parameter where name = 'mts_servers';
set heading on
select * from v$mts;
-- */
!rm tmp~~~.sql
set termout on heading on
prompt ================================================================================;
prompt
prompt . Tuning The Library Cache
prompt
prompt ================================================================================;
prompt
prompt Library cache get/hit ratio for SQL AREA should be in high 90's. If not, there
prompt is room to improve the efficiency of your application.
select namespace, gets, gethits, gethitratio from v$librarycache;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt If reloads-to-pins ratio is greater than .01, increase SHARED_POOL_SIZE in
prompt the init&SID..ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(pins) "Executions", sum(reloads) "LC Misses",
sum(reloads)/sum(pins) "Ratio" from v$librarycache;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt Data Dictionary Cache -- If ratio is greater than .15, consider increasing
prompt SHARED_POOL_SIZE in the init&SID..ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(gets) "Total Gets", sum(getmisses) "Total Get Misses",
sum(getmisses)/sum(gets) "Ratio" from v$rowcache;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt Packages you might want to consider pinning into the shared pool:
column owner format a12
column name format a25
column type format a15
set feedback on
select owner, name, type, loads, executions, sharable_mem
from v$db_object_cache
where kept = 'NO'
and loads > 1 and executions > 50 and sharable_mem > 10000
and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by loads desc;
set feedback off
prompt --------------------------------------------------------------------------------;
prompt
prompt Shared Pool Reserved space -- The goal is to have zero REQUEST_MISSES and
prompt REQUEST_FAILURES, so increase SHARED_POOL_RESERVED_SIZE in the init&SID..ora
prompt file if either of them are greater than 0.
set heading off
select 'Current SHARED_POOL_RESERVED_SIZE value is ' || value from v$parameter where name = 'shared_pool_reserved_size';
set heading on
select request_misses, request_failures, last_failure_size
from v$shared_pool_reserved;
prompt
prompt ================================================================================;
prompt
prompt . Tuning the Data Dictionary Cache
prompt
prompt ================================================================================;
prompt
prompt If the gethitratio is greater than .15 -- increase the SHARED_POOL_SIZE
prompt parameter in the init&SID..ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(gets) "totl_gets", sum(getmisses) "totl_get_misses",
sum(getmisses)/sum(gets) * 100 "gethitratio"
from v$rowcache;
prompt
prompt ================================================================================;
prompt
prompt . Tuning The Data Buffer Cache
prompt
prompt ================================================================================;
prompt
prompt Goal is to have a Cache Hit Ratio greater than 90% -- if lower, increase value
prompt for DB_BLOCK_BUFFERS in the init&SID..ora file.
set heading off
select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';
set heading on
column value format 999,999,999,999
select name, value from v$sysstat where
name in ('db block gets', 'consistent gets', 'physical reads');
select 1 - (phy.value / (cur.value + con.value)) "Cache Hit Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt If the number of free buffers inspected is high or increasing, consider
prompt increasing the DB_BLOCK_BUFFERS parameter in the init&SID..ora file.
set heading off
select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';
set heading on
column value format 999,999,999
select name, value from v$sysstat where name = 'free buffer inspected';
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt A high or increasing number of waits indicates that the db writer cannot keep
prompt up writing dirty buffers. Consider increasing the number of writers using the
prompt DB_WRITER_PROCESSES parameter in the init&SID..ora file.
set heading off
select 'Current DB_WRITER_PROCESSES value is ' || value from v$parameter where name = 'db_writer_processes';
set heading on
select event, total_waits from v$system_event where event in
('free buffer waits', 'buffer busy waits');
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt If the LRU Hit percentage is less than 99%, consider adding more
prompt DB_WRITER_PROCESSES and increasing the DB_BLOCK_LRU_LATCHES parameter
prompt in the init&SID..ora file.
set heading off
select 'Current DB_WRITER_PROCESSES value is ' || v1.value || chr(10) ||
'Current DB_BLOCK_LRU_LATCHES value is ' || v2.value
from v$parameter v1,v$parameter v2
where v1.name = 'db_writer_processes' and v2.name = 'db_block_lru_latches';
set heading on
select name, 100 - (sleeps/gets * 100) "LRU Hit%" from v$latch
where name = 'cache buffers lru chain';
prompt
prompt ================================================================================;
prompt
prompt . Tuning The Redo Log Buffer
prompt
prompt ================================================================================;
prompt
prompt Ideally, there should never be a wait for log buffer space. Increase LOG_BUFFER
prompt in the init&SID..ora file if the selection below doesn't show "no rows selected".
set heading off
select 'Current LOG_BUFFER size is ' || value from v$parameter where name = 'log_buffer';
set heading on
set feedback on
select sid, event, state from v$session_wait
where event = 'log buffer space';
set feedback off
prompt --------------------------------------------------------------------------------;
prompt
prompt If there are any Wait events because of log switches, consider increasing the
prompt size of the redo log files.
set heading off
select 'Current size of redo log files is ' || bytes || ' bytes' from v$log where rownum = 1;
set heading on
column event format a30
select event, total_waits, time_waited, average_wait from v$system_event
where event like 'log file switch completion%';
prompt
prompt ================================================================================;
prompt
prompt Tables with Chain count greater than 10% of the number of rows:
set feedback on
select owner, table_name, num_rows, chain_cnt, chain_cnt/num_rows "Percent"
from dba_tables where (chain_cnt/num_rows) > .1 and num_rows > 0;
set feedback off
prompt ================================================================================;
prompt
prompt . Tuning Sorts
prompt
prompt ================================================================================;
prompt
prompt The ratio of disk sorts to memory sorts should be less than 5%. Consider
prompt increasing the SORT_AREA_SIZE parameter in the init&SID..ora file. You
prompt might also consider setting up separate temp tablespaces for frequent
prompt users of disk sorts.
set heading off
select 'Current SORT_AREA_SIZE value is ' || value from v$parameter where name = 'sort_area_size';
set heading on
select disk.value "Disk", mem.value "Mem", (disk.value/mem.value) * 100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';
prompt
prompt ================================================================================;
prompt
prompt . Tuning Rollback segments
prompt
prompt ================================================================================;
prompt
prompt If ratio of waits to gets is greater than 1%, you need more rbs segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
select sum(waits)*100/sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets"
from v$rollstat;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt Rollback segment waits -- any waits indicates need for more segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
select * from v$waitstat where class = 'undo header';
column event format a25
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt Rollback segment waits for transaction slots -- any waits indicates need for
prompt more segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
set feedback on
select * from v$system_event where event = 'undo segment tx slot';
set feedback off
prompt --------------------------------------------------------------------------------;
prompt
prompt Rollback contention -- should be zero for all rbs's.
column name format a10
select n.name,round (100 * s.waits/s.gets) "%contention"
from v$rollname n, v$rollstat s
where n.usn = s.usn;
prompt
clear columns
set feedback on echo on

Tuning Script

CREATE OR REPLACE PACKAGE BODY tuning
AS
PROCEDURE dpr_moved
IS
CURSOR c_table_name
IS
SELECT tablespace_name, table_name
FROM user_tables
WHERE TEMPORARY = 'N';

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_table_name LOOP
BEGIN
stmt :=
'ALTER TABLE '
|| i.table_name
|| ' MOVE TABLESPACE '
|| i.tablespace_name;

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ( 'Table moved completed: '
|| i.table_name
|| '.'
|| i.tablespace_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Table can not moved: '
|| i.table_name
|| '.'
|| i.tablespace_name
);
END;
END LOOP;
END;

PROCEDURE dpr_rebuild (index_tablespace IN VARCHAR2)
IS
CURSOR c_index
IS
SELECT table_name, index_name
FROM user_indexes;

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_index LOOP
BEGIN
stmt :=
'ALTER INDEX '
|| i.index_name
|| ' REBUILD TABLESPACE '
|| index_tablespace
|| ' PARALLEL';

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ( 'Index rebuild completed: '
|| i.table_name
|| '.'
|| i.index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Index can not rebuild: '
|| i.table_name
|| '.'
|| i.index_name
);
END;
END LOOP;
END;

PROCEDURE dpr_analyze (owner_name IN VARCHAR2)
IS
CURSOR c_table_name
IS
SELECT table_name
FROM dba_tables
WHERE UPPER (owner) = UPPER (owner_name) AND TEMPORARY = 'N';

CURSOR c_index_name (table_name1 IN VARCHAR2)
IS
SELECT index_name
FROM dba_indexes
WHERE UPPER (owner) = UPPER (owner_name)
AND table_name = table_name1
AND status = 'VALID';
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_table_name LOOP
DBMS_STATS.unlock_table_stats (ownname => UPPER (owner_name),
tabname => i.table_name,
stattype => 'ALL'
);

BEGIN
DBMS_STATS.gather_table_stats
(ownname => UPPER (owner_name),
tabname => i.table_name,
estimate_percent => DBMS_STATS.auto_sample_size,
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => DBMS_STATS.auto_degree,
no_invalidate => DBMS_STATS.auto_invalidate,
granularity => 'AUTO',
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
DBMS_OUTPUT.put_line ( 'Statistics gathered for table: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line
( 'Statistics can not gathered for table: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
);
END;

FOR j IN c_index_name (i.table_name) LOOP
BEGIN
DBMS_STATS.gather_index_stats
(ownname => UPPER (owner_name),
indname => j.index_name,
estimate_percent => DBMS_STATS.auto_sample_size,
DEGREE => DBMS_STATS.auto_degree,
no_invalidate => DBMS_STATS.auto_invalidate,
granularity => 'AUTO'
);
DBMS_OUTPUT.put_line ( 'Statistics gathered for index: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
|| '.'
|| j.index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line
( 'Statistics can not gathered for index: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
|| '.'
|| j.index_name
);
END;
END LOOP;

DBMS_STATS.lock_table_stats (ownname => UPPER (owner_name),
tabname => i.table_name
);
END LOOP;
END;

PROCEDURE dpr_shrink
IS
CURSOR c_table_name
IS
SELECT table_name
FROM user_tables
WHERE TEMPORARY = 'N' AND row_movement = 'DISABLED';

CURSOR c_table_shrink
IS
SELECT table_name
FROM user_tables
WHERE TEMPORARY = 'N' AND row_movement = 'ENABLED';

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_table_name LOOP
stmt := 'ALTER TABLE ' || i.table_name || ' ENABLE ROW MOVEMENT';

EXECUTE IMMEDIATE (stmt);
END LOOP;

FOR i IN c_table_shrink LOOP
BEGIN
stmt := 'ALTER TABLE ' || i.table_name || ' SHRINK SPACE';

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ('Table shrink completed: ' || i.table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Table can not shrink: ' || i.table_name);
END;
END LOOP;
END;

PROCEDURE dpr_rebuild_indexes (index_tablespace IN VARCHAR2)
IS
table_name user_indexes.table_name%TYPE;
index_name user_indexes.index_name%TYPE;
index_height index_stats.height%TYPE;
leaf_rows index_stats.lf_rows%TYPE;
del_leaf_rows index_stats.del_lf_rows%TYPE;
analyze_stmt VARCHAR2 (1024);
index_stmt VARCHAR2 (1024);
num_rows INTEGER;
del_leaf_pct INTEGER;
max_tree_height INTEGER := 3;
max_del_leaf_pct INTEGER := 20;
/* Define maximums. This section can be customized. */
index_id INTEGER;
index_status INTEGER;

CURSOR c_index
IS
SELECT table_name, index_name
FROM user_indexes
WHERE status = 'VALID';

CURSOR c_index_notvalid
IS
SELECT table_name, index_name
FROM user_indexes
WHERE status != 'VALID';
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

OPEN c_index;

LOOP
FETCH c_index
INTO table_name, index_name;

EXIT WHEN c_index%NOTFOUND;
analyze_stmt :=
'ANALYZE INDEX ' || index_name || ' VALIDATE STRUCTURE';

EXECUTE IMMEDIATE (analyze_stmt);

SELECT height, lf_rows, del_lf_rows
INTO index_height, leaf_rows, del_leaf_rows
FROM index_stats;

IF del_leaf_rows = 0 THEN
/* handle case where div by zero */
del_leaf_pct := 0;
ELSE
del_leaf_pct := (del_leaf_rows / leaf_rows) * 100;
END IF;

IF (index_height > max_tree_height)
OR (del_leaf_pct > max_del_leaf_pct) THEN
BEGIN
index_stmt :=
'ALTER INDEX '
|| index_name
|| ' REBUILD TABLESPACE '
|| index_tablespace
|| ' PARALLEL';

EXECUTE IMMEDIATE (index_stmt);

DBMS_OUTPUT.put_line ( 'Index rebuild completed: '
|| table_name
|| '.'
|| index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Index can not rebuild: '
|| table_name
|| '.'
|| index_name
);
END;
END IF;
END LOOP;

CLOSE c_index;

FOR i IN c_index_notvalid LOOP
BEGIN
index_stmt :=
'ALTER INDEX '
|| i.index_name
|| ' REBUILD TABLESPACE '
|| index_tablespace
|| ' PARALLEL';

EXECUTE IMMEDIATE (index_stmt);

DBMS_OUTPUT.put_line ( 'Index rebuild completed: '
|| i.table_name
|| '.'
|| i.index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Index can not rebuild: '
|| i.table_name
|| '.'
|| i.index_name
);
END;
END LOOP;
END;

PROCEDURE dpr_user_locks
IS
CURSOR c_lock
IS
SELECT s.SID, s.serial#, s.username, s.machine, s.osuser
FROM v$lock l, v$session s, dba_objects o
WHERE l.SID = s.SID AND o.object_id(+) = l.id1 AND l.BLOCK = 1;

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_lock LOOP
BEGIN
stmt :=
'ALTER SYSTEM KILL SESSION '
|| ''''
|| i.SID
|| ','
|| i.serial#
|| '''';

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ( 'User killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser, INSTR (i.osuser, '\'))
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'User can not killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser,
INSTR (i.osuser, '\')
)
);
END;
END LOOP;
END;
END;
/

message box

PROCEDURE message_box (msg_txt VARCHAR2)
IS
al_id alert;
al_button NUMBER;
BEGIN
al_id := FIND_ALERT ('MSGBOX');
SET_ALERT_PROPERTY (al_id, alert_message_text, msg_txt);
al_button := SHOW_ALERT (al_id);
END;

REGEXP_INSTR Examples

REGEXP_INSTR Examples
======================
In Oracle 10g
=============
regexp_instr (string, pattern)
regexp_instr (string, pattern, position)
regexp_instr (string, pattern, position, occurence)
regexp_instr (string, pattern, position, occurence, return-option)
regexp_instr (string, pattern, position, occurence, return-option, parameters)

----------------------------------------------------------------
In Oracle 11g
=============
regexp_instr (string, pattern, position, occurence, return-option, parameters,subexpr)

subexpr:
REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits
the pattern match to a specific subexpression in the search pattern.

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

1. source_string is a character expression that serves as the search value.
Pattern is the regular expression. It is usually a text literal .It can contain up to 512 bytes.

2. Pattern is the regular expression. It is usually a text literal .It can contain up to 512 bytes.

For more about pattern:

http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10759/ap_posix001.htm

3. position is a positive integer indicating the character of source_string where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_string.

4. occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.

5. return_option lets you specify what Oracle should return in relation to the occurrence:
If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.
If you specify 1, then Oracle returns the position of the character following the occurrence.

6. match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:

Parameters
parameters can be a combination of

i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
x: to ignore white spaces.

7. subexpr:
REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits
the pattern match to a specific subexpression in the search pattern.


Examples

The following example examines the string, looking for occurrences of one or more non-blank characters. Oracle begins searching at the first character in the string and returns the starting position (default) of the sixth occurrence of one or more non-blank characters.

SELECT
REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
'[^ ]+', 1, 6) "REGEXP_INSTR"
FROM DUAL;

REGEXP_INSTR
------------
37
The following example examines the string, looking for occurrences of words beginning with s, r, or p, regardless of case, followed by any six alphabetic characters. Oracle begins searching at the third character in the string and returns the position in the string of the character following the second occurrence of a seven-letter word beginning with s, r, or p, regardless of case.

SELECT
REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
'[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR"
FROM DUAL;

REGEXP_INSTR
------------
28


Examples in Oracle 11g only
===========================

The following examples use the subexpr argument to search for a particular subexpression in pattern. The first statement returns the position in the source string of the first character in the first subexpression, which is '123':

SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1)
"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
-------------------
1
The next statement returns the position in the source string of the first character in the second subexpression, which is '45678':

SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2)
"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
-------------------
4
The next statement returns the position in the source string of the first character in the fourth subexpression, which is '78':

SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4)
"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
-------------------
7

REGEXP_SUBSTR Examples

REGEXP_SUBSTR Examples

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma. Oracle returns the substring, including the leading and trailing commas.

SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',
',[^,]+,') "REGEXPR_SUBSTR"
FROM DUAL;

REGEXPR_SUBSTR
-----------------
, Redwood Shores,
The following example examines the string, looking for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.). Oracle searches for a minimum of three and a maximum of four occurrences of this substring between http:// and either a slash (/) or the end of the string.

SELECT
REGEXP_SUBSTR('http://www.oracle.com/products',
'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
FROM DUAL;

REGEXP_SUBSTR
----------------------
http://www.oracle.com/
The next two examples use the subexpr argument to return a specific subexpression of pattern. The first statement returns the first subexpression in pattern:

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1)
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
123
The next statement returns the fourth subexpression in pattern:

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4)
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
78

SIGN_WEBUTIL.BAT FILE CONTENT

SIGN_WEBUTIL.BAT FILE CONTENT


@ECHO OFF
REM
REM $Header: sign_webutil.bat 11-feb-2005.04:12:54 osingh Exp $
REM
REM sign_webutil.bat
REM
REM Copyright (c) 2002, 2005, Oracle. All rights reserved.
REM
REM NAME
REM sign_webutil.bat - Sample script to sign frmwebutil.jar and jacob.jar
REM USAGE
REM sign_webutil.bat
REM jar_file : Path of the jar file to be signed.
REM NOTES
REM This script uses keytool and jarsigner utilities, which usually comes
REM along with JDK in its bin directory. These two utilities must be
REM available in the PATH for this script to work. Otherwise, signing
REM will fail even though the script may show a successful signing.
REM

REM Local variables
SETLOCAL

REM
REM The following are the Distinguished Names for keytool. You can change them
REM to generate your own key.
REM CN = Common Name
REM OU = Organization Unit
REM O = Organization
REM C = Country code
REM
REM Certificate settings:
REM These are used to generate the initial signing certificate
REM Change them to suite your organisation
REM
SET DN_CN=Product Management
SET DN_OU=Development Tools
SET DN_O=Oracle
SET DN_C=US
REM
REM Give your keystore file
SET KEYSTORE="%HOME%/.keystore"
REM
REM If KEYSTORE already exists, old KEYSTORE_PASSWORD for the keystore file must
REM be correctly given here. If KEYSTORE does not already exist, any password
REM given here will be taken for the new KEYSTORE file to be created.
REM
SET KEYSTORE_PASSWORD=webutilpasswd
REM
REM Give your alias key here.
REM
SET JAR_KEY=webutil2
REM
REM Key Password for the given key to be used for signing.
REM
SET JAR_KEY_PASSWORD=webutil2
REM
REM Number of days before this certificate expires
REM
SET VALIDDAYS=360

REM
REM Signing script starts here...
REM
IF NOT "%1" == "" GOTO JAR_GIVEN
ECHO Jar file name not given for signing. Use
ECHO %0 ^
EXIT /b 1

:JAR_GIVEN
IF "%2" == "" GOTO PARAM_OKAY
ECHO Incorrect parameters. Use
ECHO %0 ^
EXIT /b 1

:PARAM_OKAY

IF EXIST %1 GOTO JAR_EXISTS
ECHO The given jar file %1 does not exist.
EXIT /b 1

:JAR_EXISTS

ECHO Generating a self signing certificate for key=%JAR_KEY%...
E:\DeveloperSuit10g\jdk\bin\keytool -genkey -dname "CN=%DN_CN%, OU=%DN_OU%, O=%DN_O%, C=%DN_C%" -alias %JAR_KEY% -keypass %JAR_KEY_PASSWORD% -keystore %KEYSTORE% -storepass %KEYSTORE_PASSWORD% -validity %VALIDDAYS%
REM Check for any error
IF %ERRORLEVEL% == 0 GOTO KEYTOOL_SUCCESS
ECHO .
ECHO There were warnings or errors while generating a self signing certificate. Please review them.
GOTO SIGN_JAR

:KEYTOOL_SUCCESS
ECHO ...successfully done.
ECHO.

:SIGN_JAR
REM Signing the jar
ECHO .
ECHO Backing up %1 as %1.old...
COPY /Y %1 %1.old

ECHO Signing %1 using key=%JAR_KEY%...
E:\DeveloperSuit10g\jdk\bin\jarsigner -keystore %KEYSTORE% -storepass %KEYSTORE_PASSWORD% -keypass %JAR_KEY_PASSWORD% %1 %JAR_KEY%
REM Check for any error
IF %ERRORLEVEL% == 0 GOTO SIGN_SUCCESS
ECHO .
ECHO There were warnings or errors while signing the jar. Please review them.
EXIT /b 1

:SIGN_SUCCESS
ECHO ...successfully done.
:END
EXIT /b 0

Application Server 10g configuration

Weutil:
1. choose two webutil jar file paste it forms90/java/.
2. allocate it formsweb.cfg file like web UtilArchive=frmwebutil.jar,jacob.jar.
baseHTMLjinitiator=webutiljini.htm
3. chose webutil.cfg and webutiljinit.htm file and pest it forms90/server/
4. copy webutil folder forms90/
5. configuration = foms90\server\webutil.cfg change :
transfer.database.enabled=TRUE
transfer.appsrv.workAreaRoot=c:\tmp
transfer.appsrv.write.1=c:\tmp
6. configuration = D:\ora10gdevsuite\forms90\server\default.env

classpath=-------; d:\ora10gdevsuite\jdk\jre\lib\rt.jar;d:\ora10gdevsuite\forms90\java\frmwebutil.jar

WEBUTIL_CONFIG=D:\ora10gdevsuite\forms90\server\webutil.cfg

for linux add == LD_ASSUME_KERNEL=2.4.19

7. configuration for DS = D:\ora10gdevsuite\j2ee\DevSuite\application-deployments\forms\forms90web\orion_web.xml
add virtual path like



For AS u have to do:\\Ris\c$\Oracle\Ora10gAS\forms90\server\forms90.conf

AliasMatch ^/forms90/webutil/(..*) "D:\FRHome/forms90/webutil/$1"

8. create a user and run webutil script then compile webutil.pll by this user to create webutil.plx file on forms90\

9. oracle home bin :::: D:\ora10gdevsuite\forms90\webutil\sign_webutil.bat ::::: jar path

Example : D:\FRHome\bin d:\FRHome\forms90\webutil\sign_webutil.bat D:\FRHome\forms90\java\jacob.jar and D:\FRHome\forms90\java\ frmwebutil.jar


(D:\ora10gdevsuite\jdk\bin\) for jarsigner and keytool)
run cmd
like := D:\ora10gdevsuite\jdk\bin\keytool
D:\ora10gdevsuite\jdk\bin\jarsigner





AS:

AS Installment:

1. Install the Oracle Infrastructure.
2. Install the Oracle AS (Naming Convention First Infrastructure then AS)
3. Configure The AS
a. First configure the formsweb.cfg file (forms90/server/formsweb.cfg)
b. Configure the registry.dat (forms90\java\oracle\forms\registry\ registry.dat)
1. font

default.fontMap.appFontnames=BrowalliaUPC,CordiaUPC,C39P12DlTt,C39HrP24DlTt,Tahoma,Impact,Courier New,Courier,courier,System,Terminal,Fixed,Fixedsys,Times,Times New Roman,MS Sans Serif,Arial

default.fontMap.javaFontnames=BrowalliaUPC,CordiaUPC,C39P12DlTt,C39HrP24DlTt,Tahoma,Impact,MonoSpaced,MonoSpaced,MonoSpaced,Dialog,MonoSpaced,Dialog,Dialog,Serif,Serif,Dialog,SansSerif

2. lov
app.ui.lovButtons=true
3. required
app.ui.requiredFieldVA=true

c. Configure the cgicmd.dat(\\Ris\c$\Oracle\Ora10gAS\reports\conf\ cgicmd.dat)
shis: userid=trama/ati@orcl server=rep_ris %*

cmd : rwserver –install server=rep_ris (Report server service creator)


4. For font Ambit and Subset (Location: d:\FRHome\tools\common90\unifont.ali)

[ PDF:Embed ]
"C39P12DlTt" = "V100013_.ttf"
"C39P24DlTt" = "V100014_.ttf"
"C39P12D|Tt" = "V100013_.ttf"
"C39P24D|Tt" = "V100014_.ttf"

[ PDF:Subset ]
"C39P12DlTt" = "V100013_.ttf"
"C39P24DlTt" = "V100014_.ttf"
"C39P12D|Tt" = "V100013_.ttf"
"C39P24D|Tt" = "V100014_.ttf"
"Tahoma" = "tahoma.ttf"
"BrowalliaUPC" = "BROWAU.TTF"
"CordiaUPC" = "CORDIAU.TTF"

5. Orarrp :Mone type
AddType application/x-orarrp-text rrpt
AddType application/x-orarrp-ps rrpp
AddType application/x-orarrp-pdf rrpa
AddType application/x-orarrp-rtf rrpr
AddType application/x-orarrp-ini rrpi
AddType application/x-orarrp-html rrph

See bmp picture::

6. Create DNS Alias
7. Confugure the index.html(Apache\Apache\htdocs\ index.html)

Details (a):
[shis] = the key Name
form=E:\HALIM_HIS\Application\SCF_LOGON.fmx usesdi=yes [Run form in the particular path and user]
userid=SCOTT/TIGER@TEST [Connect database]
width=100% [Screen width]
height=100%[Screen height]
colorscheme=blue [ Screen default color select hear]
pageTitle=HIS :: DEVELOPED BY HALIM LIMITED [internet Explorer title Customize hear ]
imagebase=codebase
archive_jini=f90all_jinit.jar,rolloverbutton.jar,getclientinfo.jar,infobutton.jar,images.jar [ Stored all jar file in the \forms90\java\
webUtilArchive=frmwebutil.jar,jacob.jar
baseHTMLjinitiator=webutiljini.htm
lookAndFeel=Oracle [Look and Flee for screen it should be two option one is oracle. Its default also and another is generic]
splashscreen=ati_splash.gif [use jar file that is store in the forms\java\images.jar. If u want to store another image in the jar then first u have to open jar file by using wingip.]
logo=ati_logo.gif gif [use jar file that is store in then forms\java\images.jar. If u want to store another image in the jar then first u have to open jar file by using wingip.]

separateFrame=false (Separate frame)

Details (b):
app.ui.lovButtons = true [ If u want to show lovbuttons then true. By default it is flase. ]

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


It is assumed that You install developer suite in drive D:\ and have webutil tools;

step-1
-----

paste webutil folder in the following path
D:\DevSuiteHome\forms90


step-2
------

Paste webutil.cfg,webutiljini.htm in the following path
D:\DevSuiteHome\forms90\server


step-3
------

Paste additional jar file in the following path
D:\DevSuiteHome\forms90\java


Step-4
------

open deafult.env file (path :>> D:\DevSuiteHome\forms90\server)

go to last line of file ( the line is:>> CLASSPATH=D:\DevSuiteHome\j2ee\OC4J_BI_Forms\applications\forms90app\forms90web\WEB-INF\lib\f90srv.jar;D:\DevSuiteHome\jlib\repository.jar;D:\DevSuiteHome\jlib\ldapjclnt9.jar;D:\DevSuiteHome\jlib\debugger.jar;D:\DevSuiteHome\jlib\ewt3.jar;D:\DevSuiteHome\jlib\share.jar;D:\DevSuiteHome\jlib\utj90.jar;D:\DevSuiteHome\jlib\zrclient.jar;D:\DevSuiteHome\reports\jlib\rwrun.jar)
Modify this line as your required jar files
:>> example
CLASSPATH=D:\DevSuiteHome\j2ee\OC4J_BI_Forms\applications\forms90app\forms90web\WEB-INF\lib\f90srv.jar;D:\DevSuiteHome\jlib\repository.jar;D:\DevSuiteHome\jlib\ldapjclnt9.jar;D:\DevSuiteHome\jlib\debugger.jar;D:\DevSuiteHome\jlib\ewt3.jar;D:\DevSuiteHome\jlib\share.jar;D:\DevSuiteHome\jlib\utj90.jar;D:\DevSuiteHome\jlib\zrclient.jar;D:\DevSuiteHome\reports\jlib\rwrun.jar;D:\DevSuiteHome\forms90\java\getclientinfo.jar;D:\DevSuiteHome\forms90\java\frmwebutil.jar;D:\DevSuiteHome\forms90\java\GetImageFileName.jar;D:\DevSuiteHome\forms90\java\images.jar;D:\DevSuiteHome\forms90\java\infobutton.jar;D:\DevSuiteHome\forms90\java\jacob.jar;D:\DevSuiteHome\forms90\java\rolloverbutton.jar


add following line in the end of file

#Webutil Config
WEBUTIL_CONFIG=D:\DevSuiteHome\forms90\server\webutil.cfg


Step-5
------

open forms90.conf file (path :>> D:\DevSuiteHome\forms90\server)

go to line where the following code already written
# Virtual path for runform.htm (used to run a form for testing purposes)
AliasMatch ^/forms90/html/(..*) "D:\DevSuiteHome/tools/web90/html/$1"

add following line after this
AliasMatch ^/forms90/webutil/(..*) "D:\DevSuiteHome/forms90/webutil/$1"

:>> example
# Virtual path for runform.htm (used to run a form for testing purposes)
AliasMatch ^/forms90/html/(..*) "D:\DevSuiteHome/tools/web90/html/$1"
AliasMatch ^/forms90/webutil/(..*) "D:\DevSuiteHome/forms90/webutil/$1"



Step-6a
------
1. Edit D:\DevSuiteHome\forms90\webutil\sign_webutil.bat file
as:
1. Go to Line
ECHO Generating a self signing certificate for key=%JAR_KEY%...
Next Line
edit Line in replace of previous address
D:\DevSuiteHome\jdk.........
2. Go to Line
ECHO Signing %1 using key=%JAR_KEY%...
Next Line
edit Line in replace of previous address
D:\DevSuiteHome\jdk.........

Step-6b
Sign additional jar file in the following way........

1.open cmd window
2.type d: and press enter
3.type cd DevSuiteHome\forms90\webutil and press enter
4.type sign_webutil D:\DevSuiteHome\forms90\java\rolloverbutton.jar and press enter

repeat line no. 4 as your reuired jar files



Step-7
------

Open formsweb.cfg file (path :>> D:\DevSuiteHome\forms90\server)
go to the end of file and then type the following line

[pp]
form=D:\aftab\test\image_browser.fmx usesdi=yes
userid=ppl/ppl@lin
width=100%
height=100%
colorscheme=blue
pageTitle=HIS :: DEVELOPED BY HALIM LIMITED
imagebase=codebase
archive_jini=f90all_jinit.jar,rolloverbutton.jar,getclientinfo.jar,infobutton.jar,images.jar,GetImageFileName.jar
webUtilArchive=frmwebutil.jar,jacob.jar
baseHTMLjinitiator=webutiljini.htm
splashscreen=ati_splash.gif
logo=ati_logo.gif
separateFrame=false


webutil configuration process complete