Execute Immediate Statement issue - Variable field name and table name

Question

Hi there I am trying to build a function to reset a sequence to synch with table ID's which have gotten out of synch with the sequence. Function is as follows:

 create or replace
 FUNCTION P_FNC_SEQUENCERESET(sourceTable IN VARCHAR2, idField IN VARCHAR2, seqname VARCHAR2) RETURN NUMBER
 IS
 ln NUMBER;
 ib NUMBER;
 maxId NUMBER;
 newValue NUMBER;
 diffValue NUMBER;
 interimValue NUMBER;
 sqlStmt VARCHAR2(2000);

 BEGIN

 -- Get the maximum of the id field
 EXECUTE IMMEDIATE 'SELECT MAX(' || idField || ') INTO ' || maxId || ' FROM ' || sourceTable;

 ...code continues...

My understanding of the EXECUTE IMMEDIATE statement leads me to believe that this should be possible, however when executed I get this error:

ORA-00936: missing expression
ORA-06512: at "PSALERT_ADMIN.P_FNC_SEQUENCERESET", line 16
ORA-06512: at line 11

sers sers · Accepted Answer · 2016-12-13T13:29:00

It would need to look something like this:

EXECUTE IMMEDIATE 'SELECT ' || idField ||' FROM ' || sourceTable into maxid;

the keyword "into" is not part of the string that is dynamically executed, but it is part of the syntax of the "execute immediate" statement

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm

Execute Immediate Statement issue - Variable field name and table name

1 Answers