Another way of manipulating large amount of data in oracle (Remove duplicate)

Below example explains, 1 way to remove duplicate data. tmp_data table contains 6millions of record, but with duplicate. Table tmp_duplicate contains all duplicates data that exists in tmp_data.

create or replace procedure delete_duplicate is

cursor c is select sn from tmp_data a where a.sn in (select sn from tmp_duplicate);

type c1_type is table of c%rowtype;

l_c1 c1_type;

begin

open c;

loop

fetch c bulk collect into l_c1 LIMIT 1000;

for i in 1 .. l_c1.count

loop

–Expecting to do the delete

dbms_output.put_line(‘Delete this ‘ || l_c1(i).sn);

end loop;

dbms_output.put_line(‘———————-‘);

exit when c%notfound;

end loop;

close c;

end delete_duplicate;

Update and Select in oracle

This code does an unique update where it will select the value from other tables. I got this from a website but i forgot where. Thank those who shared this 😉

UPDATE
INCOMING_LEADSET
(
INCOMING_LEAD.DEPARTMENT_CD, INCOMING_LEAD.PROGRAM_CD,
INCOMING_LEAD.EFFORT_CD, INCOMING_LEAD.OFFER_CD) = (SELECT OFFER_INPUT_KEYCODE.DEPARTMENT_CD, OFFER_INPUT_KEYCODE.PROGRAM_CD, OFFER_INPUT_KEYCODE.EFFORT_CD, OFFER_INPUT_KEYCODE.OFFER_CD FROM OFFER_INPUT_KEYCODE
WHERE OFFER_INPUT_KEYCODE.INPUT_KEYCODE = INCOMING_LEAD.PROMO_INITIAL_KEYCODE)

Malaysian NewIC validate function in PL/SQL

I have some simple solutions to validate Malaysian IC / (NewIc) in PL/SQL. This solution may not be the best but at least helps a little.
The format
YYMMDD######

CREATE OR REPLACE FUNCTION is_validic(char_in VARCHAR2) RETURN VARCHAR2 IS
n NUMBER;
incorrect EXCEPTION;
BEGIN
n:=0;
if to_date(substr(char_in,1,6),’YYMMDD’) !=to_date(substr(char_in,1,6),’YYMMDD’) then
n:=n+1;
end if;

if length(nvl(char_in,’0′)) !=12 then
n:=n+1;
end if;

if n=0 then
return ‘TRUE’;
else
return ‘FALSE’;
end if;

EXCEPTION
WHEN OTHERS THEN
RETURN ‘FALSE’;
END is_validic;

Finding the latest / max records in transactional table

Finding the latest / max records
I’ve just came up with aniother problem of getting a set of results. Finding the latest records, Example:

ID Mobileno created
1 +90990 01-FEB-06
2 +90990 02-FEB-06
3 +90991 02-FEB-06
4 +90991 01-FEB-06

The results must be like this , only 1 mobileno with the latest creation date

ID Mobileno created
2 +90990 02-FEB-06
3 +90991 02-FEB-06

The answer:
select t.*
from (select a.*,dense_rank() over (partition by mobileno order by created desc) dr from my_table a) t
where dr=1;