Stored Procedure to compare two table field wise

----- This SP will take two table as input and will compare for thier identical columns.
----- those columns should be identical in name, then it will compare only.
----- after compareing those two columns , it will give common values count for that column from both table
----- it will also give the count for those values which is in 1st table but not in 2nd table , vice versa.
----- it will also give the identical columns has the same data type and precision or not ..?


create or replace procedure SP_TABLE_COMPARISON(Tab_Name1 IN VARCHAR2, Tab_Name2 IN VARCHAR2) as
Table_Name1 USER_TAB_COLUMNS.table_name%type;
Table_Name2 USER_TAB_COLUMNS.table_name%type;
Column_Name1 varchar2(100);
Data_Type1 varchar2(100);
Data_Length1 varchar2(100);
Common_count varchar2(100);
In1_NotIn2 varchar2(100);
In2_NotIn1 varchar2(100);
Total_count_1 varchar2(100);
Total_count_2 varchar2(100);
Prec_flag  varchar2(100);
Data_flag  varchar2(100);
Column_Name2 varchar2(100);
Data_Type2 varchar2(100);
Data_Length2 varchar2(100);
Col_flag varchar2(100);

------------- creating cursor for two tables
------------- for thier all columns and constraints

CURSOR ALL_COLUMN1(tablename1 varchar2) is
SELECT
COLUMN_NAME ,
DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM USER_TAB_COLUMNS WHERE TABLE_NAME=tablename1 ;
CURSOR ALL_COLUMN2(tablename2 varchar2) is
SELECT
COLUMN_NAME ,
DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM USER_TAB_COLUMNS WHERE TABLE_NAME=tablename2 ;

ALL_COLUMN1_VIEW        ALL_COLUMN1%rowtype;
ALL_COLUMN2_VIEW        ALL_COLUMN2%rowtype;

begin
--set serveroutput on;
--- opening the cursor ------- 

execute immediate ' truncate table TABLE_COMPARE_REPORT ';
Open ALL_COLUMN1(Tab_Name1);
----start the loop for first table -----
LOOP
Fetch ALL_COLUMN1 into ALL_COLUMN1_VIEW ;
      EXIT WHEN ALL_COLUMN1%NOTFOUND;
      Open ALL_COLUMN2(Tab_Name2);
     
      Column_Name1 := ALL_COLUMN1_VIEW.COLUMN_NAME;
      Data_Type1 := ALL_COLUMN1_VIEW.DATA_TYPE;
      Data_Length1 := ALL_COLUMN1_VIEW.DATA_LENGTH;
      Col_flag := '' ;
      Prec_flag := '' ;
      Data_flag := '' ;
---start the loop for second table ----     
      LOOP
            Fetch ALL_COLUMN2 into ALL_COLUMN2_VIEW ;
            EXIT WHEN ALL_COLUMN2%NOTFOUND;
           
               Column_Name2 := ALL_COLUMN2_VIEW.COLUMN_NAME;
               Data_Type2 := ALL_COLUMN2_VIEW.DATA_TYPE;
               Data_Length2 := ALL_COLUMN2_VIEW.DATA_LENGTH;
           
            IF (Column_Name1 = Column_Name2)
               THEN Col_flag := 'GOT IT..' ;
                
               execute immediate ' select count(distinct ' || Column_Name1 || ') from ' || Tab_Name1   into Total_count_1;
               execute immediate ' select count(distinct ' || Column_Name2 || ') from ' || Tab_Name2   into  Total_count_2;
              
                execute immediate ' select count(*) from (
                                     select ' || Column_Name1 || ' from ' || Tab_Name1 ||
                                   ' intersect
                                    select ' || Column_Name2 || ' from ' ||  Tab_Name2 || ' ) '  into Common_count;
                                       
                execute immediate ' select count(*) from (
                                     select ' || Column_Name1 || ' from ' || Tab_Name1 ||
                                   ' minus
                                    select ' || Column_Name2 || ' from ' ||  Tab_Name2 || ' ) '  into In1_NotIn2;
                                 
                               
                execute immediate ' select count(*) from (
                                     select ' || Column_Name2 || ' from ' || Tab_Name2 ||
                                   ' minus
                                    select ' || Column_Name1 || ' from ' ||  Tab_Name1 || ' ) ' into In2_NotIn1;
               
               
                IF (Data_Type1 = Data_Type2 )
                  THEN Data_flag := 'match' ;
                    ELSE Data_flag := 'no match' ;
                 END IF;
               
                   IF (Data_Length1 = Data_Length2 )
                  THEN Prec_flag := 'match' ;
                    ELSE  Prec_flag := 'no match' ;
                 END IF;
                              
                                  
                
                insert into TABLE_COMPARE_REPORT(
                Column_of_Table1      ,    
                Column_of_Table2       ,  
                Total_Count_in_Tab1,
                Total_Count_in_Tab2,
                Total_Common_Values      , 
                Value_in_Tab1_not_in_Tab2 ,
                Value_in_Tab2_not_in_Tab1 ,
                Data_Type_Check  ,         
                Precision_Check  )        
               
                values
                (
                Column_Name1,
                Column_Name2,
                Total_count_1,
                Total_count_2,
                Common_count,
                In1_NotIn2,
                In2_NotIn1,
                Data_flag,
                Prec_flag
                )   ;            
                 EXIT;
            END IF ;
           
      COMMIT;
      END LOOP;
      CLOSE ALL_COLUMN2;
