Refering to environment variable in oracle sql files on Linux and Windows

Question

We have an sql build script, with oracle 10.2. At the end of the script we have this:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Which works on Linux but not on Windows. So we created a second file with this line:

@%ORACLE_HOME%/rdbms/admin/utlrp.sql

Surely there is a better way. How can I refer to an environment variable in a cross-platform way? Or alternatively, is it possible to do OS detection in the sql file to guard each of the statements with an IF clause?

Answer 1

I believe this syntax is what you need:

@?/rdbms/admin/utlrp.sql

However, this is just for the ORACLE_HOME variable. I don't know about others...

Edit:

It's a bit awkward, but you could look into setting SQLPATH environment variable as well. It's to SQL scripts in oracle as PATH is to executables in Unix/Windows.