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