2

I have several sqls that I am running as part of a large db script. There are often conditions which are repeated over several of the sqls. For example

update phone_numbers set num = 'abc' where id not in (1,2,4,5,6,8);
update fax_numbers set num = 'abc' where id not in (1,2,4,5,6,8);
update email_add set val = 'abc' where id not in (1,2,4,5,6,8);

My problem is that I am constantly repeating the values in the not in clause.

how can i move these numbes into a variable and then apply to each sql. Something like

my_var = (1,2,4,5,6,8);
update phone_numbers set num = 'abc' where id not in @my_var;
update fax_numbers set num = 'abc' where id not in @my_var;
update email_add set val = 'abc' where id not in @my_var;

The SQL is running on Oracle if that helps? Ive done this before with one parameter but not with an array.

Ive seen this Oracle PL/SQL - How to create a simple array variable? but that doesnt apply to an in

Ive searched a few places but nothing seems to be quite what I want

Thanks

Community
  • 1
  • 1
RNJ
  • 15,272
  • 18
  • 86
  • 131

1 Answers1

4

If you're using SQL*Plus or SQL Developer, you could define a substitution variable:

define my_var = '1,2,4,5,6,8'
update phone_numbers set num = 'abc' where id not in (&my_var);
update fax_numbers set num = 'abc' where id not in (&my_var);
update email_add set val = 'abc' where id not in (&my_var);

Each statement will do a simple substitution of the value (as the name suggests); if you have set verify on then you'd see that happening:

old:update phone_numbers set num = 'abc' where id not in (&my_var)
new:update phone_numbers set num = 'abc' where id not in (1,2,4,5,6,8)

You could also wrap your statements in a PL/SQL block and use a built-in collection type to hold the values:

declare
  my_var sys.odcinumberlist;
begin
  my_var := sys.odcinumberlist(1,2,4,5,6,8);

  update phone_numbers set num = 'abc'
  where id not in (select column_value from table (my_var));
  update fax_numbers set num = 'abc'
  where id not in (select column_value from table (my_var));
  update email_add set val = 'abc'
  where id not in (select column_value from table (my_var));
end;
/

... which is longer and hides update counts unless you display SQL%ROWCOUNT yourself, but would work from any client.

Because this is using a varray type you can use collection methods. So if you currently had something like this in a PL/SQL block::

if x in (1,2,4,5,6,8) then

you could instead do;

if my_var.exists(x) the
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks @Alex-poole. I have got it sort of working. The substition bit didnt seem to work for some reason. It sill opened a popup asking me to input data. I tried the built in collection type and that worked for most of them apart from whne I was using the condition within an if. I then had to duplicate the ids for the if statement :( – RNJ Mar 06 '15 at 15:17
  • @RNJ - you shouldn't get a pop-up if you've run the `define` part. At least, running as a script, and in a recent version. Not sure what you mean about 'if' though, can you show an example? – Alex Poole Mar 06 '15 at 15:46
  • yep the define should work - Ive looked at other sites as well. not sure what is happening. THe if statement doesnt allow a subselect clause and with the collection type because it is doing a select from table(..) then I think it sees it as a sub select – RNJ Mar 06 '15 at 20:47
  • @RNJ - if I understand what you're doing, say, the equivalent of `if x in (&my_var) then` (if define worked!), you can use the collection method: `if my_var.exists(x) then`. (Added that to my answer). Not sure why define doesn't work though; are you using a really old version of SQL Devleoper? Either will work in SQL\*Plus too. – Alex Poole Mar 06 '15 at 20:53