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

Improve this question
2
  • Where are you running the code from - just a client, like SQL Developer or SQL*Plus? And if so which client? Commented Mar 5, 2015 at 18:26
  • Hi thanks for reply. Is is via SQL plus or SQL developer. Thanks Commented Mar 5, 2015 at 19:13

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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 - 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?
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 - 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.