Dynamic sql compiled but can't execute

Question

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');

Dynamic SQL will always "compile" because it's not evaluated at compile-time, because it's dynamic. This is one reason why it's worth avoiding if it's easy to do so. — Ben, Feb 10 '14 at 21:45
It's often helpful to do something like `dbms_output.put_line(stmt_txt);`, at least during development, as it tends to make this sort of mistake obvious; and if it doesn't you have something to breakdown and inspect more closely. — Alex Poole, Feb 10 '14 at 22:42
Given that you're trying to open a cursor for a statement where you don't know the types of the columns involved, I suggest you might want to investigate using the DBMS_SQL package. See the answer to [this question](http://stackoverflow.com/questions/15786953/cursor-for-loop-with-dynamic-sql-statement). Share and enjoy. — Bob Jarvis - Слава Україні, Feb 11 '14 at 12:21

score 6 · Accepted Answer · answered Feb 10 '14 at 21:42

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.

score 0 · Answer 2 · edited Feb 11 '14 at 12:12

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
---------------------------------------------------------------------------

Dynamic sql compiled but can't execute

2 Answers2