I’m using this script for listing tns entries on server side. I’ve tested it on Windows and HP-UX.
declare
v_path varchar2(255);
v_dest_arch v_path%type;
v_orahome_path v_path%type;
tns_file utl_file.file_type;
v_sep char(1);
temp boolean:=true;
buffer_Line varchar2(1000) := null;
v_home_variable varchar2(30);
v_tns_path varchar2(255);
begin
begin
–OS version
select case when instr(upper(platform_name),‘WINDOWS’)>0 then ‘%ORACLE_HOME%’
else ‘?’ end into v_home_variable
from v$database where rownum=1;
–path using variable
select ltrim(value,v_home_variable) into v_dest_arch
from v$parameter where name=‘standby_archive_dest’
and value like v_home_variable||’%’ and rownum=1;
–orahome path
select rtrim(destination,v_dest_arch) into v_orahome_path
from v$archive_dest where status=‘VALID’
and instr(destination,v_dest_arch)>0 and rownum=1;
v_sep:=case when instr(v_orahome_path,'/')>0 then '/'
else '\' end;
exception
when no_data_found then
return;
end;
--full tnsnames path
v_tns_path:=v_orahome_path||v_sep||'network'||v_sep||'admin';
execute immediate
'CREATE OR REPLACE DIRECTORY TNS_PATH as '''||v_tns_path||'''';
--opening file
tns_file := utl_file.fopen('TNS_PATH','tnsnames.ora','r');
if not utl_file.is_open(tns_file) then
dbms_output.put_line('tnsnames.ora file not found in '||v_tns_path);
return;
end if;
--parsng tnsnaes
while temp loop
<<inside_tnsnames>>
begin
utl_file.get_line(tns_file,buffer_Line);
--this parsing is only for test, it can be extended as U need
--results only on output
if substr(trim(buffer_line),1,1)<>'#' and instr(buffer_line,'=')>0 and instr(buffer_line,'(')=0 then
dbms_output.put_line(trim(rtrim(buffer_Line,'=')));
end if;
exception
when no_data_found then temp:=false;
end;
end loop inside_tnsnames;
utl_file.fclose(tns_file);
execute immediate 'drop directory TNS_PATH';
end;