how to create directory in oracle home

Question

I need to create an oracle directory in oracle installation folder. In my case this path is D:\app\Administrator\product\11.2.0\dbhome_1. I tried create directory ora_dir as '\', but this make reference to D:\. Is there any way to create the directory pointing to oracle_home?

Regards.

Answer 1

If you're already doing this dynamically from a procedure, you could get the ORACLE_HOME environment variable (as it was set when the instance started anyway) via a Java call, but you need a separate function:

create or replace function getenv(name varchar2) return varchar2
as language java
name 'java.lang.System.getenv(java.lang.String) return java.lang.String';
/

create or replace procedure p42 as
begin
  execute immediate q'[CREATE or replace DIRECTORY DIRECTORY_DIR AS ']'
    || getenv('ORACLE_HOME') || q'[']';
end;
/

Assuming both compile OK, when executed the directory is created with the path from the environment variable:

exec p42;

PL/SQL procedure successfully completed.

select directory_path
from all_directories
where directory_name = 'DIRECTORY_DIR';

DIRECTORY_PATH
----------------------------------------
/dboracle/orabase/product/11.2.0

If your procedure is in a package, the function can be in there too, privately if you prefer. And you don't need any additional privileges to use the Java call, just create procedure, which you already have in this case.

Unfortunately this doesn't work in Windows as ORACLE_HOME is set in the registry, not the environment; it may be possible to get information from the registry but you may also have more than one Oracle Home so you'd have to determine which registry key to use.

There is also (as Aramillo found) an [undocumented] built-in DBMS_SYSTEM package that can provide the same information; with that you could do:

create or replace procedure p42 as
  l_oracle_home varchar2(100);
begin
  dbms_system.get_env('ORACLE_HOME', l_oracle_home);
  execute immediate q'[CREATE or replace DIRECTORY DIRECTORY_DIR AS ']'
    || l_oracle_home || q'[']';
end;
/

But you would need to have execute permission granted on that package, and it's undocumented status might give you pause; some of the functionality seems to be restricted to SYSDBA anyway (note 159968.1).

While poking around My Oracle Support looking for DBMS_SYSTEM references, I also stumbled over this option:

select nvl(substr(file_spec, 1, instr(file_spec, 'lib') -2),
  substr(file_spec, 1, instr(lower(file_spec), 'bin') -2)) as oracle_home
from dba_libraries
where library_name='DBMS_SUMADV_LIB';

... which apparently also works on Windows, but you need sufficient privileges to see the data dictionary entries.

Answer 2

You need to provide the full path:

CREATE DIRECTORY ORA_DIR AS 'D:\app\Administrator\product\11.2.0\dbhome_1';