CODEWITHSUNDEEP
sqloracle-databasedbeaver
nahmed
nahmed

Reputation: 67

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

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.

Upvotes: 2

Views: 16813

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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

Upvotes: 0

pifor
pifor

Reputation: 7882

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.

Upvotes: 1

Related Questions