PL/SQL Oracle_home directory contents of listener.ora

I'm trying to write the contents of listener.ora to another file without hardcoding in the directory.

Originally it worked when I specified the directory as:

'C:\app\OracleDB11g\product\11.2.0\dbhome_1\NETWORK\ADMIN'

But I don't want to hard-code because this script will be used different machines/DBs.

CREATE OR REPLACE DIRECTORY LISTENERDIR AS '%ORACLE_HOME%/NETWORK/ADMIN/';
declare
f utl_file.file_type;
fOUT utl_file.file_type;
s varchar2(200);
begi开发者_运维技巧n
f := utl_file.fopen('LISTENERDIR','listener.ora','R');
loop
    utl_file.get_line(f,s);
    fOUT := utl_file.fopen('OUTPUT','oraDBoutput.txt','A');
    utl_file.put_line(fOUT,s);
    utl_file.fclose(fOUT);
end loop;
exception
when NO_DATA_FOUND then
    utl_file.fclose(f);
end;
/

drop directory LISTENERDIR;

I have tried using the other slashes, small caps, different variations such as \NETWORK\ADMIN, and even assigning a variable from the dbms_system.get_env() but no avail.

---

See if this works for you:

SELECT SUBSTR(file_spec, 1, INSTR(file_spec, '\', -1, 2) -1)
FROM dba_libraries
WHERE library_name = 'DBMS_SUMADV_LIB';

Found it here. And here.

This will programmatically get you the ORACLE_HOME, from there you can create your Oracle directory object:

DECLARE
  dir VARCHAR2(2000);
BEGIN
   SELECT 'CREATE OR REPLACE DIRECTORY  LISTENERDIR AS '''||
          SUBSTR(file_spec, 1, INSTR(file_spec, '\', -1, 2) -1) ||''''
    INTO dir
    FROM dba_libraries
    WHERE library_name = 'DBMS_SUMADV_LIB';

   EXECUTE IMMEDIATE dir;
   ... do your stuff here...
   EXECUTE IMMEDIATE 'DROP DIRECTORY LISTENERDIR';
END;
/

---

CREATE OR REPLACE DIRECTORY LISTENERDIR AS

'%ORACLE_HOME%/NETWORK/ADMIN/';

We cannot do this. We have to specify the absolute OS path when we create a directory object. That's why it worked when you used the full path. It's in the documentation. Find out more.

If you really want to create the library dynamically you can do it like this:

SQL> conn / as sysdba
Connected.    

SQL> declare
  2      l_home varchar2(255);
  3  begin
  4      dbms_system.get_env('ORACLE_HOME', l_home);
  5
  6      execute immediate 'create directory listenerdir as '''
  7          ||l_home||'\NETWORK\ADMIN''';
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select directory_path
  2  from all_directories
  3  where directory_name = 'LISTENERDIR'
  4  /

DIRECTORY_PATH
--------------------------------------------------------------------------------
C:\app\oracle\product\11.1.0\db_1\NETWORK\ADMIN

SQL>

DBMS_SYSTEM.GET_ENV() is guaranteed to return the correct directory for the $ORACLE_HOME used to start the instance; other methods are not. Only the SYS user has rights on DBMS_SYSTEM by default, but then only the restricted accounts ought to have the CREATE ANY DIRECTORY privilege.

---

Not sure why you are using PL/SQL to do this. I'm always in favour of using the database to do as much as possible, but this strikes me as an over-elaborate way of executing what is a simple OS copy.