By using a dynamic variable i need to change the schema name in my SQL Query. How do I do it dynamically?
5
-
3Which RDBMS is this precisely? And can you add some examples?SouravA– SouravA2015-08-21 08:57:24 +00:00Commented Aug 21, 2015 at 8:57
-
2Which DBMS do you use?Jens– Jens2015-08-21 08:57:50 +00:00Commented Aug 21, 2015 at 8:57
-
2Add more info, and don't tag products not involved - MySQL, SQL Server and Oracle are different products!jarlh– jarlh2015-08-21 09:00:52 +00:00Commented Aug 21, 2015 at 9:00
-
using oracle sql developer. say i am using a schema A and i want to run my query in another environment and it has schema B. my query is having like A.tableName so while running in another environment i don't want to go to every place and change from A.tableName to B.tableName. instead i need to change a variable from A to B so that all the place referring A.tableName is changed to B.tableName.jack– jack2015-08-21 09:05:01 +00:00Commented Aug 21, 2015 at 9:05
-
Use a substitution variable, as explained here: stackoverflow.com/questions/1179652/…A Hocevar– A Hocevar2015-08-21 09:12:17 +00:00Commented Aug 21, 2015 at 9:12
Add a comment
|
1 Answer
Assuming this is a query you're manually running in an Oracle database, you could just use a substitution variable, eg.:
select * from &schema..table_name;
The first . is required to indicate the end of the parameter name, so you do need a second consecutive . in order to get the query to work.
If you're needing to reuse the substitution variable across multiple queries in the same script, use && instead, eg.:
select * from &&schema..table_name;
If you're running your queries as a script, don't forget to put undefine schema at the end, so that you will be prompted for a new value when you rerun the script, if it's being rerun in the same session.
7 Comments
Boneist
The first . is to show where the end of the parameter is. If you didn't put the second dot in, you'd end up with something like:
select * from schema_nametable_name; instead of the required select * from schema_name.table_name
jack
I tried this and it asked to enter a substitution variable and if i give any random variable it is not working.
Boneist
what do you mean by "any random variable"? Clearly, you need to make sure the value is one of your schema names, otherwise the table won't be found. At least, that's the error I assume you're getting, since you didn't say...
jack
i got it sorry.my mistake.will this work if more than a table is defined in from clause?will this ask for var name more than once?
jack
can you tel me what would be the syntax in that case?
|