Declaring and using variables in Oracle SQL query

Zodman

Consider the following SQL query, which runs just fine against my Oracle connection in LinqPad:

select owner, table_name
from all_tables
where rownum<100
and TABLE_NAME like '%DEAL%'
order by table_name, owner

Is there a way to define a variable for the 'like' value and use it?

ie, something like:

define tableformat = '%DEAL%';

select owner, table_name
from all_tables
where rownum<100
and TABLE_NAME like tableformat
order by table_name, owner

JoeAlbahari

You can do that with string.Contains:

where table_name.Contains ("DEAL")

ShaneGreen

I would also like to know how to use variables in Oracle SQL scripts.

An SQL*Plus script would use:

define tableformat = '%DEAL%';
select * from all_tables where table_name like '&tableformat';
select * from all_table_columns where table_name like '&tableformat';

so the use of the variable is about reusing the same value in multiple statements

An example in C# would be

String tableformat = "NEW";
COUNTRY.Where (b => b.COUNTRY_NAME.Contains(tableformat));
STATES.Where (b => b.STATE_NAME.Contains(tableformat));

JoeAlbahari

What happens when you run your example in C#?

ShaneGreen

Sorry for the delayed response.

The lines starting with COUNTRY and STATE should end with ".Dump()" the you will get a list of countries with the character sequence "NEW" contained, and then a list of states with the character sequence "NEW" contained.