CODEWITHSUNDEEP
oracle-databaseselectplsqlsqlplus
xmcx
xmcx

Reputation: 346

How to use variable in some select statement with Oracle PL-SQL( or sql-plus)

I have a few select statements, they all need to use the same variable. So can I define a variable first, then use it directly in the following statement, like this:

declare variable;  -- a to_date function
select statement 1;
select statement 2;
select statement 3;
...

So when the conditions change, I can just modify the variables. How can I accomplish it use pl-sql(or sql-plus) in oracle?

Upvotes: 0

Views: 234

Answers (1)

Popeye
Popeye

Reputation: 35900

You need simple sql*plus variable.

variable my_date varchar2(30)

exec :my_date := '01-oct-2019';

select * from t where date_col = to_date(:my_date,'dd-mon-yyyy');

-- other select statements

You can achieve this using substitution variable also.

select * from t1 where date_col = to_date(&&my_date,'dd-mon-yyyy');

select * from t2 where date_col = to_date(&&my_date,'dd-mon-yyyy');

-- other select statements using &&my_date

Here, oracle will prompt for my_date once(as we have used two &) and its values will be used in all the select statements of that session.

Cheers!!

Upvotes: 1

Related Questions