Hi everybody,
I have one issue about my SQL/PLSQL. Here, it is my code and this works fine:
with function mod_get_host(p_host in varchar2) return varchar2 is
begin
return UTL_INADDR.GET_HOST_ADDRESS(p_host);
exception when others
then
return UTL_INADDR.GET_HOST_ADDRESS(p_host||'.fw.somedomainname.com.tr');
end;
IPTAB as (select case
when db_target_name like '%CDB%' then substr(db_target_name,instr(db_target_name, '_') + 1)
else db_target_name
end dbname, mod_get_host(regexp_substr(value, 'HOST\=(.+)\)\(',1,1,NULL,1)) hostname,
regexp_substr(value, 'PORT\=([:0-9 :]+)',1,1,NULL,1) port,
a.* FROM (select distinct DB_TARGET_NAME, type, value FROM RAPOR.ASV_GV$LISTENER_NETWORK
where db_target_name not like '%PCDBZP%'
and db_target_name not like '%PCDBYP%'
and db_target_name not like '%PCDBCP%'
and db_target_name not like '%PCDBEP%') a where type in ('REMOTE LISTENER', 'LOCAL LISTENER')),
ASV_DB_IPS_VW1 as (select * from iptab
where hostname is not null
and port is not null
and dbname not like '%CDB%'),
IPTAB_fromVW as(
select dbname, listagg(hostname, ',') within group (order by hostname) hostname
from ASV_DB_IPS_VW1
group by dbname),
PORTTAB_fromVW as(select dbname, listagg(port, ',') within group (order by port) port
from (select distinct dbname, port from ASV_DB_IPS_VW1 )
group by dbname)
select i.dbname,
case
when i.hostname like '%OneIP%' then i.hostname||',SOMEIPs'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
else i.hostname
end IPs,
p.port from IPTAB_fromVW i join PORTTAB_fromVW p
on i.dbname = p.dbname;
Also, I created table:
create table rapor.ASV_DB_IPS(dbname varchar2(100), IPS varchar2(4000), Port varchar2(255));
and I want to insert data into this table as according to the https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9539062000346471801. Yet, got this error:
ORA-06553: PLS-103: Encountered the symbol "IPTAB"
ORA-06553: PLS-103: Encountered the symbol "EXCEPTION" when expecting one of the following:
. ( * % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol ";" was substituted for "EXCEPTION" to continue.
The code thrown an error:
insert /*+ WITH_PLSQL */ into rapor.ASV_DB_IPS (dbname,IPS,port)
with function mod_get_host(p_host in varchar2) return varchar2 is
begin
return UTL_INADDR.GET_HOST_ADDRESS(p_host);
exception when others
then
return UTL_INADDR.GET_HOST_ADDRESS(p_host||'.fw.somedomainname.com.tr');
end;
IPTAB as (select case
when db_target_name like '%CDB%' then substr(db_target_name,instr(db_target_name, '_') + 1)
else db_target_name
end dbname, mod_get_host(regexp_substr(value, 'HOST\=(.+)\)\(',1,1,NULL,1)) hostname,
regexp_substr(value, 'PORT\=([:0-9 :]+)',1,1,NULL,1) port,
a.* FROM (select distinct DB_TARGET_NAME, type, value FROM RAPOR.ASV_GV$LISTENER_NETWORK
where db_target_name not like '%PCDBZP%'
and db_target_name not like '%PCDBYP%'
and db_target_name not like '%PCDBCP%'
and db_target_name not like '%PCDBEP%') a where type in ('REMOTE LISTENER', 'LOCAL LISTENER')),
ASV_DB_IPS_VW1 as (select * from iptab
where hostname is not null
and port is not null
and dbname not like '%CDB%'),
IPTAB_fromVW as(
select dbname, listagg(hostname, ',') within group (order by hostname) hostname
from ASV_DB_IPS_VW1
group by dbname),
PORTTAB_fromVW as(select dbname, listagg(port, ',') within group (order by port) port
from (select distinct dbname, port from ASV_DB_IPS_VW1 )
group by dbname)
select i.dbname,
case
when i.hostname like '%OneIP%' then i.hostname||',SomeIPs'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
when i.dbname like '%onedbname%' then 'onedbname'
else i.hostname
end IPs,
p.port from IPTAB_fromVW i join PORTTAB_fromVW p
on i.dbname = p.dbname;
What am I missing?
Thanks in advance!