17 Feb 2017

Timer Example On Web Server Form.
CREATE ONE CONTROL BLOCK HAVING ONE DISPLAY ITEM AND FIVE PUSH BUTTONS.
PROPERTIES OF DISPLAY ITEM
NAME : MY_SCROLL_BAR
DATA TYPE          : CHARACTER
MAX LENGTH     :30
XPOSITION         : 259
YPOSITION          :157
WIDTH                  : 5
HEIGHT                 :12

PUSH BUTTONS  PROPERTIES
NAME                                   : CREATE              STOP     RESET    DELETE  DEFAULT
WIDTH                                 : 113                       113         113         113         0
HEIGHT                               : 20                         20           20           20           0
XPOS                                   : 25                         86           421         464         0
YPOS                                   : 52                         153         153         52           0
Keyboard Navigation           : No                        No          No          No          Yes
Mouse Navigation                : No                        No          No          No          Yes

Trigger on Create Button
DECLARE
                V_TIMER TIMER;
BEGIN
                -- FIND TIMER FIRST IF ALREADY EXISTS.
                V_TIMER := FIND_TIMER('PRGBARTMR');
                IF ID_NULL(V_TIMER) THEN
                -- CREATING TIMER FOR ONE SECOND... ONE SECOND = 1000 MILLISECOND
                V_TIMER := CREATE_TIMER('PRGBARTMR', 1000, REPEAT);
                ELSE
                                MESSAGE('ALREADY EXISTS.');
                END IF;
                -- WILL HANDLE THIS TIMER IN FORM LEVEL WHEN-TIMER-EXPIRED TRIGGER
END;

Trigger On Stop Button
DECLARE
                V_TIMER TIMER;
BEGIN
                -- FIND THE TIMER FIRST
                V_TIMER := FIND_TIMER('PRGBARTMR');
                IF NOT ID_NULL(V_TIMER) THEN
                                -- THIS WILL STOP THE TIMER AFTER ONE MILLISECOND
                                SET_TIMER(V_TIMER, 1, NO_REPEAT);
                END IF;
                -- WILL HANDLE THIS TIMER IN FORM LEVEL WHEN-TIMER-EXPIRED TRIGGER
END;




Trigger On Delete Button
DECLARE
                V_TIMER TIMER;
BEGIN
                -- FIND THE TIMER FIRST
                V_TIMER := FIND_TIMER('PRGBARTMR');
                IF NOT ID_NULL(V_TIMER) THEN
                                -- THIS WILL DELETE THE TIMER
                                DELETE_TIMER(V_TIMER);
                END IF;
END;

Trigger On Reset Button
DECLARE
                V_TIMER TIMER;
BEGIN
                -- FIND THE TIMER FIRST
                V_TIMER := FIND_TIMER('PRGBARTMR');
                IF NOT ID_NULL(V_TIMER) THEN
                                -- THIS WILL RE-START THE TIMER AFTER ONE SECOND
                                SET_TIMER(V_TIMER, 1000, REPEAT);
                ELSE
                                V_TIMER := CREATE_TIMER('PRGBARTMR',1000, REPEAT);
                END IF;
                -- WILL HANDLE THIS TIMER IN FORM LEVEL WHEN-TIMER-EXPIRED TRIGGER
END;

Coding on When Timer Expired
DECLARE
                V_TIMER_NAME VARCHAR2(30);
                V_WIDTH NUMBER;
BEGIN
                -- GET THE TIMER NAME FIRST.. TO KNOW WHICH TIMER HAS EXPIRED.. IF MULTIPLE TIMER ARE RUNNING
  V_TIMER_NAME := GET_APPLICATION_PROPERTY(TIMER_NAME);
  -- CHECK IF THE SAME TIMER WITH CAPITAL LETTERS
  IF V_TIMER_NAME = 'PRGBARTMR' THEN
                 V_WIDTH := GET_ITEM_PROPERTY('BLKTMR.PRGBAR', WIDTH);
                 IF V_WIDTH < 100 THEN
                                  V_WIDTH := V_WIDTH + 5;
                 ELSE
                                  V_WIDTH := 0;
                 END IF;
                 SET_ITEM_PROPERTY('BLKTMR.PRGBAR', WIDTH, V_WIDTH);
                END IF;
END;


13 Feb 2017

UTL_MATCH : String Matching by Testing Levels of Similarity/Difference

The UTL_MATCH package was introduced in Oracle 10g Release 2, but first documented (and therefore supported) in Oracle 11g Release 2.
To See The Details Follow the following Link
My Query Example
SELECT 'MUHAMMAD' COL1,
       'MOHAMMAD' COL2,
       UTL_MATCH.JARO_WINKLER_SIMILARITY('MUHAMMAD', 'MOHAMMAD') AS JWS
  FROM DUAL
UNION
SELECT 'MOHAMMAD',
       'MEHMOOD',
       UTL_MATCH.JARO_WINKLER_SIMILARITY('MEHMOOD', 'MOHAMMAD') AS JWS
  FROM DUAL

UNION

