CODEWITHSUNDEEP
oracle-databasefunctionstored-proceduresplsqlplsqldeveloper
bin
bin

Reputation: 131

SQL Error: ORA-00904: "CNPPARMID": invalid identifier

I have a function:

At line execute immediate 'select ' || schemaname || '.' || value1 || '_seq.nextval from dual' into cnpParmId;

Am getting error as SQL Error: ORA-00904: "CNPPARMID": invalid identifier. I tried to put the cnpParmId inside the quotes, tried into cnpParmId from dual, in all possible ways. But its not working. Please give me some ideas to solve this issue. Thanks!!

Upvotes: 1

Views: 1214

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

Your function compiles successfully, and you get the error at runtime:

select test(user, 'T42') from dual;

SQL Error: ORA-00904: "CNPPARMID": invalid identifier
ORA-06512: at "MYSCHEMA.TEST", line 23

You said the error was on the first execute immediate, but that is line 21 not line 23, and if it was that cnpParmId reference it was complaining about then it would cause a compilation error - the function would be created but with errors/warnings, and it wouldn't be possible to call it.

So it's the second execute immediate, at line 23, which is erroring at runtime (reformatted slightly):

execute immediate
  'select ''T'' from dual where cnpParmId not in ' ||
  '(select value1 from ' || schemaname || '.' || tablename || ')'
  into good;

As GolezTrol said, the dynamic statement is executed in a SQL context that has no visibility of any of your PL/SQL variables. It's the same as running the generated statement:

select 'T' from dual where cnpParmId not in (select value1 from myschema.t42);

... directly in SQL*Plus or SQL Developer, which also gets:

SQL Error: ORA-00904: "CNPPARMID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

As a variation of GolezTrol's concatenation, you could use a bind variable to prevent hard-parsing each time round your loop, but you also need to provide your primary key column name as value1 also won't be recognised; and that has to be concatenated in:

execute immediate
  'select ''T'' from dual where :cnpParmId not in ' ||
  '(select ' || value1 || ' from ' || schemaname || '.' || tablename || ')'
  into good using cnpParmId;

which compiles and runs.

You could also use not exists rather than not in, which might perform better since you're looking for the (indexed) primary key:

execute immediate
  'select ''T'' from dual where not exists (select null from '
  || schemaname || '.' || tablename || ' where ' || value1 || ' = :cnpParmId)'
  into good using cnpParmId;

You can also move the query that finds value1 outside the loop; there's no benefit to calling that repeatedly.

It looks like you're doing this because you have primary key values that weren't generated from the sequence. If you're still adding new records like that - e.g. via a trigger that only uses the sequence if the passed key column is null - then you need a hack like this or an insert loop that catches the ORA-01001. But this approach still has a race condition - another session can simultaneously do a manual insert with the same value your function finds, and one of the sessions will get an error.

It would usually be better to only use the sequence; if you are now doing that, or can change to do that, then a one-off adjustment of all your sequences to be higher than the current maximum key value would be simpler.

Upvotes: 2

GolezTrol
GolezTrol

Reputation: 116110

Using execute immediate, you execute the statement outside of the scope of the function, so it can't use the PLSQL variable. I'd solve this by executing it as a normal query and use SELECT INTO or a cursor to fetch the query result.

But it should also work if you simply substitute the value into the query string yourself, like this:

Change 

'select ''T'' from dual where cnpParmId not in ' ||

into

'select ''T'' from dual where ' || cnpParmId || ' not in ' ||

Upvotes: 1

Related Questions