CODEWITHSUNDEEP
oracle-databaseplsqlsubquery
Woodly0
Woodly0

Reputation: 434

Alternative for conditional subquery in Oracle 11g

I'm getting more and more experienced with oracle pl/sql but this problem seems to be persistent: I have a procedure that merges external data into a table in the database that looks something like this:

PROCEDURE updateTable (ts DATE, val NUMBER, id NUMBER)
BEGIN
  IF id NOT IN (15, 16, 23)
  THEN 
    MERGE INTO myTable dest
    USING (SELECT ts, val, id FROM Dual) src
    ON (src.id = dest.id AND src.ts = dest.ts)
    WHEN MATCHED THEN UPDATE SET dest.val = src.val
    WHEN NOT MATCHED THEN INSERT (ts, val, id) VALUES (src.ts, src.val, src.id);
  END IF;
END;

This works just fine so far. Now the problem is that the list of id's that are excluded is hardcoded and it would be much more dynamic to have those in another table, i.e. in the code above replace the line 

IF id NOT IN (15, 16, 23)

with something like

IF id NOT IN (SELECT id FROM excluTable)

which returns the notorious error: PLS_00405: subquery not allowed in this context If it was only one id, I could simply create a variable and select the id into it. Unfortunately it's quite a long list. I've tried to bulk collect them into an array but then I don't find a way to put that into the conditional clause either. I'm sure there is an elegant solution for this. Thanks for your help!

Upvotes: 2

Views: 193

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

There may be many IDs in your exclusion table, but you are only passing one into the procedure. You can see if that single value exists in the table with a count into a local variable, and then check whether the count was zero or non-zero; something like:

PROCEDURE updateTable (ts DATE, val NUMBER, id NUMBER) IS
  l_excl_id PLS_INTEGER;
BEGIN
  SELECT count(*)
  INTO l_excl_id
  FROM excluTable
  WHERE excluTable.id = updateTable.id;

  IF l_excl_id = 0
  THEN 
    MERGE INTO myTable dest
    USING (SELECT ts, val, id FROM Dual) src
    ON (src.id = dest.id AND src.ts = dest.ts)
    WHEN MATCHED THEN UPDATE SET dest.val = src.val
    WHEN NOT MATCHED THEN INSERT (ts, val, id) VALUES (src.ts, src.val, src.id);
  END IF;
END;

Incidentally, it can get confusing if your procedure argument names are the same as table column names, or other identifiers. For instance, as id is the procedure argument name and the column name in the table I've had to prefix them both:

  WHERE excluTable.id = updateTable.id;

one with the table name (or alias if you add one), the other with the procedure name. If you just did

  WHERE excluTable.id = id

then the scoping rules would mean it matched every ID in the table with itself, not the argument, so you would be counting all rows - and it might not be immediately obvious why it wasn't behaving as you expected. If the arguments were named as, say, p_ts and p_id then you wouldn't have to account for that ambiguity. That's also why I've prefixed my local flag variable with l_.

Upvotes: 1

Related Questions