Output of Duplicats using a Procedure

Question

i am in the middle of studys and currently doing some exercises.

I am trying to make procedure that shows how many duplicats are in a row.

CREATE OR REPLACE PROCEDURE anzahl1(
    tabelle VARCHAR2,
    reihe VARCHAR2,
    wieviel OUT NUMBER
    )AS
    test VARCHAR2(4000);
BEGIN

    EXECUTE IMMEDIATE 'SELECT
        reihe,
        (COUNT(*)-1) AS Anzahl
    INTO     
        wieviel
    FROM 
        tabelle
    GROUP BY 
        reihe
    HAVING COUNT(*) > 1' using out wieviel;
    DBMS_OUTPUT.PUT_LINE(wieviel);

END;
/

i tried many things but nothing worked...

even the result i was given by a teacher of my old school didnt work:

CREATE OR REPLACE PROCEDURE check_doppelte_Werte_p ( 
   p_tabellenname   IN     USER_TAB_COLUMNS.TABLE_NAME%TYPE, 
   p_spaltenname    IN     USER_TAB_COLUMNS.COLUMN_NAME%TYPE, 
   p_ergebnis       BOOLEAN 
) OUT     
IS 
   v_dummy           NUMBER := 1; 
   v_sql_anweisung   varchar2 (4000); 
BEGIN 
   v_sql_anweisung := 
         'SELECT   MAX(COUNT (' 
      || p_spaltenname 
      || ')) ' 
      || '  FROM   ' 
      || p_tabellenname 
      || '  GROUP BY ' 
      || p_spaltenname; 

   DBMS_OUTPUT.PUT_LINE (v_sql_anweisung); 

   EXECUTE IMMEDIATE v_sql_anweisung INTO   v_dummy; 

   IF v_dummy > 1 THEN  
     DBMS_OUTPUT.PUT_LINE(   'Die Tabelle ' 
                          || 'hat mindestens  ' 
                          || TO_CHAR (V_DUMMY) 
                          || ' doppelte Werte in der Spalte ' 
                          || p_spaltenname); 
     p_ergebnis := TRUE; 
  ELSE  
     p_ergebnis := FALSE; 
  END IF; 
EXCEPTION 
   -- Keine Werte gefunden, da  
   WHEN NO_DATA_FOUND 
   THEN 
      p_ergebnis := FALSE; 
 DBMS_OUTPUT.PUT_LINE ('Tabellenname oder Spaltenname sind nicht vorhan-
den!!!'); 
END;

What would you do/change?

Answer 1

Your code doesn't substitute the variables in the dynamic sql.The query String has been to be appended with the input variables.

Please try the code below,

 CREATE OR REPLACE PROCEDURE anzahl1(
tabelle VARCHAR2,
reihe VARCHAR2,
wieviel OUT NUMBER
)AS
test VARCHAR2(4000);
lv_query VARCHAR2(4000);

BEGIN

lv_query := 'SELECT (COUNT(*)-1) cnt FROM '||tabelle ||' GROUP BY '||reihe||' HAVING COUNT(*) > 1';

EXECUTE IMMEDIATE lv_query into wieviel;

DBMS_OUTPUT.PUT_LINE(wieviel);

EXCEPTION 
     WHEN OTHERS THEN 
       DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE||' '||SUBSTR(SQLERRM,1,200));

    END;
/

I create table and populate records as below,

   CREATE TABLE EMP(ID NUMBER(10),NAME VARCHAR2(40));

    INSERT INTO EMP VALUES (1, 'TEST');
    INSERT INTO EMP VALUES (1, 'TEST');
    INSERT INTO EMP VALUES (2, 'TEST2');

Now i call the stored procedure to test for EMP table for ID column,

declare 
lv_cnt number(10);
begin
anzahl1('EMP','ID',lv_cnt);
dbms_output.put_line(lv_cnt);

end;

This give output as ,

1