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
– Ben, Commented Feb 10, 2014 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
– Alex Poole, Commented Feb 10, 2014 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. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Feb 11, 2014 at 12:21

Mike B · Accepted Answer · 2014-02-10 21:42:20Z

6

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.

answered Feb 10, 2014 at 21:42

Mike B

5,4612 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2014-02-11 12:12:37Z

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

Collectives™ on Stack Overflow

Dynamic sql compiled but can't execute

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related