Using a temporary variable once in Oracle SQL Developer

Question

I have a requirement for a query. It needs to select every number from a list that IS NOT present in a column. Currently, I have this working fine. This query returns every number between 1833 and 2000 that is not present in the ATTR table.

SELECT LEVEL + 1833
FROM   DUAL
CONNECT BY LEVEL <= (2000 - 1833)
MINUS
SELECT ID_TX
FROM   ATTR
WHERE  ID_TX BETWEEN 1834 AND 2000;

What I want to do is make this as user-friendly as possible. To do that, I can enter two variables, a STARTING_ID and LIST_LENGTH. Now my query looks like this.

SELECT LEVEL + &STARTING_ID
FROM   DUAL
CONNECT BY LEVEL <= &LIST_LENGTH
MINUS
SELECT ID_TX
FROM   ATTR
WHERE  ID_TX BETWEEN &STARTING_ID AND &STARTING_ID + &LIST_LENGTH;

At first, I was using &&, but then I could only use this query once. UNDEFINE couldn't be placed in the code block, and wasn't cleaning my variables anyway. Now my issue is that it considers each & variable to be different, so it's making the user enter 5 variables instead of 2.

How do I make it where I'm still using temporary variables (with or without the popup to enter the variable), but the person running the query only has to enter two values 1833 and 67?

Don't know how to ask a question without asking a question, so I'll ask a question =P. But without a question mark! What tool are you running this from! =P — T Gray
– T Gray, Commented Apr 18, 2016 at 19:01

MT0 · Accepted Answer · 2016-04-18 19:44:34Z

2

A bit of a fudge but if you want the prompt for substitution variables then you can use bind variables but just populate them using substitution variables like this:

(Run it as a script using F5 and not as a statement using Ctrl+Enter)

VARIABLE list_length NUMBER;
VARIABLE start_value NUMBER;

BEGIN
  :list_length := &ll;
  :start_value := &sv;
END;
/

SELECT LEVEL + :start_value
FROM   DUAL
CONNECT BY LEVEL <= :list_length
MINUS
SELECT ID_TX
FROM   ATTR
WHERE  ID_TX BETWEEN :start_value + 1 AND :start_value + :list_length;

Otherwise, just use bind variables (i.e. the query at the bottom of the script).

answered Apr 18, 2016 at 19:44

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

I_am_Batman · Accepted Answer · 2016-04-18 18:50:59Z

1

How about using : as prompt ?

SELECT LEVEL + :STARTING_ID
FROM   DUAL
CONNECT BY LEVEL <= :LIST_LENGTH
MINUS
SELECT ID_TX
FROM   ATTR
WHERE  ID_TX BETWEEN :STARTING_ID AND :STARTING_ID + :LIST_LENGTH;

This employs the concept of bind variables. Thus, user could enter the necessary values and proceed.

answered Apr 18, 2016 at 18:50

I_am_Batman

9159 silver badges21 bronze badges

1 Comment

Junior Dev Over a year ago

Thank you, Batman. This does what I need. I was looking for a way to put the variables into the query itself, but since the "enter binds" popup saves the variables but is still changeable, it's just user-friendly enough for what I need.

cableload · Accepted Answer · 2016-04-18 18:41:33Z

0

Try to use '&&' instead of one '&'.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored.So once the alue is substituted,variable is discarded and remains undefined. If the variable is referenced twice, even in the same statement, then you are prompted twice.

If you use '&&', the value is stored and hence you will be prompted only one.

answered Apr 18, 2016 at 18:41

cableload

4,4156 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

Using a temporary variable once in Oracle SQL Developer

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related