ORA-00936: missing expression for dynamic data

Question

I have 2 queries, which takes data dynamically.

1. Select cid from table1 where cNumber={{custNbr}}

Here {{custNbr}} comes from a .txt file.

2. update table2 set status='A' where customer_id=NVL({{cid}},0000)

Here {{customer_id}} comes from output of step 1.

Issue: When step 1 returns NULL, it actually returns an empty string ''. As a result, Step 2 translates to -

update table2 set status='A' where customer_id=NVL(,0000)

This throws an error java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

How do I rewrite the queries to make them work.

Answer 1

In your case, it's probably sufficient to provide a fallback for the result of the first query:

Select nvl(cid, -1) as cid
  from table1 
  where cNumber={{custNbr}}

This assumes that -1 does not appear as a customer ID in table2. Therefore, the UPDATE will be syntactically valid, but it will update zero rows.

CAVEAT

However, I'd be strongly suspicious of a tool that cannot handle NULL values as input to Database queries. You might want to contact the DevTest support regarding this.