How to pass values separately in oracle?

Question

I am a beginner. I want to pass A63566 & A63570 value separately. Like I can use some other variable to store A63566 value and then I can use that variable here in WHERE statement.

UPDATE SKPRT.FPL_TAR
SET CO_ID = concat('A',(SUBSTR(CO_ID,2,5) + 1))
WHERE CO_ID BETWEEN 'A63566' AND 'A63570';
COMMIT;

see here:https://stackoverflow.com/questions/5666647/how-to-use-variables-in-an-oracle-pl-sql-where-clause — gregory, Dec 12 '19 at 07:06

score 0 · Answer 1 · answered Dec 12 '19 at 07:15

Depend's how you gonna run that code, but if you're using for example SQL developer or SQL*Plus, you might want to check (but I heard that it might work for example with TOAD as well):

https://blogs.oracle.com/opal/sqlplus-101-substitution-variables

Here's little example:

undefine value1 value2;
select &&value1, &&value2, &value1, &value2 from dual;

When you run this code, on the first line, you undefine variable value1 and value2. You execute the query and you're asked first for value1 and value2. Those values are stored on your client's side and are used in for replacing all the &variables. Please check the difference between && and & when specyfing the variable.

For your code, it might be something like:

undefine value1 value2;

UPDATE SKPRT.FPL_TAR
SET CO_ID = concat('A',(SUBSTR(CO_ID,2,5) + 1))
WHERE CO_ID BETWEEN '&&value1' AND '&&value2';
COMMIT;

UPDATE SKPRT.DIFFERENT_TABLE
SET CO_ID = concat('A',(SUBSTR(CO_ID,2,5) + 1))
WHERE CO_ID BETWEEN '&value1' AND '&value2';
COMMIT;

On the first part, you fill value1 and value2 variable. But the second update checks the defined variables and automatically fills them according to your previous inputs.

Hope that helps.

How to pass values separately in oracle?

1 Answers1