SELECT 'MARRY',
       'MARRIE',
       UTL_MATCH.JARO_WINKLER_SIMILARITY('MARRY', 'MARRIE') AS JWS
  FROM DUAL;

OUTPUT
    COL1 COL2 JWS
1 MARRY MARRIE 89
2 MOHAMMAD MEHMOOD 74
3 MUHAMMAD MOHAMMAD 92

6 Feb 2017

CREATE OR REPLACE TYPE HR.a_email_address as object (email_add VARCHAR2(100))
/

CREATE OR REPLACE TYPE HR.a_email_add as table of a_email_address
/

CREATE OR REPLACE FUNCTION HR.get_email_add (rcplist IN VARCHAR2)
  RETURN a_email_add
  IS
-- rcplist as parameter should contain comma separated data ( if more than 1 recipient )
  l_email_add a_email_add := a_email_add();
  xx number:=1;
  --
Begin
  for rec in (
              select substr(rcplist,
                     instr(','||rcplist||',', ',', 1, rn),
                     instr(','||rcplist||',', ',', 1, rn+1)
                     - instr(','||rcplist||',', ',', 1, rn) - 1) mvalue
                from ( select level rn from dual
             connect by level <= length(rcplist)-length(replace(rcplist,',',''))+1)
             )
  loop
   --DBMS_OUTPUT.PUT_LINE (rec.mvalue);
     l_email_add.extend;
     l_email_add(l_email_add.last) := a_email_address(NULL);
     --
     l_email_add(xx).email_add := rec.mvalue;
     xx:=xx+1;
   end loop;
  --
  Return l_email_add;
End;
/

To test above code

SELECT * FROM TABLE(get_email_add(‘afzaal@mcbah.com,shabbir@mcbah.com,Kashif@mcbah.com’)

/
Websites

SQL / PL-SQL on Oracle Community - various related documents and articles to understand various features / functionalities



SQL Full Outer Koin



this is a PDF file which is online and on page # 11, 12 there is mentioned Forms 6i, Character Set and related Patch, please check.

Official site download link for latest version, if want to:

Working Key
ver 9.0.6
Product Code:  46q6ntekz7kx5ctygpjeeh6f5wavambtly 
Serial Number: 580356
Password:      xs374ca 


ver 11.0
Registration Code: 
Product Code: 4t46t6vydkvsxekkvf3fjnpzy5wbuhphqz 
Serial Number: 601769 
Password: xs374ca

Learn Oracle SQL also find readymade Scripts
attached is a PDF document from Intel Corporation for their new PC's ( new not for all but most of us ) or visit below link:

A must visit site for all of us, here is real Treasure, almost every famous Qaari
Oracle Forms & Reports 12c Installation guide  ( it includes installation guide of Web Logic Server )

Link 1 ( on Linux )

Link 2  ( on Win 7 )

Link 3  ( on Win 7 )
If someone wants to, here is a link contain complete step-by-step guide to install Oracle Forms & Reports 12c on Windows 7. Also contain links to install Weblogic Server and Database 12c.


Oracle Official Community Forum

Comprehensive Oracle related information for various versions.

Forum for approx. all Oracle technologies.
Please read it, we have to be much more thankful to ALLAH Sub’hanahu Wa-Ta’ala.
Very important topic for DBAs and Developers, but this much DEPENDS on how we have designed our DB Structures.
Oracle Regular Expression Enhancement in 11g
SQL

Storing and Retrieving XML Data in Oracle 11g
Site for various Samples, used PJC’s in Forms 10g


A Forum to discuss, understand and knowledge on Oracle Forms, Plug able Java Components (PJC) - Share knowledge and experience

 Webutil Configuration file for Forms 10g to run locally from our PC, here are some Forms 10g Tips & Techniques for us ( may be you will find something new for you… )


ADF


Here is a sample and results of INTERPRETED and NATIVE compilation feature of Oracle PL/SQL, kindly have a look.

-- ==========================================================================================
-- test of INTERPRETED (default) and NATIVE compiled objects (Packages/Procedures/Functions)
-- NOTE: noticeable performance / difference will be seen on where much / heavy calculations
-- (of any kind) involved
-- ==========================================================================================

CREATE PACKAGE ncomp_test AS
     PROCEDURE simple(p_comp IN VARCHAR2);
     PROCEDURE pls(p_comp IN VARCHAR2);
END ncomp_test;
/

CREATE OR REPLACE PACKAGE BODY ncomp_test AS
     -- define variables here to use in all Procedures/Functions of Package Body
     l_start NUMBER:=0;
     --
     --
     PROCEDURE simple(p_comp IN VARCHAR2) IS
        s SIMPLE_INTEGER := 0;
     BEGIN
       l_start := DBMS_UTILITY.get_time;
       FOR i IN 1 .. 10 ** 8 LOOP
           s := s + 1;
       END LOOP;
       DBMS_OUTPUT.put_line('Simple Integer took : ' ||
                       Round(((DBMS_UTILITY.get_time - l_start)/60),2)||' Seconds');
     END simple;
     --
     --
     PROCEDURE pls(p_comp IN VARCHAR2) IS
        p PLS_INTEGER := 0;
     BEGIN
        l_start := DBMS_UTILITY.get_time;
        FOR i IN 1 .. 10 ** 8 LOOP
           p := p + 1;
        END LOOP;
       DBMS_OUTPUT.put_line('PLS Integer took : ' ||
                       Round(((DBMS_UTILITY.get_time - l_start)/60),2)||' Seconds');
     END pls;

END ncomp_test;
/

-- this will run in default INTERPRETED mode
-- =========================================
BEGIN
  ncomp_test.simple('INTERPRETED');
  ncomp_test.pls('INTERPRETED');
END;
/

Simple Integer took : 4.7 Seconds
PLS Integer took : 4.55 Seconds

PL/SQL procedure successfully completed.


-- this will set mode to NATIVE
ALTER PACKAGE ncomp_test COMPILE PLSQL_CODE_TYPE = NATIVE;

-- this will run in NATIVE mode
-- ============================
BEGIN
  ncomp_test.simple('NATIVE');
  ncomp_test.pls('NATIVE');
END;
/

Simple Integer took : .65 Seconds
PLS Integer took : 1.68 Seconds

PL/SQL procedure successfully completed.


Test again in default mode

-- this will set mode to INTERPRETED – default mode
ALTER PACKAGE ncomp_test COMPILE PLSQL_CODE_TYPE = INTERPRETED;

BEGIN
  ncomp_test.simple('INTERPRETED');
  ncomp_test.pls('INTERPRETED');
END;
/

Simple Integer took : 4.7 Seconds
PLS Integer took : 4.55 Seconds


PL/SQL procedure successfully completed.
Here are findings regarding Oracle 11g, here are with complete examples. 

1)      Seudo Column in Tables - apply any complex formula here

