Oracle SQL: Declaring a variable and using the variable in other queries

Question

I'm new to Oracle Database and I'm having some trouble with declaring variables and using that in other queries. For example, I want to create a variable called caseID with which store a number based on the select statement. Then I want to use that caseID in other queries I want to create. This is what I have:

DECLARE
   caseID NUMBER;
BEGIN
   SELECT case_id FROM cases WHERE user_id = 'test';
END;

SELECT * FROM version where case_id = :caseID
MINUS
SELECT * FROM version where version_type = 'A'

I'm not able to use the caseID in the version query, a popup comes for me to enter what caseID is.

pifor · Accepted Answer · 2020-05-19 15:13:29Z

1

With SQLPlus you can try to declare a SQLPlus variable (this should also work with any GUI tool that is compatible with this kind of variable declaration such as SQL Developer, TOAD, ...):

variable caseID number;
BEGIN
   SELECT case_id INTO :caseID FROM cases WHERE user_id = 'test';
END;
/
select * from version where case_id = :caseID;

Another possibility that does not use special client syntax but only PL/SQL:

DECLARE
caseID number;
v version%ROWTYPE;
BEGIN
   SELECT case_id INTO caseID FROM cases WHERE user_id = 'test';
   SELECT * INTO v FROM version WHERE case_id = caseID;
END;
/

But in this case you have to code everything in PL/SQL and make sure to process output of SELECT statements.

edited May 19, 2020 at 15:13

answered May 19, 2020 at 14:56

pifor

7,9722 gold badges11 silver badges18 bronze badges

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

2 Comments

nahmed Over a year ago

The tool i'm using is DBeaver and I tried your answer. It gave me an invalid SQL statement

pifor Over a year ago

I've updated by answer with a full PL/SQL solution.

Lalit Kumar B · Accepted Answer · 2020-05-19 15:28:07Z

0

I think what you are looking for is @set command in DBeaver to define and assign it at once and use it in the session:

@set userId='test'
@set caseId=(SELECT case_id FROM cases WHERE user_id = :userId)
SELECT * FROM version where case_id = :caseId

answered May 19, 2020 at 15:28

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Collectives™ on Stack Overflow

Oracle SQL: Declaring a variable and using the variable in other queries

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related