Declaring and using variables in Oracle SQL query
Options
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
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
Comments
-
You can do that with string.Contains:
where table_name.Contains ("DEAL")
-
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 beString tableformat = "NEW"; COUNTRY.Where (b => b.COUNTRY_NAME.Contains(tableformat)); STATES.Where (b => b.STATE_NAME.Contains(tableformat));
-
What happens when you run your example in C#?
-
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.