procedure to delete duplicate records using dynamic sql

Question

i have successfully implemented a proc which can delete delete duplicate records from a table . Carrying on i am further trying to generalize this procedure using Dynamic sql But i get the following error when compiling itself .

ERROR line 13, col 7, ending_line 14, ending_col 14, Found ''SELECT ROWID, ERR_MOD FROM '', Expecting: ( SELECT -or- WITH

Here is my code :-


CREATE OR REPLACE PROCEDURE BIOCON.proc_dedup (
   p_tblname   IN     VARCHAR2,
   p_cname   IN varchar2,
   p_cvalue  IN varchar2,
   p_out          OUT VARCHAR2:=0
)
IS
   v_cnt   NUMBER;

CURSOR TMP
   IS
      'SELECT   ROWID, ERR_MOD
        FROM '                            ||  p_tblname||
       'WHERE '||  p_cname '='||''p_cvalue '';
BEGIN
BEGIN
   'SELECT   COUNT ( * )
     INTO   v_cnt
     FROM  '            || p_tblname||
    'WHERE  '|| p_cname' = '||''p_cvalue'';
    EXCEPTION
    WHEN OTHERS
    THEN
    p_out:=1;
    end;

FOR REC IN tmp
   LOOP
      IF v_cnt = 1
      THEN
         EXIT;
      ELSE
         'DELETE FROM '||  p_tblname||
               'WHERE   ROWID = REC.ROWID';
      END IF;

  v_cnt := v_cnt - 1;


END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      p_out := 2;
END;
/

Please [Click Here](https://stackoverflow.com/questions/3317433/delete-duplicate-records-in-sql-server/49264674#49264674) to see my post on deleting duplicate records in a table — Daniel Marcus, Mar 13 '18 at 19:55

score 1 · Accepted Answer · answered Jul 30 '13 at 08:00

Hi you cannot create a dynamic query for normal cursors in such cases you should use ref cursors and follow the the steps below --create a query string --open the ref cursor and please use execute immediate for execution of delete statements...

CREATE OR REPLACE PROCEDURE BIOCON.proc_dedup (
   p_tblname   IN     VARCHAR2,
   p_cname   IN varchar2,
   p_cvalue  IN varchar2,
   p_out          OUT VARCHAR2:=0

 IS
   v_cnt   NUMBER;
   v_string varchar2(200);
   TYPE ref_cur IS REF CURSOR
   ref_cursor_name ref_cur;

BEGIN
      v_string:='SELECT ROWID, ERR_MOD FROM ' || p_tblname|| 'WHERE '|| p_cname    
      '='||''p_cvalue ''; 
  OPEN cursor_variable_name FOR v_string;
   .....
     .....


  EXECUTE IMMEDIATE 'DELETE FROM '|| p_tblname|| 'WHERE ROWID = REC.ROWID';--you need execute immediate for this

..... .....

I have two different queries here . How to handle these two with one cursor ? — HalfWebDev, Jul 31 '13 at 12:33

procedure to delete duplicate records using dynamic sql

1 Answers1