3

Here I've found how to define a variable in Oracle SQL Developer.

But can we define the range of values somehow?

I need smth like this: 

define my_range = '55 57 59 61 67 122'; 
delete from ITEMS where ITEM_ID in (&&my_range);
Community
  • 1
  • 1
MockerTim
  • 2,475
  • 1
  • 25
  • 31
  • Possible duplicate of [WHERE IN condition not accepting String value](http://stackoverflow.com/questions/35648999/where-in-condition-not-accepting-string-value) – MT0 Mar 16 '16 at 09:57
  • Don't know oracle's abilities to deal with XML, but you might use an XML parameter like ` [...] ` In SQL Server it was very easy to get a derived table with `.nodes()` – Shnugo Mar 16 '16 at 10:00
  • @MT0 Nope. My question is different. – MockerTim Mar 17 '16 at 18:06

3 Answers3

4

Actually if you put commas in your list it will work since you are using a substitution parameter (not a bind variable):

define my_range = '55, 57, 59, 61, 67, 122'; 
delete from ITEMS where ITEM_ID in (&&my_range);

enter image description here

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
2

Use a collection:

CREATE TYPE INT_TABLE AS TABLE OF INT;
/

Then you can do:

DEFINE my_range = '55,57,59,61,67,122';
DELETE FROM   items
WHERE  ITEM_ID MEMBER OF INT_TABLE( &&my_range );

Example:

CREATE TABLE ITEMS ( ITEM_ID ) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 150;

DEFINE my_range = '55,57,59,61,67,122';
DELETE FROM items WHERE ITEM_ID MEMBER OF INT_TABLE( &&my_range );

Output:

Table ITEMS created.

old:DELETE FROM items WHERE ITEM_ID MEMBER OF INT_TABLE( &&my_range );
new:DELETE FROM items WHERE ITEM_ID MEMBER OF INT_TABLE( 55,57,59,61,67,122 );

6 rows deleted.
MT0
  • 143,790
  • 11
  • 59
  • 117
0

You can use LIKE and just change the way you contain your variable.. Something like this:

define my_range = ',55,57,59,61,67,122,'; 
delete from ITEMS
where $$my_range like ('%,' || ITEM_ID || ',%');
sagi
  • 40,026
  • 6
  • 59
  • 84
  • 1
    Maybe just the following should work: `define my_range = '55,57,59,61,67,122'; delete from ITEMS where ITEM_ID in (&&my_range);`? – MockerTim Mar 16 '16 at 10:15