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 · Accepted Answer · 2016-12-13 13:29:00Z

1

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

answered Dec 13, 2016 at 13:29

sers

3,7794 gold badges24 silver badges30 bronze badges

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

2 Comments

Paul Johnson Over a year ago

I missed out the SELECT Max(' || element from the original post. But your answers is still spot on. Much appreciated.

sers Over a year ago

@PaulJohnson I copied the select from the question - could it be that the question was edited? Or else it was just to promote attentive reading ;-)

Collectives™ on Stack Overflow

Execute Immediate Statement issue - Variable field name and table name

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related