Delete duplicate rows from Oracle

There are times when duplicate rows somehow creep into the table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully.

Now in Oracle you can delete the duplicate entries by just executing a simple SQL because Oracle stores an index to each row in a table known as ROWID

SQL> CREATE TABLE duptest
  2    ( Id varchar(5),
  3    nonid varchar(5));

Table created.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> INSERT INTO duptest VALUES('2','b');

1 row created.

SQL> INSERT INTO duptest VALUES('2','b');

1 row created.

SQL> INSERT INTO duptest VALUES('2','b');

1 row created.

SQL> commit;

Commit complete.

SQL> DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest GROUP BY id);

2 rows deleted.

SQL> commit;

Commit complete.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> INSERT INTO duptest VALUES('1','a');

1 row created.

SQL> commit;

Commit complete.

SQL> DELETE FROM duptest
  2  WHERE ROWID IN (SELECT ROWID FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) rnk
  3                                       FROM   duptest )
  4                  WHERE rnk>1);

3 rows deleted.

SQL> commit;

Commit complete.

Enjoy

How to split Single row value into multiple lines ?

SQL> create table emp_split (
  2  id number,
  3  name varchar2(30),
  4  description varchar2(2000)
  5  )
  6  ;

Table created.

SQL> insert into emp_split (id,name,description)
  2  values(1,'akbar','I am an Oracle Certified Professional, Having good knowdedge in Oracle Database and Developer, I have completed several projects, I 

have been working since 3 years')
  3  ;

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE package emb_string as
  2  type string_array is table of varchar2(32767);
  3  function split_string(str in varchar2, delimiter in char default ',') return string_array;
  4  end emb_string;
  5  /

Package created.

SQL> CREATE OR REPLACE package body emb_string as
  2  function split_string(str in varchar2, delimiter in char default ',') return string_array is
  3  return_value         string_array := string_array();
  4  split_str            long default str || delimiter;
  5  i                    number;
  6  begin
  7  loop
  8  i := instr(split_str, delimiter);
  9  exit when nvl(i,0) = 0;
 10  return_value.extend;
 11  return_value(return_value.count) := trim(substr(split_str, 1, i-1));
 12  split_str := substr(split_str, i + length(delimiter));
 13  end loop;
 14  return return_value;
 15  end split_string;
 16  end emb_string;
 17  /

Package body created.

SQL> CREATE OR REPLACE FUNCTION F_split_string(P_text varchar2) RETURN VARCHAR2 is
  2  --v_id varchar2(10):=p_id;
  3  v_text varchar2(2000):=P_text;
  4  r_text varchar2(2000);
  5  v_split_string       emb_string.string_array;
  6  begin
  7  v_split_string := emb_string.split_string(P_text);
  8  if v_split_string.count > 0 then
  9  r_text:=v_split_string(1);
 10  for i in 2..v_split_string.count loop
 11  r_text:=r_text||chr(10)||v_split_string(i);
 12  end loop;
 13  end if;
 14  return r_text;
 15  end;
 16  /

Function created.

SQL> select F_split_string(description) from emp_split;

F_SPLIT_STRING(DESCRIPTION)                                                     
--------------------------------------------------------------------------------
I am an Oracle Certified Professional                                           
Having good knowdedge in Oracle Database and Developer                          
I have completed several projects                                               
I have been working since 3 years                                               
                                                                                

Oracle spool output to file

Use following commands to spool oracle script

SQL> Spool on
SQL> Spool c:\spooltext.txt
SQL> Query
SQL> Spool off

If you want to off the header use following commands

SQL> Spool on
SQL> set heading off <===
SQL> Spool c:\spooltext.txt
SQL> Query
SQL> Spool off

How does one code a matrix/crosstab/pivot report in SQL?

Newbies frequently ask how one can display "rows as columns" or "columns as rows". Look at these example crosstab queries (also sometimes called transposed, matrix or pivot queries):

SELECT  *
  FROM  (SELECT job,
                sum(decode(deptno,10,sal)) DEPT10,
                sum(decode(deptno,20,sal)) DEPT20,
                sum(decode(deptno,30,sal)) DEPT30,
                sum(decode(deptno,40,sal)) DEPT40
           FROM scott.emp
       GROUP BY job)
ORDER BY 1;

JOB           DEPT10     DEPT20     DEPT30     DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST                    6000
CLERK           1300       1900        950
MANAGER         2450       2975       2850
PRESIDENT       5000
SALESMAN                              5600

Here is the same query with some fancy headers and totals:

SQL> ttitle "Crosstab Report"
SQL> break on report;
SQL> compute sum of dept10 dept20 dept30 dept40 total on report;
SQL>
SQL> SELECT     *
  2    FROM     (SELECT job,
  3                  sum(decode(deptno,10,sal)) DEPT10,
  4                  sum(decode(deptno,20,sal)) DEPT20,
  5                  sum(decode(deptno,30,sal)) DEPT30,
  6                  sum(decode(deptno,40,sal)) DEPT40,
  7                  sum(sal)                   TOTAL
  8             FROM emp
  9            GROUP BY job)
 10  ORDER BY 1;




                                Crosstab Report

JOB           DEPT10     DEPT20     DEPT30     DEPT40      TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST                    6000                             6000
CLERK           1300       1900        950                  4150
MANAGER         2450       2975       2850                  8275
PRESIDENT       5000                                        5000
SALESMAN                              5600                  5600
          ---------- ---------- ---------- ---------- ----------
sum             8750      10875       9400                 29025

Here's another variation on the theme:

SQL> SELECT DECODE(MOD(v.row#,3)
  2                 ,1, 'Number: '  ||deptno
  3                 ,2, 'Name: '    ||dname
  4                 ,0, 'Location: '||loc
  5                 ) AS "DATA"
  6    FROM dept,
  7         (SELECT rownum AS row# FROM user_objects WHERE rownum < 4) v
  8   WHERE deptno = 30
  9  /

DATA
--------------------------------------- ---------
Number: 30
Name: SALES
Location: CHICAGO

Open File Dailog box example using Webutil CLIENT_GET_FILE_NAME Forms 10g

Today i m going to show the How to configure Oracle WebUtil Package with Oracle forms 10g in simple steps with working example that i have created.

In this example we'll upload a Text file (Fields separated by Comma ) into Oracle Forms data block.
Download Text file and FMB
Before we start we have to configure WebUtil  to work with Forms here are the steps

Updated September 22, 2012 A good Forms 10g Configuration manual can be found here



Configuring WebUtil at OS:

1.Download WebUtil http://www.oracle.com/technology/software/products/forms/files/webutil/webutil_106.zip
2. Download Java Com Library http://prdownloads.sourceforge.net/jacob-project/jacob_18.zip
3. Extract both zips to some directory.
4. Copy
  File frmwebutil.jar
  From folder: webutil_106\webutil_106\java  To: $ORACLE_HOME/forms/java

  File: jacob.jar 
  From folder: jacob_18  To: $ORACLE_HOME/forms/java

  File: d2kwut60.dll, JNIsharedstubs.dll
  From folder: webutil_106\webutil_106\webutil To: $ORACLE_HOME/forms/webutil

  File: jacob.dll
 From folder: jacob_18 To: $ORACLE_HOME/forms/webutil

  File: forms_base_ie.js, forms_ie.js
- From folder: webutil_106\webutil_106\java To: $ORACLE_HOME/forms/java

 File: webutil.olb, webutil.pll and create_webutil_db.sql
 From folder: webutil_106\webutil_106 To: $ORACLE_HOME/forms

 File: webutilbase.htm, webutiljini.htm, webutiljpi.htm and webutil.cfg
- From folder: webutil_106\webutil_106\server To: $ORACLE_HOME/forms/server

File: sign_webutil.bat
- From folder: webutil_106\webutil_106\webutil To: $ORACLE_HOME/forms/webutil


5. In $ORACLE_HOME/forms/server/formsweb.cfg file
Change the following entries

# Forms applet archive setting for JInitiator
archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar 

if you use JRE plugin then this line also required
archive=frmall_jinit.jar,frmwebutil.jar,jacob.jar 

[webutil]
WebUtilArchive=/forms/java/frmwebutil.jar,/forms/java/jacob.jar

 6. In $ORACLE_HOME/forms/server/default.env file change the following entries

  FORMS_PATH=C:\YourOracle_Home\forms;C:\YourOracle_home\forms\webutil

  # webutil config file path
  WEBUTIL_CONFIG=C:\YourOracle_Home\forms\server\webutil.cfg

Add frmwebutil.jar, jacob.jar into Classpath 

CLASSPATH=C:\YourOracle_Home\forms\java\frmwebutil.jar;C:\YourOracle_Home\forms\java\jacob.jar

 7. Add frmwebutil.jar and Jacob.jar path entries into Windows Registry (Start > Run > type regedit and press enter) goto HKEY_LOCAL_MACHINE > SOFTWARE > ORACLE > DEVSUITE_HOME > FORMS_BUILDER_CLASSPATH Variable.
Note: if you are using Linux then instead of this setup the environment variable.

8. Sign Webutil JAR files by running these following commands Make sure Form Builder is Closed

C:\DevSuiteHome_1\forms\webutil>sign_webutil.bat c:\DevSuiteHome_1\forms\java\frmwebutil.jar
C:\DevSuiteHome_1\forms\webutil>sign_webutil.bat c:\DevSuiteHome_1\forms\java\jacob.jar

Configuring Database:

1. Connect as Scott or your User
2. Run the script $ORACLE_HOME/forms/create_webutil_db.sql using SQLPLUS or any tool.

Setting up Form Builder:

1. Open Form builder Connect with SCOTT and in PL/SQL libraries open Webutil.pll file
2. File > Save as and Rename the file to different name e.g WebUtil_lib
(Trust me it works to avoid Webutil not found error) :)
3. Compile the new PLL file and generate it
4. Attached the new named Webutil_lib.pll to your form with Remove Path option YES.
5. Open WebUtil.olb file Object group in builder and copy or subclass it into your form.
6. Now compile your form and run it.

Note: Browser will ask to certify these libraries first time Choose ALWAYS certify. Check Java Console icon on the task bar and see frmwebtul.jar and jacob.jar are loaded successfully.
7. Press Browse button on form and choose Datafile.txt and Press Open...Text file will load into the Data Block.

Feel free to comment on this if i missed something plz inform me.

Check this Manual for further info on WebUtil
http://www.oracle.com/technology/products/forms/htdocs/webutil/web_util.pdf

The Role of HTML & CSS in Web Design

If you are thinking about pursuing web design as either a career or a hobby, you must be able to do more than simply design a pretty page. Professional web designers are experts in programming and coding as well. The most talented web designers are those that can adequately balance both their creative and artistic sides with their love of technical coding.
As a web designer, there are plenty of different web design standards one must master. The two most important in the industry today are HTML and CSS. What are these methods all about and how much time and money will you need to invest in order to learn them?

"iGenius" to debut on Discovery

Discovery Channel has announced a quickly-produced new documentary that will look back at the many ways Apple visionary Steve Jobs influenced modern life.
"MythBusters" hosts Adam Savage and Jamie Hyneman will host the documentary "iGenius: How Steve Jobs Changed the World," which will premiere on Discovery Channel on October 16.
Immediately following its debut on Discovery, the special will air on the network's sister channels in more than 210 countries and territories around the world.
Interviewees lined up for the documentary include John Draper, an engineer who helped start Jobs' career; Daniel Kottke, a friend who traveled to India with Jobs and became an early Apple employee; Lee Felsenstein, a founding member of the Homebrew Computer Club; journalists Tom Brokaw and Toure; and musicians Stevie Wonder and Pete Wentz, who talk about how Jobs' musical innovations added to their career successes.
"Someone once said that to follow the path that others have laid before you is a very reasonable course of action, therefore all progress is made by unreasonable men," Adam Savage said.
"Steve Jobs was an unreasonable man. He didn't simply give the public what they wanted, he defined entirely new ways of thinking about our lives in the digital space: productivity, creativity, music, communication, media and art. He has touched, directly and indirectly, all of our lives."

BlackBerry says services have improved

BlackBerry services buzzed back to life Thursday across Europe, the Middle East, Africa and India, after a three-day outage that interrupted email messages and Internet services for millions of customers.
Research In Motion Ltd., the Canadian company that makes the phones, posted a statement Thursday that says services are better. BlackBerry users around the world have faced huge problems over the past few days getting and receiving emails and messages, as well as launching Internet services.
The company says it will monitor the situation around the clock to make sure there's no repeat.
Research In Motion said a crucial link in its European infrastructure failed Monday, and that its backup also failed. Although the underlying issues have been repaired, the system had built up a backlog of emails and messages that needed to be wound down.
The failure in Europe soon spread to regions all round the world, including the U.S. and Asia.
Users fumed and confidence in the brand was strained, at a time the company is fighting a big battle with Apple Inc. the timing of the outage was particularly unfortunate as it came on the launch of Apple's new iPhone.

Laptop bazaar opens

A marketplace for laptops and other similar mobile devices only has been opened on 13th October,2011on the 5th floor of Eastern Plus Shopping Complex at Shantinagar in the capital.

Innovation is the biggest show:

It was a fair unlike any other.
It did not have the hustle bustle of the Ekushey Book fair; the window-shoppers and chaos of the annual trade expo; or the dandy gadgets and electronic novelties of the computer fairs.
But everyone who visited the three-day Digital Innovation Fair 2011 that ends today came away with something.
For some, it was the knowledge that anyone can now book hotel tickets from anywhere in the country over the internet, or how schoolteachers even in remote villages can develop digital classroom contents with help of textbooks, websites and YouTube videos.
Others learned how the education boards use digital devices to check examination papers or how the much-hyped electronic voting machines introduced by the Election Commission (EC) work.
“I didn't know you could view the maps and all the details of DAP (Detailed Area Planning) from the RAJUK website!” said Iqbal Hossain, a civil engineering student of a private university.
“I also didn't know that you could have immediate weather updates from your mobile phones or farmers can actually seek expert opinions on crops, fertilisers and insecticides through websites and cell phones,” he said. “These are information that many of us need for our everyday life, work and other things, and more people should know about these.”
Showcasing innovative government initiatives and projects on the use of ICT in various government and private organisations was the motto for the Digital Innovation Fair 2011 that is being held at the Bangabandhu Sheikh Mujib Novotheatre in the city.
The fair, organised by the Prime Minister's Office (PMO) in collaboration with the Ministry of Science & ICT, was also meant to bridge the communication gap between government, private organisations and the general people
“There are many convenient and useful services available for the people, and many of them do not know about them. This fair is a means to change that,” said.
A total of 68 different ministries, government authorities and private organisations took part at the fair with over a hundred different digitally enabled projects and initiatives.
While there was nothing to purchase or no rides to explore, the interesting and innovative initiatives from the many organisations attracted flocks of visitors.
The organisers said the fair also seeks to encourage both government and private organisations to come up with innovative services and deliver them to the people's doorsteps.
Over 125 unique digitally enabled services were introduced over the past two years and the innovation fair in the last year and this year played an important role in making that possible, they said.
National Digital Innovation Award 2011
This year the second National Digital Innovation Award 2011 ceremony was merged with the fair and held yesterday where 31 top initiatives from 180 nominations received awards in various categories of which 11 became champions.
Finance minister Abul Maal Abdul Muhith was present as the chief guest at the award ceremony with Jamilur Reza Choudhury, eminent educationist and chairperson of the jury board of the National Digital Innovation Award 2011.
Of the champions three of the initiatives are from government organisations and the eight from private organisations.
Among government organisations, Bangladesh Post Office received the award for its Electronic Money Transfer Service (EMTS) under the e-Finance category. Dhaka Electric Supply Company Limited's (DESCO) Online Bill Payment System through the internet was the runner-up in the category while National Board of Revenue's (NBR) Online Income Tax Calculator and Online Income Tax Return Submission received special mention.
Ministry of Communication received the champion's award for its Central Management System of Roads and Highways Department under the e-Inclusion and Participation category. Directorate General of Food and Management Information System (MIS) were chosen as runners-up.
Agriculture Information Service (AIS) received the champion's awards under the e- Livelihood category. The Deputy Commissioner's Office at Bandarban was chosen as the runner-up for its Record Room Digitisation initiative. And Deputy Commissioner's office at Naogaon received special mention for its Customised database software on Agriculture Khasland Management Information System (AKMIS) and Agriculture Khasland Settlement Information System.
Eight initiatives from individual, private and social enterprises received awards.
Gonit pathshala, an initiative from the students of Dafodil University, received the champion's award under the eLearning and Education category. Bangla Text to Braille Translator was picked as the runner-up while TeamCreative Ltd.'s Champs21, a study assessment website, received special mention.
Rabbit's Fury, a videogame, received the champion's award under e-Entertainment and Games category while Halda River: A Natural Fish Spawning Heritage of Bangladesh received the award under the e-Culture & Heritage category.
appRain Content Management Framework received an award under e-Science and Technology category while Ankur ICT Development Foundation was picked as the runner-up for its Bangla Spell Checker.
Under the e-Livelihood category, Activity for Reformation of Basic Needs' (ARBAN) initiative to Promoting Info- Lady for Building Inclusive Information and Knowledge based Upazilla won the champion's award.
Somewhere in net ltd's somewhere in... blog received the champion's award under e-News and Social Media category while CEGIS (SMS Gateway System for Monitoring Avian Influenza (Bird Flu) to reduce the community health risk of Bangladesh received the award under m-Health category.
In the m-Science and Technology category, Transformer Protection from Unauthorised Person received the champion's award.
Ministry of Science & ICT and D.Net organised the contest while Access to Information Programme (A2I) is the Principal Partner and The Daily Star, The Daily Prothom Alo, ATN News were the organising partners.