in this example: update Sale_category column based on value of sales_amt directly
without user interaction, its like a DEFAULT value on column
---------------------------------------------------------------------------------

create table hr.sales
(
   sales_id      number,
   cust_id       number,
   sales_amt     number,
   sale_category varchar2(6)
   generated always as
   (
      case
         when sales_amt <= 10000
            then 'LOW'
         when sales_amt > 10000
            and sales_amt <= 100000
            then case
               when cust_id < 101 then 'LOW'
               when cust_id between 101 and 200 then 'MEDIUM'
               else 'MEDIUM'
            end
         when sales_amt > 100000
            and sales_amt <= 1000000
            then case
               when cust_id < 101 then 'MEDIUM'
               when cust_id between 101 and 200 then 'HIGH'
               else 'ULTRA'
            end
         else 'ULTRA'
      end
    ) virtual
);

insert into hr.sales (sales_id, cust_id, sales_amt) values (1,1,100);
insert into hr.sales (sales_id, cust_id, sales_amt) values (2,101,100001);
insert into hr.sales (sales_id, cust_id, sales_amt) values (3,102,1500);
insert into hr.sales (sales_id, cust_id, sales_amt) values (4,102,10000);
insert into hr.sales (sales_id, cust_id, sales_amt) values (5,101,100000);
insert into hr.sales (sales_id, cust_id, sales_amt) values (5,101,1000000);
insert into hr.sales (sales_id, cust_id, sales_amt) values (6,102,10000000);

select * from hr.sales;

  SALES_ID    CUST_ID  SALES_AMT SALE_C
---------- ---------- ---------- ------
         1          1        100 LOW
         2        101     100001 HIGH
         3        102       1500 LOW
         4        102      10000 LOW
         5        101     100000 MEDIUM
         5        101    1000000 HIGH
         6        102   10000000 ULTRA

7 rows selected.

drop table hr.sales;

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


2)      Using a Constraint to Enforce a Phone Number Format with Regular Expression

Regular expressions are useful for enforcing constraints—for example, to ensure that phone numbers are entered into the

database in a standard format. Example 3-1 creates a contacts table and adds a CHECK constraint to the p_number column to

enforce the below format model:

(XXX) XXX-XXXXXXX
(092) 333-1234567


CREATE TABLE hr.contacts (
  cust_name    VARCHAR2(30),
  cust_mobile  VARCHAR2(30)
  CONSTRAINT c_contacts_pnf
  CHECK (REGEXP_LIKE (cust_mobile, '^\(\d{3}\) \d{3}-\d{7}$'))
);

test entering wrong format in cust_mobile column
------------------------------------------------
HR.@.xe > insert into hr.contacts values ('My Customer 1','123456789');
insert into hr.contacts values ('My Customer 1','123456789')
*
ERROR at line 1:
ORA-02290: check constraint (HR.C_CONTACTS_PNF) violated


HR.@.xe > insert into hr.contacts values ('My Customer 1','(092) 333-123456');
insert into hr.contacts values ('My Customer 1','(092) 333-123456')
*
ERROR at line 1:
ORA-02290: check constraint (HR.C_CONTACTS_PNF) violated


now make a correct entry ...
----------------------------
  1* insert into hr.contacts values ('My Customer 1','(092) 333-1234567')
HR.@.xe > /

1 row created.


commit;