Misc Queries

Find the Duplicate Salaries
---------------------------------
select a.empno,a.ename,a.sal,a.deptno,b.empno dup_empno,b.ename dup_ename,b.sal dup_sal,b.deptno dup_deptno
from emp a, emp b
where a.sal=b.sal
and   a.empno<>b.empno
and   a.deptno=b.deptno
and   a.rowid>b.rowid;

EMPNO	ENAME	SAL	DEPTNO	DUP_EMPNO	DUP_ENAME	DUP_SAL	DUP_DEPTNO
7654	MARTIN	1,250.00	30	7521	WARD	1,250.00	30
7902	FORD	3,000.00	20	7788	SCOTT	3,000.00	20
7789	SCOTT	5,000.00	10	7839	KING	5,000.00	10

Nth Value

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

select level, max(sal) from emp

connect by prior sal > sal

group by level

order by 1 desc;

Free Space

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

SELECT   s.tablespace_name TABLESPACE,

         ROUND (((s.ublocks * 8) * 1024) / (1024 * 1024), 0) \"USEDMB\",

         ROUND ((((f.fblocks * 8) * 1024) / (1024 * 1024)), 0) \"FREEMB\",

         ublocks + fblocks \"TOTAL BLOCKS\",

         ROUND (((((ublocks + fblocks) * 8) * 1024) / (1024 * 1024)),

                0

               ) \"TOTAL MB\"

    FROM (SELECT   tablespace_name, SUM (blocks) ublocks

              FROM dba_segments

          GROUP BY tablespace_name) s,

         (SELECT   tablespace_name, SUM (blocks) fblocks

              FROM dba_free_space

          GROUP BY tablespace_name) f

   WHERE s.tablespace_name = f.tablespace_name

ORDER BY TABLESPACE;

User Privileges and Roles

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

select

  lpad('  ', 3*level) || granted_role "Users and roles privileges"

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username in (select username from dba_users)

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

       )

start with grantee is null

connect by grantee = prior granted_role;

Generate dates
with
    start_and_end ( start_date, end_date ) as (
      select TO_DATE ('2003/04/01', 'yyyy/mm/dd'), TO_DATE ('2003/04/07', 'yyyy/mm/dd') from dual
    )
-- end of input data; actual solution begins below this line
select start_date + level - 1 as dt
from   start_and_end
connect by level <= end_date - start_date + 1;

DT
----------
01/04/2003
02/04/2003
03/04/2003
04/04/2003
05/04/2003
06/04/2003
07/04/2003

Convert Numbers

Package with functions to convert numbers between the  Decimal, Binary, Octal and Hexidecimal numbering 

set serveroutput on
 
CREATE OR REPLACE PACKAGE dbms_numsystem AS
   function bin2dec (binval in char  ) RETURN number;
   function dec2bin (N      in number) RETURN varchar2; 
   function oct2dec (octval in char  ) RETURN number;
   function dec2oct (N      in number) RETURN varchar2; 
   function hex2dec (hexval in char  ) RETURN number;
   function dec2hex (N      in number) RETURN varchar2; 
END dbms_numsystem;
/
show errors
 
CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS
 