END LOOP;
CLOSE ALL_COLUMN1;
end ;
 

Stored Procedure to Find out Getting Different pattern for field

---------Stored procedure for Diffirent pattern finding for one column of table.
--------- input for this SP is TABLE_NAME and COLUMN_NAMEcreate or replace procedure S_PATTERN_MATCHING(my_table IN VARCHAR2, my_col IN VARCHAR2)
AUTHID CURRENT_USER IS
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(1000);
col_value varchar2(1000);
col_count VARCHAR2(20);
Table_name varchar2(1000);
val_len number(10);
counter number(10);
counter1 number(10);
SL_NO number(10);
pattern_value varchar2(2000);
curr_pattern_value varchar2(2000);
tab_val pattern_table_list%rowtype;
BEGIN
   DBMS_OUTPUT.ENABLE(1000000);
 query_str := ' select ' || my_col || ' from ' || my_table || ' order by ' || my_col ;
curr_pattern_value :=  NULL;
counter1 := 1 ;
SL_NO := 1 ;
execute immediate ' truncate table COLUMN_PATTERN_VALUES ';
OPEN c FOR query_str ;
LOOP FETCH c INTO col_value;
     EXIT WHEN c%NOTFOUND;
          IF(col_value is NULL)
                       THEN val_len := 0;
          ELSE IF (col_value is NOT NULL)
                        THEN val_len := length(col_value);
               END IF;
          END IF;
         
counter := 1 ;
pattern_value := '';
-----Loop for each charater in value ---------------
FOR counter in 1 .. val_len
  LOOP
    IF(SUBSTR(col_value,counter,1)= ' ')
         Then pattern_value := pattern_value || ' ';
    ELSE IF(LENGTH(TRIM(TRANSLATE(SUBSTR(col_value,counter,1),'0123456789',' '))) is NULL)
         Then pattern_value := pattern_value || '9';
    ELSE IF(LENGTH(TRIM(TRANSLATE(SUBSTR(col_value,counter,1), 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
                                                                                        ,' '))) is NULL)
         Then pattern_value := pattern_value || 'X';

    ELSE IF(LENGTH(TRIM(TRANSLATE(SUBSTR(col_value,counter,1), 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
                                                                                        ,' '))) is NOT NULL)
         Then pattern_value := pattern_value || SUBSTR(col_value,counter,1);

END IF;
END IF;
END IF;
END IF;
END LOOP;
-----END Loop for each charater in value ---------------
-----inserting into COLUMN_PATTERN_VALUES table , column value and thier pattern---insert into COLUMN_PATTERN_VALUES (SL_NO,Pattern,Val)
values(SL_NO,pattern_value,col_value);

-----dbms_output.put_line(pattern_value);
-----dbms_output.put_line(col_value);

SL_NO := SL_NO+1;

END LOOP;

----- below query will create the view which shows, how many frequency of one kind of pattern we

execute immediate ' create or replace view Pattern_frequency as
select pattern,  count(*) as Pattern_count  from COLUMN_PATTERN_VALUES group by pattern ';
commit;
CLOSE c; 
End  ;

Oracle - Not frequently used features

WITH Clause : Subquery Factoring

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.

SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc
WHERE  e.deptno = dc.deptno;

Using a WITH clause this would look like the following.

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno;

The difference seems rather insignificant here.
What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.

SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc1,
       emp m,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

Using the WITH clause this would look like the following.

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.
If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient. The MATERIALIZE and INLINE optimizer hints can be used to influence the decision. The undocumented MATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

Even when there is no repetition of SQL, the WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.

WITH 
  dept_costs AS (
    SELECT dname, SUM(sal) dept_total
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
    GROUP BY dname),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;

In the previous example, the main body of the query is very simple, with the complexity hidden in the WITH clause.

Oracle Complicated Query

·  To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp); 

·  To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp); 

·  Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal); 

·  Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal); 

·  Select FIRST n records from a table.
select * from emp where rownum <= &n; 

·  Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp); 

·  List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp); 
alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null; 

·  How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc; 

·  How to get 3 Min salaries ?
select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal); 

·  How to get nth max salaries ?
select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal); 

·  Select DISTINCT RECORDS from emp table.
select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno); 

·  How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno); 

·  Count of number of employees in  department  wise.
select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;