I have a DML statement "Simple update statement" that i need to execute in Different Oracle Environment (DEV,SIT,QA,PROD). As of Now im hard coding the Schema Name in Alter Session Command and Running the DML statement. So i need to maintain 4 different scripts across the environment. Is there a way i can get the Database name on which the script is Running, from the database Name i can use If Else Condition to Choose between the Schema and assign it to the ALTER SESSION COMMAND?
You can query the name from the v$database view. How you translate that to an alter session command depends on your client and how you're connecting.
For example, in SQL*Plus you could do:
column x_schema new_value y_schema noprint
set termout off
select case name
when 'DEVDB' then 'DEVSCHEMA'
when 'PRODDB' then 'PRODSCHEMA'
...
end as x_schema
from v$database;
alter session set current_schema = &y_schema;
set termout on
The first query uses the DB name to determine a schema name; the column command makes that column alias x_schema available later as substitution variable &y_schema. Then that is used in the alter. The set termout is optional but will hide the query if it's run as part of a script (though then I guess the noprint is a bit pointless).
You could base your case on global_name.global_name if you prefer, or even sys_context('USERENV', 'SERVICE_NAME').
Of course, you could also just pass the schema name in as a positional parameter when you run the DML script instead. It's unusual, I think, for the schema name to be different for the same application in different databases, but this should work if that is the situation you have.