FUNCTION bin2dec (binval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(binval);
  for i in 1..digits loop
     current_digit := SUBSTR(binval, i, 1);
     current_digit_dec := to_number(current_digit);
     result := (result * 2) + current_digit_dec;
  end loop;
  return result;
END bin2dec;
 
FUNCTION dec2bin (N in number) RETURN varchar2 IS
  binval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     binval := mod(N2, 2) || binval;
     N2 := trunc( N2 / 2 );
  end loop;
  return binval;
END dec2bin;
 
FUNCTION oct2dec (octval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(octval);
  for i in 1..digits loop
     current_digit := SUBSTR(octval, i, 1);
     current_digit_dec := to_number(current_digit);
     result := (result * 8) + current_digit_dec;
  end loop;
  return result;
END oct2dec;
 
FUNCTION dec2oct (N in number) RETURN varchar2 IS
  octval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     octval := mod(N2, 8) || octval;
     N2 := trunc( N2 / 8 );
  end loop;
  return octval;
END dec2oct;
 
FUNCTION hex2dec (hexval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(hexval);
  for i in 1..digits loop
     current_digit := SUBSTR(hexval, i, 1);
     if current_digit in ('A','B','C','D','E','F') then
        current_digit_dec := ascii(current_digit) - ascii('A') + 10;
     else
        current_digit_dec := to_number(current_digit);
     end if;
     result := (result * 16) + current_digit_dec;
  end loop;
  return result;
END hex2dec;
 
FUNCTION dec2hex (N in number) RETURN varchar2 IS
  hexval varchar2(64);
  N2     number := N;
  digit  number;
  hexdigit  char;
BEGIN
  while ( N2 > 0 ) loop
     digit := mod(N2, 16);
     if digit > 9 then 
        hexdigit := chr(ascii('A') + digit - 10);
     else
        hexdigit := to_char(digit);
     end if;
     hexval := hexdigit || hexval;
     N2 := trunc( N2 / 16 );
  end loop;
  return hexval;
END dec2hex;
 
END dbms_numsystem;

-- Examples:
select dbms_numsystem.dec2bin(22)      from dual;
select dbms_numsystem.bin2dec('10110') from dual;
select dbms_numsystem.dec2oct(44978)   from dual;
select dbms_numsystem.oct2dec(127662)  from dual;
select dbms_numsystem.dec2hex(44978)   from dual;

select dbms_numsystem.hex2dec('AFB2')  from dual;

Leap Year

Leap Year Query

select  extract(year from hiredate) years,

       decode( mod(extract(year from hiredate), 4), 0,

          decode( mod(extract(year from hiredate), 400), 0, 'Leap Year',

             decode( mod(extract(year from hiredate), 100), 0, 'Not a Leap Year', 'Leap Year')

          ), 'Not a Leap Year'

       ) as leap_year_indicator

from   emp;

Output Look Like

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

YEARS	LEAP_YEAR_INDICATOR
1980	Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1987	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1987	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1982	Not a Leap Year
2003	Not a Leap Year

Unused Columns in Oracle Database

Find Unused Columns in Oracle Database

CREATE OR REPLACE FUNCTION GET_NULL_COLUMNS ( 

   IN_TABLE_NAME VARCHAR2 DEFAULT NULL) 

   RETURN SYS.ODCIVARCHAR2LIST 

 IS 

   L_NULL_COLS   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST (); 

   LN$ROW_COUNT  NUMBER; 

   LN$NULL_COUNT  NUMBER; 

   LN$INDEX    NUMBER := 1; 

   CURSOR LCUR$COLS (P_TABLE_NAME VARCHAR2) 

   IS 

    SELECT TABLE_NAME, COLUMN_NAME 

     FROM USER_TAB_COLS 

     WHERE DECODE (P_TABLE_NAME, TABLE_NAME, 1, NULL, 1, 0) = 1; 

 BEGIN 

   FOR LREC$COLS IN LCUR$COLS (IN_TABLE_NAME) 

   LOOP 

    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || LREC$COLS.TABLE_NAME 

      INTO LN$ROW_COUNT; 

    EXECUTE IMMEDIATE  'SELECT COUNT(1) FROM ' 

             || LREC$COLS.TABLE_NAME 

             || ' WHERE ' 

             || LREC$COLS.COLUMN_NAME 

             || ' IS NULL' 

      INTO LN$NULL_COUNT; 

    IF LN$ROW_COUNT = LN$NULL_COUNT 

    THEN 

      L_NULL_COLS.EXTEND; 

      L_NULL_COLS (LN$INDEX) := 

       LREC$COLS.TABLE_NAME || '.' || LREC$COLS.COLUMN_NAME; 

      LN$INDEX := LN$INDEX + 1; 

    END IF; 

   END LOOP; 

   RETURN L_NULL_COLS; 

 END;  

Time Calculation

Following Example shows how we calculate the time.

CREATING THE TABLE

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

CREATE TABLE KMH_1

(  PKKEY  NUMBER CONSTRAINT PK_PKEY PRIMARY KEY,

   ID     NUMBER,

   NAME   VARCHAR2(20 BYTE),

   DATE1  DATE);

DML STATEMENTS FOR TABLE

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

INSERT INTO KMH_1

VALUES

(1,1,'A',TO_DATE('12/03/2013 07:30','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(2,1,'A',TO_dATE('12/03/2012  16:00','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(3,2,'B',TO_DATE('12/03/2013 07:30','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(4,2,'B',TO_dATE('12/03/2012  16:00','DD/MM/RRRR HH24:MI')); 

/

QUERY

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

SELECT A.ID,A.NAME,MAX(A.START_TIME) ST,MAX(A.END_TIME) ET,

TRUNC(REPLACE(MAX(A.END_TIME),':','.')  - REPLACE(MAX(A.START_TIME),':','.'),0) NO_HRS

FROM 

(SELECT ID,NAME, 

CASE WHEN TO_NUMBER(TO_CHAR(DATE1,'HH24'))BETWEEN 0 AND 12 THEN TO_CHAR(DATE1,'HH24:MI') END START_TIME,

CASE WHEN TO_NUMBER(TO_CHAR(DATE1,'HH24'))BETWEEN 13 AND 23 THEN TO_CHAR(DATE1,'HH24:MI') END END_TIME

FROM KMH_1

ORDER BY PKKEY) A

GROUP BY A.ID,A.NAME

Bulk Collect

PROCEDURE process_all_rows
IS
   CURSOR table_with_227_rows_cur
   IS
      SELECT * FROM table_with_227_rows;

   TYPE table_with_227_rows_aat IS
      TABLE OF table_with_227_rows_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   l_table_with_227_rows table_with_227_rows_aat;
BEGIN  
   OPEN table_with_227_rows_cur;
   LOOP
      FETCH table_with_227_rows_cur
         BULK COLLECT INTO l_table_with_227_rows LIMIT 100;

         EXIT WHEN table_with_227_rows_cur%NOTFOUND;     /* cause of missing rows */

      FOR indx IN 1 .. l_table_with_227_rows.COUNT
      LOOP
         analyze_compensation (l_table_with_227_rows(indx));
      END LOOP;
   END LOOP;

   CLOSE table_with_227_rows_cur;
END process_all_rows;