CREATE TABLE OUSERS_TEMP_EMP
(
  UCOMP_CODE      VARCHAR2(4 BYTE),
  UUSER_ID        VARCHAR2(40 BYTE),
  UUSER_GROUP_ID  VARCHAR2(60 BYTE),
  UUG_DESC        VARCHAR2(2000 BYTE)
)


INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'HANEF', 'RAK_ADHT', 'RAK ADMIN HOTEL AND TICKET BOOKING GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'SHABITH', 'RAK_ADHT', 'RAK ADMIN HOTEL AND TICKET BOOKING GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'VIJESH', 'RAK_ADMENT', 'RAK FINANCE ENTRY GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'SHARMA', 'RAK_ADMIN', 'RAK ADMINISTRATION DEPARTMENT GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'SADDIK', 'RAK_FINAD', 'FINANCE ADMIN'); 
COMMIT;


UCOMP_CODE,UUSER_ID,UUSER_GROUP_ID,UUG_DESC
RAK,HANEF,RAK_ADHT,RAK ADMIN HOTEL AND TICKET BOOKING GROUP
RAK,SHABITH,RAK_ADHT,RAK ADMIN HOTEL AND TICKET BOOKING GROUP
RAK,VIJESH,RAK_ADMENT,RAK FINANCE ENTRY GROUP
RAK,SHARMA,RAK_ADMIN,RAK ADMINISTRATION DEPARTMENT GROUP
RAK,SADDIK,RAK_FINAD,FINANCE ADMIN

CREATE TABLE PM_EMP_MAST
(
  EMP_CODE  VARCHAR2(12 BYTE),
  EMP_NAME  VARCHAR2(30 BYTE)
);



INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R1054', 'SHABITH CHAKKAPPOYAN'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R0303', 'MOHAMMAD HANIF MUHAMMAD'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R2029', 'HANIF'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R2881', 'VIJESH PAI THEKKATE'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R0174', 'JITENDRA KUMAR SHARMA'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R1911', 'JITENDRA SHARMA'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R0613', 'SADDIK M. PURAYIL'); 
COMMIT;


SELECT EMP_CODE,EMP_NAME FROM PM_EMP_MAST

EMP_CODE,EMP_NAME
R1054,SHABITH CHAKKAPPOYAN
R0303,MOHAMMAD HANIF MUHAMMAD
R2029,HANIF
R2881,VIJESH PAI THEKKATE
R0174,JITENDRA KUMAR SHARMA
R1911,JITENDRA SHARMA
R0613,SADDIK M. PURAYIL



--the output desired will be as follows.

emp_code,emp_name,uuser_id
R1054,SHABITH CHAKKAPPOYAN,SHABITH
R0613,SADDIK M. PURAYIL,SADDIK
R0303,MOHAMMAD HANIF MUHAMMAD,HANEF
R2029,HANIF,HANEF
R2881,VIJESH PAI THEKKATE,VIJESH
R0174,JITENDRA KUMAR SHARMA,SHARMA
R1911,JITENDRA SHARMA,SHARMA

SCOTT@orcl> CREATE INDEX emp_name_idx ON pm_emp_mast (emp_name) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl> COLUMN uuser_id FORMAT A20
SCOTT@orcl> SELECT emp_code, emp_name,
  2  	    MAX (uuser_id) KEEP (DENSE_RANK LAST ORDER BY score) uuser_id
  3  FROM   (SELECT SCORE (1) score, emp_code, emp_name, uuser_id
  4  	     FROM   pm_emp_mast, ousers_temp_emp
  5  	     WHERE  CONTAINS (emp_name, 'FUZZY (' || uuser_id || ', 1, 5000, W)', 1) > 0)
  6  GROUP  BY emp_code, emp_name
  7  /

EMP_CODE     EMP_NAME                       UUSER_ID
------------ ------------------------------ --------------------
R0174        JITENDRA KUMAR SHARMA          SHARMA
R0303        MOHAMMAD HANIF MUHAMMAD        HANEF
R0613        SADDIK M. PURAYIL              SADDIK
R1054        SHABITH CHAKKAPPOYAN           SHABITH
R1911        JITENDRA SHARMA                SHARMA
R2029        HANIF                          HANEF
R2881        VIJESH PAI THEKKATE            VIJESH

7 rows selected.