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

Question

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?

How can you accomplish what? What you described is the correct process. Are you asking how to assign a value to the variable before running the queries? You show how to declare the variable, but not how to assign a value to it - is that what you are asking about? — user5683823
– user5683823, Commented Oct 5, 2019 at 12:45
Sorry, I didn't express the problem very clearly. My problem is to declare the variable first, then the variable is assigned, and finally used. — xmcx
– xmcx, Commented Oct 6, 2019 at 12:31

Popeye · Accepted Answer · 2019-10-05 12:47:46Z

1

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!!

answered Oct 5, 2019 at 12:47

Popeye

36k4 gold badges12 silver badges31 bronze badges

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

6 Comments

xmcx Over a year ago

Yes, it is ok in sql-plus. When executed in pl-sql, it prompts "ORA-00900" and "ORA-01008" errors. Is there an example in pl-sql?

Popeye Over a year ago

Try the second example that is use of two &

Popeye Over a year ago

Can you please post the entire code where you are getting error. And also are you using it inside some proc/function?

xmcx Over a year ago

I am completely following the example two, but each sql statement prompts the same error "ORA-01008".

Popeye Over a year ago

Please add actual code, in which tool you are executing and actual error message in your question

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related