May 10 2012

Delete Duplicate Rows Without All Records in Oracle

Category: Administration,Procedure,SQLFatih Acar @ 10:23

If you want to delete duplicate rows but not all rows in table, you can use a procedure. For example, you have 10 duplicate rows and you want to delete 9 rows, then you can use procedure to delete process. For instance, you have a table as tmp_table. tmp_table’s columns are id,name,surname,city,phone. You want to delete duplicate for name,surname,city. You can use below procedure for this process.

Delete Duplicate Rows Procedure

CREATE OR REPLACE PROCEDURE deleteduplicate
IS
BEGIN
FOR duplicates IN ( SELECT max(id) id, name, surname, city
FROM tmp_table GROUP BY name,surname,city
HAVING count(*)>1)

LOOP
DELETE FROM tmp_table t
WHERE t.name = duplicates.name
AND t.surname = duplicates.surname
AND t.city = duplicates.city
AND t.id != duplicates.id;
END LOOP;
COMMIT;
END;

12,441 total views, no views today

Tags: Database Administration, Oracle, Stored Procedure


Jul 03 2011

XML file export with stored procedure in Oracle

Category: ProcedureFatih Acar @ 19:50

This example provides export xml file. You must create a directory before create procedure. After, you must give grant for user. Finally, You can create procedure and execute.

Create Directory and Grant

You must be sys user and can execute this queries;

CREATE DIRECTORY XMLDUMP AS ‘C:\XmlDirectoryFileName’;
SELECT * FROM DBA_DIRECTORIES;
GRANT READ,WRITE ON DIRECTORY XMLDUMP TO HR;

Create Procedure

CREATE OR REPLACE PROCEDURE PR_XMLEXPORT
IS
CURSOR CRS IS
SELECT first_name,last_name,salary FROM employees;

REC CRS%ROWTYPE;
XML_CONTENT VARCHAR2(10000) := ”;
FILENAME varchar2(100) := ’employees_salary.xml’;
FILEWRITE UTL_FILE.FILE_TYPE;

BEGIN

OPEN CRS;

XML_CONTENT := XML_CONTENT || ‘< ?xml version="1.0" encoding="ISO-8859-9" ? >‘;

LOOP
FETCH CRS INTO REC;
EXIT WHEN CRS%NOTFOUND;

XML_CONTENT := XML_CONTENT || ‘< employee>‘;
XML_CONTENT := XML_CONTENT || ‘< first_name>‘||TO_CHAR(REC.FIRST_NAME)||’< /first_name>‘;
XML_CONTENT := XML_CONTENT || ‘< last_name>‘||TO_CHAR(REC.LAST_NAME)||’< /last_name>‘;
XML_CONTENT := XML_CONTENT || ‘< salary>‘||TO_CHAR(REC.SALARY)||’< /salary>‘;
XML_CONTENT := XML_CONTENT || ‘< /employee>‘;

END LOOP;

XML_CONTENT := XML_CONTENT || ‘< /employees >‘;

FILEWRITE := UTL_FILE.FOPEN(‘XMLDUMP’,’sehirleregorenes.xml’,’w’,32767);
UTL_FILE.PUTF(FILEWRITE,XML_CONTENT);
UTL_FILE.FCLOSE(FILEWRITE);

COMMIT;

CLOSE CRS;

END;

30,326 total views, 15 views today

Tags: Oracle, SQL, Stored Procedure