CODEWITHSUNDEEP
sqloracle-databaseplsql
JohnD
JohnD

Reputation: 353

Dynamic sql compiled but can't execute

I am trying to display the duplicate records using dynamic sql(execute immediate). I am getting 'An identifier with more than 30 characters was specified' error. What am I doing wrong with the dynamic sql?

 CREATE OR REPLACE PROCEDURE FIND_DUP(P_TABLE IN VARCHAR2, P_COLUMN IN VARCHAR2)
    AS
    stmt_txt varchar2(4000);
    BEGIN
     stmt_txt:= 'select' 
                      ||p_column
                      || 'from' 
                      ||p_table
                      || 'group by' 
                      ||p_column
                      ||'having count(*)>1';

     execute immediate stmt_txt;
    end;
    /

    EXECUTE FIND_DUP('EMPLOYEES','FIRST_NAME');

Upvotes: 1

Views: 181

Answers (2)

Avrajit
Avrajit

Reputation: 230

Hi the first thing whih should be kept in mind while using dynamic sql is ALWAYS print the sql generated as it will give you the idea what query exactly you are running. This code might help you to solve your query as you have done almost everything right only you were missing with some spaces.

    CREATE OR REPLACE
    PROCEDURE FIND_DUP(
        P_TABLE  IN VARCHAR2,
        P_COLUMN IN VARCHAR2)
    AS
      stmt_txt VARCHAR2(4000);
    BEGIN
      stmt_txt:= 'select' ||' ' ||p_column ||' ' ||'from' ||' ' ||p_table;
      EXECUTE immediate stmt_txt;
      DBMS_OUTPUT.PUT_LINE(STMT_TXT);
    END;


For the output 

BEGIN
  FIND_DUP('DUAL','SYSTIMESTAMP');
END;

---------------------------------------------------------------------------

OUTPUT

select SYSTIMESTAMP from DUAL

Statement processed.

0.01 seconds
---------------------------------------------------------------------------

Upvotes: 0

Mike B
Mike B

Reputation: 5451

You're missing some spaces in your query.

stmt_txt:= 'select ' 
              ||p_column
              || ' from ' 
              ||p_table
              || ' group by ' 
              ||p_column
              ||' having count(*)>1';

Without the spaces your query would end up as selectFIRST_NAMEfromEMPLOYEESgroup byFIRST_NAMEhaving count(*)>1, which to Oracle looks like an identifier with more than 30 characters.

Upvotes: 6

Related Questions